Packaging SSIS Catalog Deployments

I love the SSIS Catalog. It’s an elegant piece of data integration engineering and I cannot say enough positive things about it. Packaging SSIS Catalog deployments can be tricky, though.

The SSIS Catalog is a framework. Frameworks manage execution, configuration, and logging; and the SSIS Catalog handles each task with grace. Like I said, I love it!

But…

(You knew there was a “but…” coming, didn’t you?)

A Tale of Two Audiences

There are two audiences for the SSIS Catalog, two groups of consumers:

  1. Administrators
  2. Developers
  3. Release Managers

I listed three. Because…

Administrators

(click to enlarge)

SSIS is often administered by database administrators, or DBAs. I admire DBAs. It’s often a thankless job – more like a collection of jobs all rolled into one (and subsequently paid as if its one job…).

I believe the SSIS Catalog interface presented to DBAs in SQL Server Management Studio is sufficient.

My complaint is the SSIS administrator has to expand a handful of nodes in Object Explorer and then right-click to open the SSIS project configuration window and then double-click each referenced SSIS Catalog environment to determine which value is configured for use when an SSIS package is executed.

Click the screenshot above to see what I mean. Configuring SSIS Catalog deployments in SSMS is challenging. I find it… clunky. Once I understood all the windows, what they meant and how to configure an SSIS package and project deployed to the SSIS Catalog, this made sense. But – in my opinion – this interface works against comprehension.

Does this interface work, though? It certainly does. When I teach people how to use the SSIS Catalog, I show them how to use the Object Explorer interface provided in SSMS.

(click to enlarge)

I don’t stop there, however, because I built one solution to the problem. I call my solution SSIS Catalog Browser. If you click to enlarge this image you will note I am viewing the configuration of the same parameter displayed in the SSMS image above. I find this interface cleaner.

Do administrators still need to understand how to configure SSIS Catalog deployments and SSIS packages and projects deployed to the SSIS Catalog? You bet. There is no substitute for understanding. SSIS Catalog Browser surfaces the same metadata displayed in the SSMS Object Explorer. The only difference is Catalog Browser is easier to navigate – in my opinion.

Best of all, Catalog Browser is free.

Developers and Release Managers

SSIS developers and release managers (DevOps release teams) need more functionality. As I wrote in DILM Tiers for the SSIS Enterprise, an enterprise should have a minimum of four Data Integration Lifecycle Management (DILM) tiers to manage enterprise data integration with SSIS. Those tiers need to be:

  1. Development – an environment where SSIS developers build SSIS packages and projects. SSIS developers need permission / rights / roles to utterly destroy the database instances in Dev. If the SSIS developers lack this ability, you have “an environment named Development but not a Development environment.” There is a difference.
  2. Test or Integration – an environment where SSIS developers have permission / rights / roles to deploy, configure, execute, and view logs related to SSIS packages and projects.
  3. UAT or QA (User Acceptance Testing or Quality Assurance or any environment other than Production, Test, or Development) – an environment that mimics Production in security, permission / rights / roles. Developers may (or may not) have read access to logs, source, and destination data. SSIS administrators (DBAs or DevOps / Release teams) own this environment. The person performing the deployment to Production should perform the deployment to UAT / QA / Whatever because I do not want the Production deployment to be the very first time this person deploys and configures this SSIS project.
  4. Production.

I architect data integration environments in this manner to support DILM (Data Integration Lifecycle Management) with SSIS, as I wrote in Data Integration Lifecycle Management with SSIS.

Viewing the contents of an SSIS Catalog is not enough functionality to manage releases. Why, then, do I include developers? Because…

SSIS developers create the initial SSIS Catalog deployments in the DILM DevOps cycle.

I cannot overemphasize this point. Developers need an environment where they are free to fail to build SSIS. They aren’t free to succeed, in fact, unless and until they are free to fail.

Have you ever heard a developer state, “It works on my machine.”? Do you know why it works on their machine? Defaults. They coded it up using default values. The defaults have to work or the developer will not pass the code along to the next tier.

How does an SSIS developer know they’ve forgotten to parameterize values?
How do they figure this out?

It’s impossible to test for missing parameters in the Development environment.

The answer is: SSIS developers must deploy the SSIS project to another environment – an environment separate and distinct from the Development environment – to test for missing parameterization.

To review: SSIS developers need a Development environment (not merely an environment named Dev) and they need a different environment to which they can deploy, configure, execute, and monitor logs.

Error Elimination

Having the SSIS developers build and script the SSIS Catalog deployments eliminates 80% of deployment configuration errors (according to Pareto…).

Having SSIS administrators practice deployment to UAT / QA / Whatever eliminates 80% of the remaining errors.

Math tells me an enterprise practicing DILM in this manner will experience a 4% deployment error rate. (Want to knock that down to 0.8%? Add another tier.)

