THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is a Data Philosopher at Enterprise Data & Analytics, an SSIS Trainer, Consultant, developer of the Data Integration Lifecycle Management (DILM) Suite, a Business Intelligence Markup Language (Biml) developer and BimlHero; SQL Server database and data warehouse developer, community mentor, engineer, and farmer. He is a co-author of SQL Server Integration Services Design Patterns, and the Stairway to Integration Services.

  • Long Poles and Critics

    OldNewThingCoverAs a consultant, I get calls to complete projects started by someone else or extend projects completed by someone else. When I look at someone else's work it's sometimes tempting to say, "Wow - they did that wrong." But I don't. Instead I say, "I'm not sure why they built it this way." That may sound back-handed but I make sure it's not by asking follow-up questions. Which questions? My favorite is, "What was the problem they were trying to solve?" It’s entirely possible my predecessor delivered precisely what he was asked to deliver. Plus software projects evolve (especially elegant software projects). If software solves a problem it's common for "new" problems to come into focus.

    We speak about this in terms like "long pole," the subject of this article by Raymond Chen, gifted thinker and author of The Old New Thing (which, I am embarrassed to admit, just found it’s way onto my Kindle). If I'm taking down a tent, I may decide to take down the tallest (longest) pole first. That makes the tent noticeably shorter and provides clear evidence to onlookers that I'm doing my job (aka “highly visible”). But then, *another* pole becomes the long pole. And the cycle repeats.

    Some things to keep in mind before criticizing:

    1. Delivering software is a collaboration between the service provider and the customer. It's never 50/50%. There's always imbalance even if it's as little as 49/51% – and this applies to success as well as failure. If you criticize – especially early-on – you may be speaking to the person on the 51% side of a failure. You may be unwittingly feeding the beast with all new criticisms, which leads to my next consideration...
    2. If I criticize the work of others, I am priming my customer to criticize the next bit of work she sees. Who's on deck? Me and my work.
    3. “But what if the person before me did something truly horrible, Andy?” That’s a fair question and I have a question for you, “Are you aware of 100% of the factors that went into the decisions made by your predecessor?” I’m certain the answer is “no.” Are you aware of 50%? Again, no. At best, you’re speaking to one side of an incomplete project. You will most likely have no opportunity to speak with your predecessor and the person with whom you are speaking is not going to tell you all of their side. You’re not going to get even half of the story! Now, your predecessor could have delivered something dangerous, illegal, insecure, of poor quality, not up to modern standards and best practices, or merely a solution of which you do not approve. They could well and truly be 100% to blame. Your customer may indicate that they want you to disparage the work of your predecessor. I advise you to resist the temptation to do so. Again, my advice is to fall back to “I don’t understand why they built it this way,” or (perhaps), “Based on what I’ve heard today, I would have designed this differently,” or, “I would have delivered the solution so that ______.” Why? Because you don’t know the other side of the story.

    Maybe I’m wrong about this. Maybe you’ve built a reputation as an expert by disparaging the work of others thinking that you will get all of the work and everyone will think you’re a rock star. Maybe. Or maybe John Nash and I are right about coopetition, and there’s more work out there than you can handle alone and that you have, unwittingly, introduced errors into your deliverables and primed your customers to criticize the mistakes of consultants.  (Even you.)

    Time will tell.

    Peace.

    :{>

    Learn More:
    SSIS Design Patterns and Biml: A Day of Intelligent Data Integration – Boston SQL Saturday precon, 24 Feb 2017
    Save Time and Improve SSIS Quality with Biml
    An Example of Data Integration Lifecycle Management with SSIS, Part 4
    The Recordings for SSIS Academy: Using the SSIS Catalog are Available
    SSIS Catalog Compare v2.0 Launch Event 7 Mar 2017!

    Related Training:
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago 
    IESSIS2: Immersion Event on Advanced Integration Services – Oct 2017, Chicago
    From Zero to Biml - 19-22 Jun 2017, London

  • The Last SSIS Book You Read

    SSISDesignPatternsCoverEvery now and then I get an email or meet someone at an event and they say something like, “I learned SSIS from your book!” I’m not going to lie to you, that makes me feel pretty good. Since I’ve written most of my books as part of a team, I usually reply, “It was a team effort and our team was a bunch of really smart people… plus me!”

    These folks tell me they read other books about SSIS (SQL Server Integration Services) but they didn’t really grok SSIS until they read the book authored by the team and me. I suppose that could mean the team and I are awesome. I believe that’s mostly true, but I am definitely biased…

    Here’s what I believe is really happening, though. I think these folks – and many others I’ve never met – learned more than they realized from those other books. I think our book was the last or latest SSIS book they read. I think the other books exposed these readers to complex data integration concepts, perhaps for the very first time. I still remember learning data integration and it was painful for me – and I believe I have a knack for it!

    I think our book is merely the book they read after the others. I’m willing to bet other folks have read our books first, then read books by other authors, and told those authors the same thing. I’m cool with that. As an author, I’m happy you’re reading books about the topic.

    :{>

    Learn More:
    SSIS Design Patterns and Biml: A Day of Intelligent Data Integration – Boston SQL Saturday precon, 24 Feb 2017
    Save Time and Improve SSIS Quality with Biml
    An Example of Data Integration Lifecycle Management with SSIS, Part 4
    The Recordings for SSIS Academy: Using the SSIS Catalog are Available
    SSIS Catalog Compare v2.0 Launch Event 7 Mar 2017!

    Related Training:
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago 
    IESSIS2: Immersion Event on Advanced Integration Services – Oct 2017, Chicago
    From Zero to Biml - 19-22 Jun 2017, London

  • Intelligent Data Integration, SSIS Design Patterns, and Biml

    On Friday, 24 Feb 2017, I have the privilege and honor of delivering a full-day precon titled SSIS Design Patterns and Biml: A Day of Intelligent Data Integration as part of SQL Saturday Boston. If you’re interested in learning more or attending, you can learn more here.

    “Isn’t This The Same Presentation You Delivered Before, Andy?”

    Yes and no. It has the same title but…

    I’ve focused on Biml presentations for the past two years. Over the past eighteen months I’ve built the DILM Suite. These facts intersect: My goal is to facilitate DevOps and Continuous Integration (CI) with SQL Server Integration Services (SSIS) and Biml plays an important role; namely, automated SSIS code generation. The DILM Suite development work has impacted my webinars and presentations – especially this precon. I delivered SSIS Design Patterns and Biml: A Day of Intelligent Data Integration once before, over a year ago in Atlanta. Since then I’ve delivered modules of that presentation in Enterprise Data & Analytics webinars. With each delivery the DILM Suite development work has informed and inspired changes to the content of the modules; the content has evolved and the 24 Feb delivery will be different.

    This evolution-of-content has happened to many of my Biml presentations. In some cases the updates are such that today’s version of the presentation is a radical departure from the first delivery. Why? I’m constantly learning. Writing the DILM Suite has intensified my learning. As I’ve shepherded this vision and watched it come to fruition, I’ve discovered new possibilities and more use cases.

    “Mini-Cycles”

    I catch a glimpse of what’s possible and develop until it’s reality. As I develop, the glimpse becomes more defined and I add and refine features in response. This “mini-cycle” continues until I reach a good stopping point with a solution, product, or utility. By then I’ve caught a glimpse of a solution to another problem and begin developing a different solution… and the mini-cycle repeats for this other solution, product, or utility.

    With DILM Suite, I catch a glimpse of a Euler diagram (I think visually, in graphs) showing how two or more of the solutions, products, and utilities work together to facilitate more complex DevOps and SSIS CI scenarios. This started in early 2016 around the time I began releasing a handful of free utilities. There will be more free utilities (one within the next 30 days) but the list of free DILM Suite stuff at the time of this writing is:

    The blog post titled An Example of Data Integration Lifecycle Management with SSIS, Part 4 provides a glimpse of how one might use four of these free tools together (everything except the Biml Express Metadata Framework – which hadn’t been released at that time). Today, at the time of this writing, that glimpse is my latest “pinnacle.” The Euler’s in my mind, though, are already two pinnacles beyond that – and working on a 3rd. It’s likely the 24 Feb delivery of the SSIS Design Patterns and Biml: A Day of Intelligent Data Integration precon will contain material beyond these five free tools.

    The delivery after 24 Feb (15 Jun) will likely contain still more material. I’m continuously integrating my Continuous Integration and DevOps-for-SSIS thoughts, and then building tools and designing best practices and patterns to support the latest version of my vision.

    I don’t expect to stop.

    Ever.

    “Is the SSIS Design Patterns and Biml: A Day of Intelligent Data Integration Precon Just One Big Commercial for the DILM Suite, Andy?”

    Goodness no.

    In the first part I’m going to share everything I know about using what’s-in-the-box to deliver enterprise-class data integration with SSIS – some of which Kent Bradshaw and I covered in the 3-part webinar series titled SSIS Academy: Using the SSIS Catalog (we stayed “in the box” for these three webinars). In the second part I’ll point out some gaps in the OOTB solutions and demonstrate some ways to close them. Examples of some (not all) solutions are free DILM Suite tools.

    Conclusion

    I hope to see you at SQL Saturday Boston 25 Feb! If you’re interested in learning more about DevOps and Data Integration Lifecycle Management, I also hope to see you at the SSIS Design Patterns and Biml: A Day of Intelligent Data Integration precon.

    You might enjoy engaging Enterprise Data & Analytics consultants because we like helping teams learn more about DevOps and CI for SSIS.

    :{>

    Learn More:
    An Example of Data Integration Lifecycle Management with SSIS, Part 4
    The Recordings for SSIS Academy: Using the SSIS Catalog are Available
    Save Time and Improve SSIS Quality with Biml
    SSIS Catalog Compare v2.0 Launch Event 7 Mar 2017!

    Related Training:
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago 
    IESSIS2: Immersion Event on Advanced Integration Services – Oct 2017, Chicago
    From Zero to Biml - 19-22 Jun 2017, London

  • On DevOps…

    DevOps is getting a lot of attention in SQL Server and Developer communities these days. Two friends have blogged about the outage and accompanying data loss experienced by GitLab:

    I’m sure there are other posts, these are the two I read recently. Like Brent and Mike, my first impression on hearing about the incident was: I admire their transparency. It’s on-going. GitLab posted about the incident and they were, in my opinion, incredibly honest with themselves. That’s integrity. And it’s worth its weight in californium.

    Brent’s post translated the episode into SQL Server-speak, which is awesome. Mike’s post, also awesome, used the GitLab incident to reinforce some DevOps best practices.

    Mike emphasizes the balance needed between the Dev side of DevOps and the Ops side. I’m going to kick that up a notch.

    If you are not balancing Development and Operations efforts, you are not practicing DevOps.

    What is DevOps?

    I (and likely Brent and Mike) have seen a lot of organizations struggle with this question. I’ve helped companies implement DevOps (Enterprise Data & Analytics helps organizations implement DevOps – learn more here). In my experience, companies that struggle with DevOps most often misunderstand the difference between DevOps and Agile methodologies. When well-intentioned, good-hearted, intelligent people confuse DevOps and Agile, they try to implement something like Scrum in Operations. In my humble opinion and based on experience, this either fails outright or morphs (in order to survive) into something other than Agile Operations. (Note: One can label their operations activities “Agile.” That doesn’t mean they’re practicing agile.)

    Operations is its own practice. Even in DevOps. Can we use some of the same tools and best practices designed for managing and monitoring software development in operations? Goodness yes. But that doesn’t make Operations agile.

    “What Does DevOps Mean in Operations, Then, Andy?”

    Automation. Scripted and documented, idempotent (re-executable without harming state), repeatable automation.

    Documentation

    DevOps documentation includes but is not limited to vendor and internal team documentation, wikis, knowledge bases, how-to guides, and run books. Run books may should include practices, policies, and procedures. Additional Operations documentation will cover Outage Response Management which will include on-call schedules and escalation policy and procedures.

    Most Operations teams use Service Desk ticketing software such as Jira Service Desk or ZenDesk. This too is part of Operations documentation.

    Automation

    Creating scripts and/or using tools to manage previously-manual or menial tasks – that’s automation. Automation impacts both Development and Operations in the DevOps enterprise. Regarding data-related technology, tools like Business Intelligence Markup Language (Biml) save SSIS package development time and improve quality. For managing SSIS, DevOps enterprises should take a look at the DILM Suite – a collection of tools and utilities that surface SSIS-related configurations, generate scripts, and automate deployments between Development, Test, QA, and Production instances of SSIS Catalogs.

    The Cost

    Creating and maintaining this level of Operations documentation costs time and money. It impacts time to market. But it is as necessary as unit testing software.

    I’m a huge fan of Agile methodologies. I can produce witnesses – ask anyone who’s worked with me. I’ve led many Agile Data Warehouse and Agile Business Intelligence projects. I like Agile because it’s faster and cleaner than waterfall methodologies. I like Agile because it places decision-making power for development where it belongs – in the hands of the software developers (a concept Agile borrows from Kanban, the Japanese just-in-time scheduling system).

    Creating and maintaining much of the Operations documentation is the responsibility of the development team. Your Scrum Board (or software development project plan) needs a marker for this documentation. More importantly, the project needs to be given the time to create (or update) this documentation. Granted, not all documentation needs to be complete before the software goes live. Run books describing execution, monitoring, and Operational responses, though? That information is vital at go-live.

    How much does it cost to build this level of documentation? It depends.

    One thing’s for sure: It’s cheaper than a single catastrophic failure.

    :{>

    You might like working with Enterprise Data & Analytics because we grok DevOps and SSIS.

    Learn More:
    Broken References in the SSIS Catalog
    SSIS Academy: Using the SSIS Catalog Day 1 - Create the Catalog and Deploy
    SSIS Academy: Using the SSIS Catalog Day 2 - Package Execution and Monitoring
    SSIS Academy: Using the SSIS Catalog Day 3 - SSIS Configuration
    SSIS Catalog Management
    SSIS Lifecycle Management

    Related Training:
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago 
    IESSIS2: Immersion Event on Advanced Integration Services – Oct 2017, Chicago
    From Zero to Biml - 19-22 Jun 2017, London

  • The Recording for Save Time and Improve SSIS Quality with Biml is Available

  • Biml Express Metadata Framework

    I’m not good at naming things. “Biml Express Metadata Framework” is descriptive but the acronym – BEMF – is difficult to pronounce (for me, at least). Kent Bradshaw wants to name it George. We’ll keep working on a name…

    What is the Biml Express Metadata Framework?

    The Biml Express Metadata Framework uses Business Intelligence Markup Language (Biml) to:

    • Read metadata stored in a SQL Server database.
    • Build SSIS projects that create the target database, schemas, and tables.
    • Build an SSIS project that contains one SSIS package per table, plus a Controller package that executes each table-package.
    bemf_0

    I demonstrated this solution in the webinar Save Time and Improve SSIS Quality with Biml. It’s free; both the webinar recording and the framework (although registration is required to view the webinar recording). It’s the latest addition to the DILM Suite. The zip file contains a backup of the AdventureWorks2014 database, BimlMetadata database, and the SQL Server Data Tools (SSDT) solution BimlExpressMetadataFramework.  You may download the zip file here.

    Once open, the SSDT solution appears as shown here:

    bemf_1

    To begin, please open the text file named README_BimlExpressMetadataFramework.txt found in the Miscellaneous virtual folder:

    bemf_2

    This file will contain the latest execution notes for the solution:

    bemf_3

    Generating the Build Destination Database SSIS Package

    To generate the SSIS Package named 10_Build_Destination_Databases.dtsx multi-select the Biml files 0_00_BuildConnections.biml and 0_10_BuildDestinationDatabaseMain.biml. Right-click the selected files and click Generate SSIS Packages:

    bemf_4

    If there are no errors, the Project Connection Manager named __master__AdventureWorks2014_Stage.conmgr and the SSIS Package named 10_Build_Destination_Databases.dtsx are generated:

    bemf_5

    Generating the Build Destination Schemas SSIS Package

    To generate the SSIS Package named 20_Build_Destination_Schemas.dtsx multi-select the Biml files 0_00_BuildConnections.biml and 0_20_BuildDestinationSchemasMain.biml. Right-click the selected files and click Generate SSIS Packages:

    bemf_6

    If there are no errors, the Project Connection Manager named AdventureWorks2014_Stage.conmgr and the SSIS Package named 20_Build_Destination_Schemas.dtsx are generated:

    bemf_7

    At any time during this process, you may see a dialog similar to the one shown here:

    bemf_9

    If you see this dialog, always Select All and click the Commit button.

    You may also see this dialog:

    bemf_10

    If you see this dialog, always click the Yes to All button.

    Why? The Biml Express Metadata Framework was not originally developed for Biml Express, the free SSIS package code-generation tool from Varigence. Originally, this framework was built in Mist (Biml Studio) and ported to Biml Express. One unfortunate result of the port was some SSIS artifacts are recreated during the process (note: this does not happen in the Commercial version of the Biml Framework, currently sold as an implemented solution available from Enterprise Data & Analytics).

    Generating the Build Destination Tables SSIS Package

    To generate the SSIS Package named 30_Build_Destination_Tables.dtsx multi-select the Biml files 0_00_BuildConnections.biml and 0_30_BuildDestinationTablesMain.biml. Right-click the selected files and click Generate SSIS Packages:

    bemf_8

    If there are no errors, the SSIS Package named 30_Build_Destination_Tables.dtsx is generated:

    bemf_12

    Executing the Build SSIS Packages

    Open and execute the SSIS Package named 10_Build_Destination_Databases.dtsx:

    bemf_11

    If the database already exists, the SSIS Package named 10_Build_Destination_Databases.dtsx will succeed and do nothing.

    Open and execute the SSIS Package named 20_Build_Destination_Schemas.dtsx:

    bemf_13

    If the schemas already exist, the SSIS Package named 20_Build_Destination_Schemas.dtsx will succeed and do nothing.

    Open and execute the SSIS Package named 30_Build_Destination_Tables.dtsx:

    bemf_14

    There’s an Execute SQL Task for each table. The T-SQL statement in each Execute SQL Task drops the table if it exists and then creates the table. Once created, the database, schemas, and tables appear in SQL Server Management Studio (SSMS) Object Explorer:

    bemf_15

    Delete the existing SSIS artifacts. This is another side-effect of porting this framework from Biml Studio to Biml Express. Before proceeding, we need to delete the existing Project Connection Managers and SSIS Packages:

    bemf_16

    You will need to confirm your intention to delete these artifacts:

    bemf_17

    Generating the SSIS Project That Contains the Loader SSIS Packages

    To generate the SSIS packages that load the AdventureWorks2014_Staging database, multi-select the following Biml files:

    • 0_00_BuildConnections.biml
    • 0_10_BuildDestinationDatabaseMain.biml
    • 0_20_BuildDestinationSchemasMain.biml
    • 0_30_BuildDestinationTablesMain.biml
    • 1_99_Main.biml

    Right-click the selected files and click Generate SSIS Packages:

    bemf_18

    The SSIS loader packages are generated.

    bemf_19

    A Controller SSIS package is also generated, named 99_Execute_AdventureWorks2014_Stage_Loader_Staging_Packages.dtsx:

    bemf_20

    The Controller package uses Execute SSIS Package Tasks to call the SSIS loader packages.

    Conclusion

    The Biml Express Metadata Framework may not have the coolest name but it’s free and performs a lot of work in a short amount of time.

    :{>

    Learn More:
    Save Time and Improve SSIS Quality with Biml
    SSIS Catalog Compare v2.0 Launch Event 7 Mar 2017!
    The Basics of Biml – Populating the Biml Relational Hierarchy

    Related Training:
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago 
    IESSIS2: Immersion Event on Advanced Integration Services – Oct 2017, Chicago
    From Zero to Biml - 19-22 Jun 2017, London

  • An Example of Data Integration Lifecycle Management with SSIS, Part 4

    In this post I will demonstrate advanced options for executing SSIS packages in the SSIS Catalog. I’ll also demonstrate SSIS Catalog Browser, SSIS Framework Community Edition, Framework Browser, and Catalog Reports – free utilities and solutions from DILM Suite that may help as you implement your enterprise Data Integration Lifecycle Management (DILM).

    The Problem I am Trying to Solve

    I am staging data from flat files using SSIS packages in two SSIS Projects. Here are the projects containing the packages as viewed in SSIS Catalog Browser:

    dilm4_0a

    I want to execute these packages from the SSIS Catalog in the following order:

    1. LoadWidgetsFlatFile.dtsx
    2. ArchiveFile.dtsx (to archive the Widgets flat file)
    3. LoadSalesFlatFile.dtsx
    4. ArchiveFile.dtsx (to archive the Sales flat file)

    I’ve followed an SSIS development best practice and developed several SSIS projects containing function-focused, unit-of-work SSIS packages. I cannot use the Execute Package Task because it only allows me to execute SSIS packages contained within the same SSIS project:

    dilm4_1

    What to do? Well, I could go all custom task on you…

    dilm4_2

    … but I am getting way ahead of myself. I’ll write more later about the ALC Execute Catalog Package Task. Promise.

    Three (Well, Four) SQLAgent Job Steps

    I can create a SQLAgent job with three four job steps (I want to execute the ArchiveFile.dtsx package twice, once per flat file):

    dilm4_3

    One may schedule a job step for each SSIS package one wishes to execute. I’ve seen SQLAgent jobs that execute other SQLAgent jobs which in turn execute SSIS packages from the SSIS Catalog. There’s nothing wrong with this approach and there are benefits to using SQLAgent as an SSIS workflow manager.

    “But…” (you saw this “but” coming, didn’t you?)

    While I like using SQLAgent as a scheduler, I prefer to only use it as a scheduler. I prefer to use (or build) a data integration workflow management solution to manage data integration workflow.

    SSIS Framework Community Edition

    Kent Bradshaw and I work together at Enterprise Data & Analytics. We built the SSIS Framework to manage data integration workflow. Our frameworks are available in three flavors: Community Edition, Commercial Edition, and Enterprise Edition. The Community Edition is not only free, it’s open source! The documentation (SSIS Framework Community Edition Documentation and User Guide.docx) is a primer on designing your very own SSIS Framework.

    Like all editions of our frameworks, SSIS Framework Community Edition is integrated into the SSIS Catalog. Why? We want our frameworks to participate in your Data Integration Lifecycle, and we believe the SSIS Catalog is a vital component of enterprise DILM (Data Integration Lifecycle Management).

    Many enterprise data integration platforms include built-in framework functionality. Why? Because framework functionality is necessary for enterprise DILM (Data Integration Lifecycle Management). The SSIS Catalog is a framework but it lacks some of the functionality found in competing data integration platforms. You can implement the missing functionality in SSIS. The SSIS Framework Community Edition implements some of the missing functionality.

    How can SSIS Framework Community Edition help?

    It may help to understand some fundamentals of our framework first. Our Framework contains three entities: Applications, Packages, and Application Packages. The core object is the Application Package. The metadata contained in the custom.ApplicationPackages table isn’t terribly exciting:

    dilm4_4

    An Application Package represents a Framework Package that will execute as part of a Framework Application, as you can glean from the metadata in the screenshot above. The last four Application Packages shown represent four packages that execute as part of Application ID 2. The Package IDs for these four packages are 4, 5, and 6. “But wait, Andy. You wrote four packages. Why are there only three PackageID values?” That is an excellent question. The answer is: PackageID 6 appears twice.

    The Framework Package table is straightforward and simple. It contains metadata used to identify the Package in the SSIS Catalog. All we need is the Catalog “path” to the SSIS Package. The Catalog path is a four-part hierarchy: Catalog\Folder\Project\Package. For now, there can be only one SSIS Catalog per instance of SQL Server and it is always named “SSISDB.” So we don’t store that value in the SSIS Framework Community Edition:

    dilm4_5

    The last three packages listed are PackageIDs 4, 5, and 6: LoadWidgetsFlatFile.dtsx, LoadSalesFlatFile.dtsx, and ArchiveFile.dtsx. These are the packages that are part of our Framework Application. You can think of a Framework Application as a collection of Framework Packages configured to execute in a specified order:

    dilm4_6

    The “Stage EDW Data” Framework Application is identified by ApplicationID 2. If you recall, ApplicationID 2 is mapped to PackageIDs 4, 5, and 6 (LoadWidgetsFlatFile.dtsx, LoadSalesFlatFile.dtsx, and ArchiveFile.dtsx) in the ApplicationPackages table shown above.

    The cardinality between Framework Applications and Framework Packages is many-to-many. We see an Application can contain many Packages. The less-obvious part of the relationship is represented in this example: a single Package can participate in multiple Applications or even in the same Application more than once. Hence the need for a table that resolves this many-to-many relationship. I hope this helps explain why Application Package is the core object of our SSIS Frameworks.

    The Parent.dtsx SSIS Package

    In SSIS Framework Community Edition (and the other editions of our SSIS Framework), an SSIS package named Parent.dtsx serves as the workflow engine:

    dilm4_7

    Community Edition’s Parent.dtsx package has a single Package Parameter named ApplicationName:

    dilm4_8

    The ApplicationName parameter is used in the first Execute SQL Task – named “SQL Get Framework Packages” – which selects the Application Packages based on ApplicationName and ordered by the ExecutionOrder attribute stored in the Application Packages table:

    dilm4_9

    The query, executed to select the Application Packages contained in the “Stage EDW Data” Framework Application, returns these results:

    dilm4_10

    The “SQL Get Framework Packages” Execute SQL Task stores this full resultset to an SSIS object data-type variable named User::ApplicationPackages:

    dilm4_11

    A Foreach Loop named “FOREACH Application Package” uses a Foreach ADO Enumerator to shred the contents of the User::ApplicationPackages object variable:

    dilm4_12

    The Foreach ADO Enumerator “points” at each row in the resultset in the order the rows are retrieved. It copies the values from each field into other SSIS variables, as shown here:

    dilm4_13

    Inside the “FOREACH Application Package” Foreach Loop Container, an Execute SQL Task named “SQL Execute Child Package” calls a stored procedure named custom.execute_catalog_package (we’ll take a closer look at this stored procedure in a bit):

    dilm4_16

    Custom.execute_catalog_package requires three parameters: @package_name, @project_name, and @folder_name. They are supplied by the “SQL Execute Child Package” Execute SQL Task from the SSIS variables mapped in the “FOREACH Application Package” Foreach Loop Container:

    dilm4_15

    As a result of the “FOREACH Application Package” Foreach Loop Container variables mappings, the “SQL Execute Child Package” Execute SQL Task will execute, calling the custom.execute_catalog_package stored procedure and passing it the values on each row, in order, of the resultset returned by the “SQL Get Framework Packages” Executequery.

    There’s more, but your head is hurting enough for one blog post.

    A Test Execution

    Executing the Parent.dtsx package in SSDT succeeds:

    dilm4_17

    A quick glance at Catalog Reports confirms all four Application Packages executed:

    dilm4_18

    Framework Browser

    There’s one last thing. You can view the contents of the SSIS Framework Community Edition using Framework Browser, another free utility from DILM Suite.

    When you first open Framework Browser you may be prompted to for “Defaults.” If so, click File—>Open on the Defaults form and navigate to a file named CommunityEdition.json:

    dilm4_19

    When you open CommunityEdition.json, the Defaults form will appear similar to this:

    dilm4_20

    Click the Save button to return to the Framework Browser main form.

    In the Catalog Instance textbox, enter the name of the SQL Server instance that hosts your SSIS Framework Community Edition and click the Connect button. You Framework metadata should load and appear similar to that shown in this screenshot:

    dilm4_21

    Framework Browser surfaces SSIS Framework Community Edition metadata.

    Conclusion

    In this post I demonstrated advanced options for executing SSIS packages in the SSIS Catalog. I demonstrated SSIS Catalog Browser, SSIS Framework Community Edition, Framework Browser, and Catalog Reports – free utilities and solutions that make up the DILM Suite that may help you as you implement your enterprise Data Integration Lifecycle Management (DILM). I even snuck in a screenshot of a custom SSIS task – the Execute Catalog Package task – of which I’ll write more later.

    :{>

    You might like working with Enterprise Data & Analytics because we like helping teams learn more about the SSIS Catalog.

    Learn More:
    Advanced SSIS Execution
    SSIS Academy: Using the SSIS Catalog Day 1 - Create the Catalog and Deploy
    SSIS Academy: Using the SSIS Catalog Day 2 - Package Execution and Monitoring

    Previous Posts in this Series:
    An Example of Data Integration Lifecycle Management with SSIS, Part 0
    An Example of Data Integration Lifecycle Management with SSIS, Part 1
    An Example of Data Integration Lifecycle Management with SSIS, Part 2
    An Example of Data Integration Lifecycle Management with SSIS, Part 3

    Related Training:
    SSIS Lifecycle Management (free recording, registration required) 
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago 
    IESSIS2: Immersion Event on Advanced Integration Services – Oct 2017, Chicago
    From Zero to Biml - 19-22 Jun 2017, London

  • The Recording for Advanced SSIS Execution is Available

    The recording for the Enterprise Data & Analytics webinar titled Advanced SSIS Execution is now available (registration required).

    Enjoy!

    :{>

    Learn More:
    SSIS Catalog Compare v2.0 Launch Event 7 Mar 2017!
    SSIS Catalog Management
    SSIS Lifecycle Management

    Related Training:
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago 
    IESSIS2: Immersion Event on Advanced Integration Services – Oct 2017, Chicago
    From Zero to Biml - 19-22 Jun 2017, London

  • SSIS Catalog Compare v2.0 Launch Event 7 Mar 2017!

    The Data Integration Lifecycle Management Suite (DILM Suite) helps SSIS Developers and Database Administrators manage SQL Server Integration Services (SSIS) deployments in the enterprise lifecycle.

    Join Kent Bradshaw and me at 1:00 PM EST 7 Mar 2017 for the SSIS Catalog Compare v2.0 Launch Event! In this free webinar we will demonstrate the value of using the DILM Suite to practice Data Integration Lifecycle Management. Join us and learn how to :

    • “Walk” an SSIS project though several lifecycle tiers
    • Detect differences between SSIS projects deployed to Production and QA lifecycle tiers
    • Automate SSIS comparisons, scripting, and deployment between SSIS Catalogs
    • Detect broken references in the SSIS Catalog

    What is the DILM Suite?

    Visit the DILM Suite website for a full list of the (mostly free!) tools! I can hear you thinking, “What does mostly free mean, Andy?” I’m glad you asked.

    SSIS Catalog Browser, shown below, is free and surfaces a full view of SSIS Catalog objects in a single view:

    CatC_2

    The SSIS Framework Community Edition integrated into the SSIS Catalog to provided extended SSIS execution functionality. It’s not only free, it’s open source!

    CatC_4

    SSIS Framework Browser (… you guessed it, also free!) supports the SSIS Framework Community Edition by surfacing Framework Applications and Packages.

    CatC_3

    SSIS Catalog Compare is an application that helps DBAs and SSIS Developers support and maintain SSIS in the DevOps enterprise. Compare, script, and deploy SSIS Catalog objects between SSIS Catalogs with alacrity. SSIS Catalog Compare delivers value because the utility helps DBAs and SSIS Developers efficiently manage enterprise SSIS deployments and configurations.

    CatC_1

    CatCompare is the command-line interface for SSIS Catalog Compare. DBAs and SSIS Developers use CatCompare to automate SSIS Catalog comparisons, deployments, and  synchronization.

    CatC_0

    In the DILM Tools webinar, Kent Bradshaw and I will demonstrate using these tools to improve operational efficiency and automate SSIS management.

    Register today!

    :{>

    I was honored to be interviewed by Carlos Chacon (@CarlosLChacon | SQL Data Partners) and Steve Stedman (@SqlEmt | Blog) for the SQL Data Partners podcast. We had a great discussion about the SSIS Catalog. We talked about how the SSIS Catalog works, Data Integration Lifecycle Management (DILM), SSIS Catalog Browser, SSIS Catalog Compare, and the Richmond SQL Server Community.

    If you’ve never listened to the SQL Data Partners podcast, it’s awesome! My favorite part is the SQL Family questions at the end!

    Enjoy!

    :{>

    You might like working with Enterprise Data & Analytics because we grok the SSIS Catalog.

    Learn More:
    Broken References in the SSIS Catalog
    SSIS Academy: Using the SSIS Catalog Day 1 - Create the Catalog and Deploy
    SSIS Academy: Using the SSIS Catalog Day 2 - Package Execution and Monitoring
    SSIS Academy: Using the SSIS Catalog Day 3 - SSIS Configuration
    SSIS Catalog Management
    SSIS Lifecycle Management

    Related Training:
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago 
    IESSIS2: Immersion Event on Advanced Integration Services – Oct 2017, Chicago
    From Zero to Biml - 19-22 Jun 2017, London

  • SQL Data Partners Podcast Episode 79: SSIS Catalog

    I was honored to be interviewed by Carlos Chacon (@CarlosLChacon | SQL Data Partners) and Steve Stedman (@SqlEmt | Blog) for the SQL Data Partners podcast. We had a great discussion about the SSIS Catalog. We talked about how the SSIS Catalog works, Data Integration Lifecycle Management (DILM), SSIS Catalog Browser, SSIS Catalog Compare, and the Richmond SQL Server Community.

    If you’ve never listened to the SQL Data Partners podcast, it’s awesome! My favorite part is the SQL Family questions at the end!

    Enjoy!

    :{>

    You might like working with Enterprise Data & Analytics because we grok the SSIS Catalog.

    Learn More:
    SSIS Academy: Using the SSIS Catalog Day 1 - Create the Catalog and Deploy
    SSIS Academy: Using the SSIS Catalog Day 2 - Package Execution and Monitoring
    SSIS Academy: Using the SSIS Catalog Day 3 - SSIS Configuration
    SSIS Catalog Management
    SSIS Lifecycle Management

    Referenced Posts:
    Deploying SSIS Projects to a Restored SSIS Catalog (SSISDB)
    Stairway to Integration Services
    DILM Suite
    WideWorldImporters: The new SQL Server sample database

    Related Training:
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago 
    IESSIS2: Immersion Event on Advanced Integration Services – May 2017, Chicago
    From Zero to Biml - 19-22 Jun 2017, London

  • An Example of Data Integration Lifecycle Management with SSIS, Part 3

    In this post I will demonstrate how to use SSIS Catalog Environments, References,  and Reference Mappings to override SSIS parameter values at execution time.

    There three sources of SSIS parameter values:

    1. Design-time defaults – these are the parameter values developers use when building the SSIS project and packages.
    2. SSIS Catalog Literal overrides – as part of the previous post in this series, An Example of Data Integration Lifecycle Management with SSIS, Part 2, I demonstrated overriding the value of a string parameter using an SSIS Catalog Literal override.
    3. SSIS Catalog Reference Mapping overrides – we will focus on using Parameters, Environments, References, and Reference Mappings to override parameter values in this post.

    SSIS Parameters, Catalog Environments, and References

    At the end of 2016, Kent Bradshaw and I delivered a webinar that covers this topic. It’s called SSIS Academy: Using the SSIS Catalog Day 3 - SSIS Configuration and you can access it for free, although registration is required. In that webinar, Kent and I talked about logical and physical models for SSIS parameters, Catalog environments, and references.

    Let’s begin by looking at parameters.

    SSIS Parameters

    SSIS parameters are available only for SSIS projects developed using the Project Deployment Model (the default project deployment model in SSIS 2012, 2014, and 2016). Parameters may be scoped at the package or project. The image below presents a logical diagram of package and project parameters in an SSIS Project deployed to the SSIS Catalog:

    image

    SSIS Catalog Environments and Catalog Environment Variables

    Unlike SSIS Parameters, which are configured at design time when developing SSIS packages and projects in SQL Server Data Tools (SSDT), SSIS Catalog Environments are creatures of the SSIS Catalog.

    A Catalog Environment is a collection of zero-to-many Catalog Environment Variables. The variables contain Name, Data Type, Description, Value, and Sensitive attributes, as we’ll see in a bit. Although it’s technically possible to create an SSIS Catalog Environment that contains no Catalog Environment Variables, I cannot think of a use case for doing so. Catalog Environments are pretty useless without at least one Catalog Environment Variable. The image below presents a logical diagram of an SSIS Catalog Environment that contains one Catalog Environment Variable:

    LogicalSSISCatalogEnvironment

    References

    A Reference is a “link” between a single Catalog Environment and a single SSIS Project or Package. The image below presents a logical diagram of Reference between a Catalog Environment and a Catalog Project:

    LogicalSSISCatalogReference

    Reference Mappings

    A Reference contains zero-to-many Reference Mappings.

    One way to describe Reference Mappings: Think of a Reference, then drill down one level at the Catalog Environment to a single Catalog Environment Variable and one level at the SSIS Project (or Package) to a single Parameter. The image below presents a logical diagram of Reference Mapping between a Catalog Environment Variable (contained in a Catalog Environment) and a Project Parameter (contained in a Catalog Project):

    image

    Summarizing So Far…

    These four objects reside solely in the SSIS Catalog:

    • Catalog Environments
    • Catalog Environment Variables
    • References
    • Reference Mappings

    These four objects are related in the following ways:

    • A Catalog Environment contains zero-to-many Catalog Environment Variables.
    • A Reference may be configured between a single Catalog Environment and a single Project (or Package).
    • A Reference Mapping may be configured between a single Catalog Environment Variable and a single Project (or Package) Parameter.

    You may be thinking, “This is awfully complex, Andy. Why would anyone use Environments and References?”

    Why This Complexity?

    My argument for embracing (or ignoring) the complexity of SSIS Catalog configuration is straightforward: Externalization – the act of storing execution-time values outside of the object to be executed – is one way to achieve portability, a software development best practice.

    As an architect I loathe complexity. Whenever I encounter (or opt to develop) complexity in a software project, I question it. I ask others to question it. I don’t always arrive at the right conclusion, but I challenge complexity at every turn.

    I’ve helped several organizations implement SSIS Catalog portability using Environments and References. We all agree that the solution is complex, but most also agree that the complexity is worth the flexibility achieved thereby.

    Implementing Catalog Environments and References

    If you don’t have the demo project, you can download it here and follow the instructions in this post to deploy it.

    To implement using our demo project, open SQL Server Management Studio (SSMS) and connect to the instance of SQL Server that hosts the Catalog containing our demo project. In SSMS Object Explorer, expand the Integration Services Catalogs node, then drill down to our SSIS Project Folder. Right-click the Environments virtual folder and click “Create Environment” as shown:

    dilm_6

    When the Create Environment window displays, supply an Environment Name (I named mine “envMedicalData”) and optional Environment Description, and then click the OK button:

    dilm_7

    The newly-created Catalog Environment will appear beneath the Environments virtual folder. Right-click the Catalog Environment and click Properties (or simply double-click the Catalog Environment) to open the Environment Properties editor:

    dilm_8

    Click the Variables page and begin configuring a new Environment Variable by typing in the Name cell. I configured my Catalog Environment Variable thus:

    • Name: “MedicalDatabaseConnectionString”
    • Type: String
    • Description: (blank)
    • Value: An OLE DB connection string aimed at the SQL Server instance for my Medical database.

    You may recall we overrode this value in the previous post using a Literal override (click the image to enlarge):

    dilm_9

    Click the OK button to save the Environment Properties. The Catalog Environment envMedicalData is now created and configured with a single Catalog Environment Variable named MedicalDatabaseConnectionString.

    Remember, a Reference is a “link” between a Catalog Environment and a Project (or Package). We will now configure a Reference between our demo project and envMedicalData.

    Right-click the Project and click Configure, as shown:

    dilm_10

    When the Configure <Project> window displays, click the References page. Click the Add button to create a Reference between our project and a Catalog Environment:

    dilm_11

    The Browse Environments dialog displays. References to a Catalog Environment can either be Relative or Absolute. A Relative reference points to a Catalog Environment that exists in the same Catalog Folder as the SSIS Project. An Absolute references points to a Catalog Environment that resides in any Catalog Folder other than the Catalog Folder that contains the SSIS Project. Since we created the envMedicalData Catalog Environment in the same Catalog Folder (Demo) as our project, we can create either a Relative or Absolute reference to envMedicalData. And it doesn’t matter which we create, they both work the same. The image below shows me configuring a Relative reference:

    dilm_12

    After selecting the Catalog Environment, click the OK button to return to the Configure window. The Environment Name and Folder are now displayed on the References page (“.” indicates “local” or a relative reference):

    dilm_13

    Click on the Parameters page and click the ellipsis beside the MedicalDatabaseConnectionString parameter:

    dilm_28

    When the Set Parameter Value window displays, select the “Use environment variable” option and select the MedicalDatabaseConnectionString Catalog Environment Variable from the dropdown:

    dilm_15

    Click the OK button to save and close the Set Parameter Value dialog. The MedicalDatabaseConnectionString Project Parameter value property is now mapped, via the reference, to the MedicalDatabaseConnectionString Catalog Environment Variable. The Configure window displays Reference Mappings by supplying the name of the Catalog Environment Variable, underlined, in the Value cell:

    dilm_16

    Click the OK button to complete the Configuration of our Project.

    Test Execution

    In SSIS Academy: Using the SSIS Catalog Day 2 - Package Execution and Monitoring we walked through executing an SSIS Package in the Catalog, and monitoring that execution. That’s a good test exercise because it will reinforce what you learned last time. If you’re playing along at home, execute the SSIS Package named ProviderGetFile.dtsx.

    When we right-click the ProviderGetFile.dtsx package and click “Execute,” we see something different (click the image to enlarge it). We cannot click the OK button until we resolve the error:

    The parameter “MedicalDatabaseConnectionString” is configured to use an environment variable, but no environment has been selected.  Check the "Environment" checkbox and specify the environment to use, or specify a literal value for the parameter.

    We can remedy this by checking the Environment checkbox and selecting a Reference from the dropdown:

     dilm_31a

    When the Environment checkbox is checked, the Reference dropdown is enabled. Since we configured one reference, we have only one option, the “.\envMedicalData” reference:

    dilm_32

    Once the reference is configured, the OK button is enabled. We can execute the SSIS Package. We may view the Overview Report – either the version built into SSMS or the Catalog Reports version from DILM Suite. I’m going to use Catalog Reports because, well, I wrote it! :)

    dilm_33

    We see the package is running at the time of this screenshot. Click the Overview link to view the Execution Parameters:

    dilm_34

    The value supplied to the execution of ProviderGetFile.dtsx’s MedicalDatabaseConnectionString package parameter came from the MedicalDatabaseConnectionString  Catalog Environment Variable of the same name (MedicalDatabaseConnectionString). How can we be sure? Update the value so that the connection remains the same but the connection string value is different. All I’ve changed here is how I reach the Data Source; I’ve updated “vmSql16” to “(local)”:

    dilm_35

    Re-executing and viewing the Overview Report confirms the MedicalDataConnectionString parameter value is now managed outside the SSIS package in the Catalog Environment Variable named MedicalDataConnectionString:

    dilm_36

    And here we have the updated value from this later execution.

    One More Thing…

    I can hear you thinking, “Andy, we sure did a lot of clicking to view those values and configurations in the SSIS Catalog. Is there an easier way to see what’s configured?” Yes. Yes, there is an easier way to view SSIS Catalog configurations metadata. Better yet, it’s free!

    It’s called SSIS Catalog Browser (beta) and, like Catalog Reports, is part of the DILM Suite . In the image below, please note you can view:

    • The Catalog Environment Variable, data type, and value (the lower circle),
    • The (Project) Reference and Reference Mapping (middle circle); and
    • The (Project) Parameter Override (upper circle).

    The Catalog configuration is surfaced in a single view. You do not have to open additional windows or dialogs to get a complete picture of Project and Package configuration in the SSIS Catalog.

    dilm_37

    As I mentioned earlier, SSIS Catalog Browser (beta) is free!

    Conclusion

    In this post I demonstrated how to use SSIS Catalog Environments, References,  and Reference Mappings to override SSIS parameter values at execution time.

    :{>

    You might like working with Enterprise Data & Analytics because we like helping teams learn more about the SSIS Catalog.

    Learn More:
    SSIS Academy: Using the SSIS Catalog Day 1 - Create the Catalog and Deploy
    SSIS Academy: Using the SSIS Catalog Day 2 - Package Execution and Monitoring
    SSIS Academy: Using the SSIS Catalog Day 3 - SSIS Configuration

    Previous Posts in this Series:
    An Example of Data Integration Lifecycle Management with SSIS, Part 0
    An Example of Data Integration Lifecycle Management with SSIS, Part 1
    An Example of Data Integration Lifecycle Management with SSIS, Part 2

    Related Training:
    SSIS Lifecycle Management (free recording, registration required) 
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago 
    IESSIS2: Immersion Event on Advanced Integration Services – May 2017, Chicago
    From Zero to Biml - 19-22 Jun 2017, London

  • An Example of Data Integration Lifecycle Management with SSIS, Part 2

    In this post I will demonstrate how to use the SSIS Catalog to execute and monitor an SSIS project.

    Manual SSIS Package Execution in the SSIS Catalog

    To execute an SSIS Package manually using the SSIS Catalog, connect to the SQL Server instance that hosts the Catalog using SQL Server Management Studio (SSMS). Once connected, open Object Explorer and drill into the Integration Services Catalogs node until you locate the SSIS package you desire to execute. I’m going to execute the ProviderGetFile.dtsx package. Right-click the package and click Execute (click the image to enlarge it):

    dilm_17

    The Execute package window displays. For this SSIS project, please pay particular attention to the MedicalDatabaseConnectionString parameter value (click the image to enlarge it):

    dilm_18

    If you downloaded the demo project, it includes a SQL Server 2016 backup of a database named Medical. Restore the Medical database to a test instance of SQL Server 2016 (or higher) before proceeding. Then follow these steps to update the MedicalDatabaseConnectionString parameter value.

    1. Click the ellipsis to the right of the MedicalDatabaseConnectionString parameter value (click the image to enlarge it):

    dilm_19

    In the Edit Literal Value dialog, edit the connection string in the Value property. Set the Data Source portion of the connection string to the SQL Server instance to which you restored the Medical database (click the image to enlarge it):

    dilm_20

    Click the OK button and your Execute Package window should now reflect an overridden value for the MedicalDatabaseConnectionString parameter value (click the image to enlarge it):

    dilm_21

    Values that are overridden with Literals are shown in bold font.

    Monitoring Using the SSIS Catalog Reports Solution in SSMS

    Click the OK button to begin executing the SSIS package. You will be prompted with a dialog similar to that shown below. Click the Yes button to view the Overview report:

    dilm_22

    The Overview report contains information about this execution of this SSIS package (click the image to enlarge it):

    dilm_23

    The ProviderGetFile.dtsx package takes some time to execute. On my virtual machine, it took about 5.5 minutes (click the image to enlarge it):

    dilm_24

    Monitoring Using the Catalog Reports Solution from DILMSuite

    I really like the SSIS Catalog Reports that are built into SSMS. They are informative and intuitive. I have one complaint, though, and that is that the reports are built into SSMS. Why is that an issue? Some people in the enterprise have a legitimate need to view these reports. As you can see by viewing the screenshots above, the Overview Report can provide operational and historical data regarding the execution of an SSIS package. This information is vital for enterprise data integration.

    But SSMS contains other functionality that is, frankly, not vital for many consumers of the Overview Report. The additional nodes highlighted in the screenshot below, for example, are unnecessary for people in non-database-savvy roles within an enterprise.

    One may argue – fairly, even – that enterprise security can and should manage access in such a way that users who access the Overview Report do not have access to the highlighted nodes in the following screenshot. We are in violent agreement. But… must we surface these nodes and their accompanying functionality to users who only need to view the Overview Report?

    dilm_26

    One of the first Data Integration Lifecycle Management (DILM) Suite solutions I built was Catalog Reports. Catalog Reports is a relatively simple and straightforward version of some of the SSIS Catalog Reports embedded in SSMS. The main difference is Catalog Reports is a SQL Server Reporting Services (SSRS) solution.

    It’s free.

    And it’s open source. Here’s a screenshot of the Overview Report for the same execution above (click the image to enlarge it):

    dilm_27

    As you can see by mere observation, the reports are not identical. Most of the information included in the SSMS-version of the Overview Report is, however, present in the DILM Suite version. Plus it’s open source, so if you’d like for the report to surface more data or appear differently, you have the source.

    Best of all, the DILM Suite version provides no additional access to SQL Server functionality. Must you still manage and maintain SQL Server security? That’s a silly question and the answer is, “Of course!” DILM Suite’s Catalog Reports helps by surfacing only data and functionality required for enterprise data integration reporting.

    Conclusion

    In this post I demonstrated how to use the SSIS Catalog to execute and monitor an SSIS project.

    :{>

    You might like working with Enterprise Data & Analytics because we like helping people understand the SSIS Catalog.

    Learn More:
    SSIS Academy: Using the SSIS Catalog Day 1 - Create the Catalog and Deploy
    SSIS Academy: Using the SSIS Catalog Day 2 - Package Execution and Monitoring

    Previous Posts in this Series:
    An Example of Data Integration Lifecycle Management with SSIS, Part 0
    An Example of Data Integration Lifecycle Management with SSIS, Part 1

    Related Training:
    SSIS Lifecycle Management (free recording, registration required) 
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago 
    IESSIS2: Immersion Event on Advanced Integration Services – May 2017, Chicago
    From Zero to Biml - 19-22 Jun 2017, London

  • Introducing astreams

    astreams_0

    For months, I’ve received pings about a screenshot I posted on social media a year or so ago.

    Plus I needed to wind down. You real runners will know what I mean: You know how you feel after you’ve covered some distance (for you)? You need to walk around for a bit, you can’t just stop cold. You need to wind down. I needed that after the effort to release SSIS Catalog Compare v2.

    astreams became the coding-equivalent of a wind down.

    I’d been using another application that kind-of-sort-of did what astreams does but astreams is cleaner, in my opinion.

    So, how does one use astreams? Open it. It’s empty:

    astreams_1

    Right-click in the treeview and click Add Stream to begin adding a new stream:

    astreams_2

    Type the name of the new stream:

    streams_14

    Press Enter when done:

    astreams_4

    Right-click any stream to add a sub-stream:

    astreams_5

    As before, begin typing to enter the sub-string’s name:

    astreams_6

    Also as before, press Enter when done:

    astreams_7

    You can nest streams as deeply as you like:

    astreams_8

    You may equate streams with tasks. Check them off as you complete each stream:

    astreams_9

    When you’re done, you can save an individual stream by right-clicking and selecting “Save Stream File.” You can save all the streams loaded by right-clicking and selecting “Save Streams Folder”:

    astreams_10

    Select a folder and click OK:

    astreams_11

    Your streams are stored in JSON format in the selected folder:

    streams_12

    You can manage streams by copying, pasting, editing, and deleting the JSON files, if you’d like.

    As you can see by viewing one of my streams folders, I use the utility often:

    streams_13

    It is a simple and flexible utility. I find it very helpful for tracking what I’ve done and what I need to do.

    You may purchase astreams here.

    Enjoy!

    :{>

  • The Recording for SSIS Catalog Management is Available

    The recording for the Enterprise Data & Analytics webinar titled “SSIS Catalog Management” is now available (registration is required).

    In the SSIS Academy: Using the SSIS Catalog series, Kent and I walked through the SSIS Catalog functionality built into SQL Server Management Studio (SSMS). This webinar picks up where SSIS Academy: Using the SSIS Catalog, Day 3 – Configuration left off.

    In this webinar, Kent Bradshaw and I discussed and demonstrated some enterprise management shortcomings of the SSIS Catalog as it ships, and some of the tools and solutions we use to overcome those shortcomings.

    Learn More:
    SSIS Academy: Using the SSIS Catalog, Day 3 – Configuration

    Related Training:
    SSIS Lifecycle Management (free recording, registration required) 
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago 
    IESSIS2: Immersion Event on Advanced Integration Services – May 2017, Chicago
    From Zero to Biml - 19-22 Jun 2017, London

    :{>

  • An Example of Data Integration Lifecycle Management with SSIS, Part 1

    In this post I will provide some details about the demo SSIS project. I will then focus on version control and deployment.

    What DILM Is and Is Not

    Data Integration Lifecycle Management (DILM) is not about data integration development.

    DILM is about everything else:

    • Configurations Management
    • Version Management
    • Deployment
    • Execution

    Although DILM is not about development, implementing DILM will impact the design of SSIS solutions.

    The remainder of this post focuses on obtaining the solution, getting some pieces of DILM in place, and deploying the SSIS project to the SSIS Catalog.

    The Setup

    I’m using SQL Server Data Tools 2016 (Visual Studio 2015) on a virtual machine named vmSql16. The VM’s operating system is Windows Server 2016.

    The Project

    My SSIS project is named MedicalDataDemo. It contains two SSIS packages of relative complexity: ProviderGetFile.dtsx and ProviderStage.dtsx. ProviderGetFile will attempt to connect to the CMS website and download the latest National Provider Index (NPI) zip file, then it unzips the file. (Yes, it does all this in SSIS using a couple/three snappy design patterns.) The file name and file status are stored in a database named Medical. The ProviderStage package reads the name of the file and loads its contents to a table in the Medical database. There’s a 2016 version backup of the Medical database included in the project, which you can download the project in this state from Dropbox here.

    Version Control

    If you download the project and open it in SSDT, you may get source control messages. I added the project to source control, then “unbound” it before zipping it and sending it to Dropbox. Hopefully that was enough and you won’t see those pesky source control prompts.

    That said, please use source control. If you do not have access to version control, create a (free) account at visualstudio.com. I use visualstudio.com version control for internal work; I’ve been using it since it was in beta. 

    “There are two types of developers, those who use source control and those who will.” – Andy, circa 2005

    Once source control is set up, SSDT Solution Explorer will add indicators about the state of the solution (click to enlarge):

    dilm__1

    Deployment

    The screenshot above shows me deploying MedicalDataDemo. Right-click the project name – MedicalDataDemo – and then click Deploy (click to enlarge):

    dilm_0

    This opens the Integration Services Deployment Wizard (click to enlarge):

    dilm_1

    The first page of the Integration Services Deployment Wizard is an introduction. Click the Next button to open the next page in the process (click to enlarge):

    dilm_2

    Note that the “Select Source” page is skipped. That’s intentional; the wizard knows you’re deploying from SSDT.

    On this page you need to do some work. First, select a SQL Server instance that hosts an SSIS Catalog. If you’re scratching your head and asking, “What’s an SSIS Catalog?” that’s ok. Please see SSIS 2016 Administration: Create the SSIS Catalog for more information about setting up an SSIS Catalog.

    Click the Next button to open the next page in the process (click to enlarge):

    dilm_3

    The Review page contains some helpful information. Did you know you can execute SSIS project deployments from the command line? You can, and the arguments portion of the command line are shown on the Review page of the Integration Services Deployment Wizard. Click the Deploy button to deploy the project and open the next page in the process (click to enlarge):

    dilm_4

    Once deployment is complete, the Integration Services Deployment Wizard should appear as shown above.

    If you open SQL Server Management Studio (SSMS) and connect to the SQL Server instance to which you deployed the project, you can expand the Integration Services Catalogs node and drill down to the SSIS project you deployed:

    dilm_6a

    The SSIS packages in our project are now ready for execution, but they will execute with the default values we configured at design time. Our next step is to set up some external configurations.

    Conclusion

    In this post we discussed the demo SSIS project, version control, and deployment. In the next installment, I will demonstrate how to use the SSIS Catalog to execute and monitor these packages.

    :{>

    You might like working with Enterprise Data & Analytics because we grok the SSIS Catalog.

     

    Learn More:
    SSIS Academy: Using the SSIS Catalog Day 1 - Create the Catalog and Deploy

    Previous Posts in this Series:
    An Example of Data Integration Lifecycle Management with SSIS, Part 0

    Related Training: 
    SSIS Lifecycle Management (free recording, registration required) 
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago 
    IESSIS2: Immersion Event on Advanced Integration Services – May 2017, Chicago 
    From Zero to Biml - 19-22 Jun 2017, London
More Posts Next page »

This Blog

Syndication

News


My Companies



Community Awards

Friend of Red Gate

Contact Me

Archives

Privacy Statement