DevOps is gaining momentum in many enterprises. You will get different answers when you ask people, “What is DevOps?” I do not like some answers to that question; in particular, the answers that treat people as interchangeable commodities. My preferred answer to the question is: “DevOps uses automation to improve efficiencies by reducing variance in software and server configurations management.”

There is considerable overlap between DevOps and Software Development Lifecycle Management (SDLC) and/or Application Lifecycle Management (ALM). Practicing a methodology such as Scrum is beneficial in ALM and supports the goals of DevOps, but automation is the key.

In this series of articles, I intend to discuss and demonstrate some ideas for migrating SSIS projects through an enterprise lifecycle. I refer to this discipline as “Data Integration Lifecycle Management” or DILM.

SSIS and Code Promotion

I want to limit the remainder of this article to a discussion about how one promotes SSIS code from one DILM tier to another in the modern enterprise. Enterprise lifecycles almost always include a Production tier and a Development tier, at least. Data integration developers build and test their code – SSIS packages in this case – in the Development tier. Later, the code is promoted to the Production tier. This is a software development best practice.

In this article, we will:

• Promote an SSIS Project from one DILM tier (SSIS Catalog) instance to another.
• Configure SSIS Project and Package Parameter values using a Catalog Environment and Catalog Parameter Value Overrides.
• Examine the process of migrating Catalog Environments, Project References, Reference Mappings, and Catalog Parameter Value Overrides from one DILM tier (SSIS Catalog) to another.

Code Promotion

Deployment to an SSIS Catalog or code promotion (or migration), is almost always accomplished by deploying SSIS projects via the Integration Services Deployment Wizard, as shown in Figure 1:

Figure 1: Integration Services Deployment Wizard

Figure 1 shows a deployment from an ISPAC file (the default). But note the option to select an Integration Services Catalog as the source, shown below. In this case, we are picking the Stage_Source_Data project from the Stage folder in our catalog, as shown in Figure 2:

Figure 2: Configuring an SSIS Project Deployment from an SSIS Catalog

One would next select or enter the target SSIS Catalog and browse to select the target SSIS Catalog Folder, as shown in Figure 3:

Figure 3: Configuring the Target SSIS Catalog

Completing the deployment may require the creation of an SSIS Catalog Folder on the target SSIS Catalog, as shown in Figure 4, since we did not have a folder named “Stage” on our destination server.

Figure 4: Creating an SSIS Catalog Folder in the Target SSIS Catalog

The completed configuration for the Target SSIS Catalog displays the SSIS Catalog Path for the SSIS Project in the format <Catalog>/<Folder>/<Project>, as shown in Figure 5:

Figure 5: Target SSIS Catalog Configuration

The next step is reviewing the intent to deploy, as shown in Figure 6:

Figure 6: Review the Configuration

Clicking the Deploy button starts the deployment process. When completed, a successful SSIS Catalog deployment appears similar to that shown in Figure 7:

Figure 7: Successful Deployment!

Note that we really accomplished two operations in the SSIS Catalog:

1. We created a Catalog folder on the target that matches the source (“Stage” in this case).
2. We deployed an SSIS project (“Stage_Source_Data” in this case) from one instant to another.

Is there more to migrating an SSIS Project to another SSIS Catalog? Yep. Let’s look at the remainder now.

Managing Parameter Values

SSIS Project and Package Parameters allow data integration developers to build more generic SSIS packages. The SSIS Catalog provides a way to externalize potential parameter values, storing them separately in Catalog Environments. Storing external values elsewhere reduces the number of packages required, reduces the amount of time developers spend developing single-purpose SSIS packages, improves solution flexibility, and promotes code reuse; thereby increasing value.

SSIS Catalog Environments

An SSIS Catalog Environment is a collection of configuration metadata – Catalog Environment Variables and values – stored so it may be referenced by SSIS packages at runtime. Environments are to the SSIS Catalog what Package Configurations were to pre-2012 SSIS. Compared to Package Configurations, Catalog Environments are much better but also more complex.  For a more detailed look at SSIS Catalog Environments, please read SSIS Catalog Environments– Step 20 of the Stairway to Integration Services.

Let’s look at an example to learn more.

Add an Environment

You can add an SSIS Catalog Environment by using SSMS to connect to a SQL Server Instance that hosts an SSIS Catalog. Open Object Explorer and expand the Integration Services Catalogs node. You can create a new Catalog Folder or expand an existing Catalog Folder, and then right-click the Environments virtual folder to create a new Catalog Environment, as shown in Figure 8:

Figure 8: Creating a New Catalog Environment

Add a name and optional description for the new Catalog Environment, as shown in Figure 9:

Figure 9: Configuring the New Catalog Environment

Click OK to complete the creation of the Catalog Environment. Creating the environment is a two-step process. The first step is called “Create Environment” and we’ve just completed it. The next step is configuring the environment and we cannot begin that step until we’ve completed the Creation step.

To configure the new Catalog Environment, right-click it and click Properties, as shown in Figure 10:

Figure 10: Edit a Catalog Environment

The Environment Properties dialog allows you to add Catalog Environment Variables to the Variables page. Here is where you can configure the data types and values. In this example we are creating two string variables that contain connection strings, shown in Figure 11:

Figure 11: Adding and Configuring Catalog Environment Variables

When the Catalog Environment Variable configuration is complete, click the OK button to close the Environment Properties window.