Packaging SSIS Deployment

I will not go into functionality missing from the SSMS Object Explorer Integration Services Catalogs node (nor the underlying .Net Framework assemblies). I will simply state that some functionality that I believe should be there is not there.

(click to enlarge)

I don’t stop there, however, because I built one solution to the problem. I call my solution SSIS Catalog Compare. If you click the image to enlarge it, you will see a treeview that surfaces SSIS Catalog artifacts in the same way as SSIS Catalog Browser (they share a codebase). You will also see the results of a comparison operation, and the user initiating the packaging of an SSIS folder deployment by scripting the folder and its contents.

The result is a file system folder that contains T-SQL script files and ISPAC files for each SSIS Catalog configuration artifact:

  • Folder
  • SSIS Project
  • Environment
  • Reference
  • Configured Literals

You can use SSIS Catalog Compare to lift and shift SSIS Catalog objects from any environment to any other environment – or from any DILM tier to any other DILM tier – provided you have proper access to said environments and tiers.

This includes lifting and shifting SSIS to the Azure Data Factory SSIS Integration Runtime, also know as Azure-SSIS.

Zip up the contents of this file system folder, attach it to a ticket, and let your enterprise DevOps process work for data integration.

Conclusion

The SSIS Catalog is a framework, and a pretty elegant framework at that. Some pieces are clunky and other pieces are missing.

DILM Suite helps.

Honored to Present Lift and Shift SSIS to ADF at #Azure DataFest Reston

I am honored to deliver Lift and Shift SSIS to ADF at the Azure DataFest in Reston Virginia 11 Oct 2018!

Abstract

Your enterprise wants to use the latest cool Azure Data Analytics tools but there’s one issue: All your data are belong to the servers on-premises. How do you get your enterprise data into the cloud?

In this session, SSIS author and trainer Andy Leonard discusses and demonstrates migrating SSIS to Azure Data Factory Integration Runtime.

Register today!

:{>

Free Stuff for People Who Give Back: Announcing 2019 Scholarships

I haven’t advertised this in the past and… I’m not sure why: I donate licenses for SSIS Catalog Compare and (non-free) SSIS Framework Editions – and subscriptions to Biml Academy and SSIS Academy – and Enterprise Data & Analytics Training – to individuals who work for charities and non-profit organizations. I am honored to announce our 2019 Scholarships.

I was inspired to make this public after reading this post over at Brent Ozar Unlimited.

Free Stuff for Charities and Non-Profit Organizations

  

Do you work for a charity or non-profit organization? Submit your application today.

Free Training and Discounted Consulting Services

In addition to donating free licenses to our software and online training sites, Enterprise Data & Analytics offers a discounted rate to charities and non-profits for consulting services and free access to Enterprise Data & Analytics Training.

We are here to help.™ How may we serve you? Contact us today and let us know!

Announcing the Fundamentals of Azure Data Factory Course!

I am excited to announce a brand new course (it still has that new course smell) from Brent Ozar Unlimited and honored to deliver it! This one-day, live, online course is titled Fundamentals of Azure Data Factory and it’s designed to introduce you to Azure Data Factory (ADF).

There will be demos.
Live demos.
Lots of live demos!

Abstract

Azure Data Factory, or ADF, is an Azure PaaS (Platform-as-a-Service) that provides hybrid data integration at global scale. Use ADF to build fully managed ETL in the cloud – including SSIS. Join Andy Leonard – authorblogger, and Chief Data Engineer at Enterprise Data & Analytics – as he demonstrates practical Azure Data Factory use cases.

In this course, you’ll learn:

  • The essentials of Azure Data Factory (ADF)
  • Developing, testing, scheduling, monitoring, and managing ADF pipelines
  • Lifting and shifting SSIS to ADF SSIS Integration Runtime (Azure-SSIS)
  • ADF design patterns
  • Data Integration Lifecycle Management (DILM) for the cloud and hybrid data integration scenarios

To know if you’re ready for this class, look for “yes” answers to these questions:

  • Do you want to learn more about cloud data integration in Azure Data Factory?
  • Is your enterprise planning to migrate its data, databases, data warehouse(s), or some of them, to the cloud?
  • Do you currently use SSIS?

The next delivery is scheduled for 10 Dec 2018. Register today!

I hope to see you there.

:{>

Anatomy of a Catalog Reference Script in SSIS Catalog Compare

A Catalog Reference script is automatically generated from several methods in SSIS Catalog Compare’s Catalog object named CatalogBase.

Header Documentation

The script begins with a header documentation similar to that shown here:

(click to enlarge)

When executed, the Project Reference script header documentation portion displays in the Messages output window of SQL Server management Studio (SSMS):

The statements returned in the Messages tab of SQL Server Management Studio (SSMS) are designed to be copied and stored. The authors recommend enterprises use a ticketing system to manage and track the deployment of enterprise scripts. Before closing a ticket to create a Catalog Reference, the deploying agent is advised to copy the contents of the Messages tab and paste them into the Notes section of the ticket for auditing purposes.

Status and Conditions Checks

The next portion of the Catalog Reference script tests for the existence of prerequisite artifacts:

Prerequisites for a Project Reference include:
• Catalog Folder
• SSIS Project
• Catalog Environment

When executed, this portion of the script returns status messages for prerequisites similar to those shown below:

Create the Reference

The next portion of the Reference Script creates the Reference which is a relationship between an SSIS Catalog Environment and an SSIS Project (or Package). An example of this portion of the script is shown here:

Once this portion of the script is executed, a message similar to that shown in the figure above is returned if the reference is created:

If the script detects the reference already exists, a message similar to that shown below is returned:

Clear the Parameter Value

The next portion of the Reference script clears the parameter value:

The messages generated by this portion of the References script appear similar to those show below:

Set the Parameter Value

The final portion of the script builds the Reference Mapping – the relationship between a Catalog Environment Variable and a Parameter that the Environment Variable value will override at execution-time:

When executed, this portion of the script generates a message similar to that shown below:

After executing the Project Reference script in the target instance, click the Refresh button in SSIS Catalog Compare to observe the updated SSIS Catalog state of the target SSIS Catalog instance as shown here:

Conclusion

As you may glean from this analysis of one script generated for SSIS Catalog References management, the Transact-SQL for scripting SSIS Catalog artifacts in SSIS Catalog Compare is rigorous and includes several existence and error-condition checks prior to performing any updates. The script is designed to be idempotent, as well, meaning the script will succeed and the results will be repeatable and predictable each time the script is executed – and that the script itself is re-executable.

Learn more about SSIS Catalog Compare!

Anatomy of an Environment Script in SSIS Catalog Compare

An SSIS Catalog Environment script is automatically generated from several methods in SSIS Catalog Compare’s Catalog object named CatalogBase.

Declarations and Header Documentation

The script begins with a declaration of Transact-SQL parameters that support SSIS Catalog Environment Variables contained within an SSIS Catalog Environment. These parameters are placed at the top of the script for easy access by release management personnel, DBAs, DevOps, or other specialists responsible for deployment and deployment testing:

(click to enlarge)

Script documentation follows and is recorded as both Transact-SQL documentation and then printed so it will be part of the output found in the Messages window:

When executed, this portion of the script outputs messages suitable for copying and pasting into the Notes field of a ticket used by enterprise DevOps teams:

The last piece of the script header is the deployment output message, for which the script is shown here:

When executed, this portion of the script produces output similar to that shown here:

Status and Conditions Checks

The next section of the artifact script checks for the existence of the Catalog Folder which is the only prerequisite for a Catalog Environment. An example:

When executed, this portion of the script produces a message similar to that shown:

Catalog Environment Check / Creation

The next portion of the script checks for the existence of the Catalog Environment and creates it if it does not exist:

If the script creates the Environment, the output appears similar to this:

If the environment does not exist, the script informs the individual executing the script:

Environment Variables

The final portion of the script checks for the existence of the Environment Variables and responds accordingly. This is a three-step process:

  1. Drop the Environment Variable if it exists.
  2. Create the Environment Variable.
  3. Set the Environment Variable value.

If the Environment Variable exists the script drops it. Why? SSIS Catalog Compare wants to be sure the environment variable is created with the proper data type and initial values.

The next step is creation of the Environment Variable.

Finally, the Environment Variable value is set. This is somewhat redundant as the value of the Environment Variable is initialized when the Environment Variable is created in the previous step.

An example of the Transact-SQL for this portion of the script is shown here:

After executing this portion of the script, messages similar to those shown below are displayed in the Messages output:

If the SSIS Catalog Environment Variable exists when the script is executed, it is first dropped and the output messages reflect this:

Catalog Environments, Post-Script-Execution

After executing the Catalog Environment script in the target instance, click the Refresh button in SSIS Catalog Compare to observe the updated SSIS Catalog state of the target SSIS Catalog instance:

Conclusion

As you may glean from this analysis of one script generated for SSIS Catalog Environments management, the Transact-SQL for scripting SSIS Catalog artifacts in SSIS Catalog Compare is rigorous, containing several existence and error-condition checks prior to performing any updates. The script is designed to be idempotent, as well, meaning the script will succeed and the results will be repeatable and predictable each time the script is executed – and that the script itself is re-executable.

Learn more about SSIS Catalog Compare!

Anatomy of an SSIS Catalog Connection Literals Script in SSIS Catalog Compare

An SSIS Catalog Connection Literals script is automatically generated using several methods in SSIS Catalog Compare’s Catalog object named CatalogBase.

Declarations and Header Documentation

(click to enlarge)

The script begins with a declaration of Transact-SQL parameters that will be used to provide literal overrides. These parameters are placed at the top of the script for easy access by release management personnel, DBAs, DevOps, or other specialists responsible for deployment and deployment testing.

(click to enlarge)

The next section provides feedback for the professional deploying the script. The feedback includes the same information contained in the head documentation, followed by deployment feedback.

(click to enlarge)

When executed, this documentation section returns messages similar to that shown here:

These messages are intended to be copied and stored in the Notes field of a ticketing system in a DevOps enterprise. Note the detail contained herein:

  • Script Name – the path to the file used to perform the operation.
  • Generated From – the SQL Server instance of the SSIS Catalog host from which the script was generated.
  • Catalog Name – redundant at present because all SSIS Catalogs are named “SSISDB.”
  • Folder Name – the name of the SSIS Catalog Folder that contains the scripted artifact.
  • Project Name – the name of the SSIS Project that contains the scripted artifact.
  • Project Connection Name – the name of the SSIS Project Connection.
  • Generated By – the name of the enterprise account used to generate the artifact’s script.
    • Note: SSIS Catalog Compare respects the security model of the SSIS Catalog. Windows Authentication is required to perform many SSIS Catalog operations.
  • Generated Date – the date and time the script was generated.
  • Generated From – the version of CatalogBase used in the generation of the artifact script.
    • Executing On – the name of the machine on which CatalogBase was running.
  • Deployed to Instance – the SQL Server instance hosting the target SSIS Catalog.
  • Deploy Date – the date and time the deployment script was executed.
  • Deploy By – the enterprise account used to deploy the artifact script.

Script Support Declarations

(click to enlarge)

The next section of the artifact script is the declaration of parameters used to support the remained of the script’s operations. An example is shown here:

Status and Conditions Checks

(click to enlarge)

The next section of the artifact script checks for the existence of required artifacts like Catalog Folders and Projects. An example of checks for the existence of a Catalog Folder and an SSIS Project.

An example of messages generated by this portion of the script are shown below:

If required preceding artifacts do not exist in the target SSIS Catalog, an error message is generated – similar to that seen here:

(click to enlarge)

Connections Properties Reset

 

(click to enlarge)

Unlike other scripts generated by SSIS Catalog Compare, Connections Literals scripts reset all related properties (parameters) for a connection manager that are not overridden via Reference Mapping. The portion of the script that manages clearing connection property parameter values is shown here. The results of the execution of this portion of the artifact script are shown below:

(click to enlarge)

Connections Properties Literal Override

(click to enlarge)

The final section of the Connection Property Literal script contains the literal override. This section of the script is laden with existence checks and conditionals.

The results of executing this section of the script are shown below:

(click to enlarge)

Conclusion

As you may glean from this analysis of one script generated for Project Connection Literals management, the Transact-SQL for scripting SSIS Catalog artifacts in SSIS Catalog Compare is rigorous, containing several existence and error-condition checks prior to performing any updates. The script is designed to be idempotent, as well, meaning the script will succeed and the results will be repeatable and predictable each time the script is executed – and that the script itself is re-executable.

Learn more about SSIS Catalog Compare!

Surface Connections in SSIS Catalog Compare and Catalog Browser

Changing the way Connections are surfaced and managed was a major focus of SSIS Catalog Compare version 3. The changes were first shared as part of SSIS Catalog Browser – a free utility from DILM Suite for viewing the contents of an SSIS Catalog – because building the surfacing mechanisms for the SSIS Catalog was the easiest part of the job (i.e., scripting is hard!). For this reason, SSIS Catalog Browser will almost always be ahead of SSIS Catalog Compare in surfacing functionality.

Connections Virtual Folders

SSIS Catalog Compare now surfaces Connections virtual folders at the Project and Package level:

Expanded, the Connection virtual folders respectively contain nodes that represent SSIS Project and Package connections:

Each connection node, in turn, surfaces Connection properties:

Different connection manager providers surface different property collections. But the SSIS Catalog treats connection manager properties exactly like parameters. Don’t believe me? Query the SSISDB.catalog.object_parameters view:

As shown in the screenshot above (click to enlarge), Project parameters shown at the top of the results with object_type 20 include Project-level parameters and project connection manager properties. The same can be seen for Package parameters and connection manager properties with object_type 30, found lower in the results. Connection manager properties are stored in the SSIS Catalog in the same location as parameters (the internal.object_parameters table).

Connection manager properties are identified with the prefix “CM.”

SSIS Catalog Compare surfaces connection manager properties apart from parameters to surface a more-accurate visualization of the SSIS Catalog.