Reference the Environment

SSIS Projects must access Catalog Environment Variable values via References. To create a reference to a Catalog Environment, right-click the SSIS Project and click Configure, as shown in Figure 12:

Figure 12: Editing an SSIS Project Configuration

You have the option of referencing Catalog Environments in the same Catalog Folder (Stage) that hosts the SSIS Project, or you can reference Catalog Environments in another Catalog Folder. In this case, we reference the Catalog Environment (testStage) in the same Catalog Folder (Stage) in Figure 13:

Figure 13: Selecting a Catalog Environment to Reference

You can create many references to different Catalog Environments. Once referenced by an SSIS Project, a Catalog Environment is listed in the References grid, as shown in Figure 14:

Figure 14: A Project Reference to a Catalog Environment

To map Catalog Environment Variables to SSIS Project and Package Parameters, return to the Parameters page and click the ellipsis to the right of a value for which you wish to create a reference mapping, as shown in Figure 15. In this case, we will map the project ConnectionString parameter:

Figure 15: SSIS Project Connection Managers with Design-time Defaults

In the Set Parameter Value window, select the “Use environment variable” option and select the Catalog Environment Variable from the dropdown, as shown in Figure 16:

Figure 16: Mapping a Catalog Environment Variable to the ConnectionString Property

Click the OK button when completed.

Note the text and text decoration of the Value once the reference mapping is complete – the text displays the name of the Catalog Environment Variable and is underlined, in this case MedicalStageConnectionString, as shown in Figure 17:

Figure 17: A Configured Reference Mapping to a Catalog Environment Variable

Figure 18 shows the completed reference mapping for the other Catalog Environment Variable (MedicalSourceConnectionString) we created earlier:

Figure 18: Another Configured Reference Mapping to a Catalog Environment Variable

Catalog Parameter Value Overrides

There are three sources for parameter values in the SSIS Catalog:

1. Design-time defaults – values to which your SSIS Project and Package Parameters were set when deployed to the SSIS Catalog.
2. Catalog Environment Variables – values of Catalog Environment Variables stored as a collection inside Catalog Environments and referenced by SSIS Projects in the Catalog.
3. Catalog Parameter Value Overrides – values specified in the SSIS Project Configuration after the project is deployed to the SSIS Catalog.

You can learn more about SSIS Project Parameter management in the blog post titled Managing SSIS Catalog Project Parameter Values.

Figure 19 shows a Catalog Parameter Value Override configuration. To create an override, click the ellipsis to the right of a value for which you wish to create an override, select the “Edit value” option, and enter the desired value in the associated textbox:

Figure 19: Configuring a Parameter Value Override

Once configured, overridden values are displayed in bold text, as shown in Figure 20:

Figure 20: A Configured Parameter Value Override

Note that we can use the text decorations to easily tell when a catalog variable is mapped, or a value is overridden. Underlining indicates catalog variables, while bold highlights a value override. Plain text denotes the original package value.

Promoting the Configuration Metadata

If you’ve been working through your own example as you read this article, you may have noticed the Script buttons near the top of almost every page and window. You may assume, as I and many others have assumed, that producing T-SQL scripts to port these settings from one DILM tier (SSIS Catalog) to another is merely a matter of scripting the configurations.

Like myself and many others, you would be wrong.

The Script Button Doesn’t Work

Open a Catalog Environment’s properties window and click the Script button, as shown in Figure 21:

Figure 21: Scripting a Catalog Environment

The result is an empty query window.

Open an SSIS Project configuration window and click the Script button, as shown in Figure 22:

Figure 22: Scripting an SSIS Project Configuration

The result is… nothing. No query window displays. The Reference, Reference Mappings, and Catalog Parameter Value Overrides are not scripted by SSMS.

It is possible to script the Environment, Environment Variables, Environment Variable Values, References, Reference Mappings, and Catalog Parameter Overrides. You have to remember to click the Script buttons prior to clicking the OK buttons when you are creating and configuring these objects. Once you click the OK button, your opportunity to generate T-SQL scripts for these objects by clicking the Script button has passed.

If you make edits to references, mappings, and/or overrides, clicking the Script button will result in a script that edits or adds the references, mappings, and/or overrides.

The Solution?

I am not the first person to notice the scripting problems. Others have created T-SQL scripts that will extract the configuration metadata from an SSIS Catalog. I won’t list them here, but you can find them using your favorite search engine.

At the time of this writing, some of your options are:

• Use one of the aforementioned scripts to extract the configuration metadata.
• Create your own scripts to extract the configuration metadata.
• Manually copy, paste, and reconfigure configuration metadata in the target DILM tier.
• Use SSIS Catalog Compare. (Full disclosure: I wrote SSIS Catalog Compare. At the time of this writing, the product is in beta, and Kevin Hazzard and I continue development efforts.)

Conclusion

In this article, I demonstrated how to deploy an SSIS package from an existing SSIS Catalog or ISPAC file to a target SSIS Catalog. I showed you how to configure SSIS Catalog Environments to manage SSIS parameter values. We examined some “gotchas” and noted some remedies, one of which is the SSIS Catalog Compare product.

In Part 2, I will show you how to use SSIS Catalog Compare to migrate SSIS projects and their configuration metadata from one DILM tier to another. If you cannot wait until the next article, you can learn more by watching the “3-Minute Drill” video here.