How to Promote SSIS Code in the Enterprise Lifecycle

I’ve written about Data Integration Lifecycle Management (DILM) tiers. To summarize, every enterprise needs at least four lifecycle management tiers:

1. Development – an environment in which developers have sysadmin privileges.
2. Test – another environment in which developers have sysadmin privileges.
4. Production – managed by production operations such as domain administrators, system administrators, and DBAs. Developers have limited and read-only permissions (at most).
3. Pre-Production (aka QA, UAT, etc.) – Identical to Production.

Developers need to able to develop software that will execute enterprise operations.
Production is solely managed by operations personnel. Allow very little, if any, developer access to Production.
Before deploying to Production, operations personnel need a Pre-Production environment they can use to test the deployment and performance after the deployment. No one wants operations personnel – or anyone, really – deploying a process to Production without a practice run.
Similarly, developers need to move their code from the Development tier (aka the “works on my machine” tier) to another tier – such as Test – so they can identify hard-coded defaults that should be parameters.

Move It!

A key component in software lifecycle management is the ability to promote code, or move software from one tier to the next. Enterprise developers need tools and the capability to promote code from Development to Test. Operations personnel need tools and the capability to promote code from Pre-Production to Production.

With each move, more missing parameters are identified. The Pareto principle applies: Each move reveals 80% of missing parameters, so:

• Development > Test identifies 80% of the missing parameters, leaving 20% of the original missing parameters.
• Test > Pre-Production identifies 80% of the remaining missing parameters, leaving 4% of the original missing parameters.
• Pre-Production > Production identifies 80% of the remaining missing parameters, leaving 0.8% of the original missing parameters.

There’s simply no substitute for promoting code.

Promoting SSIS

Promoting SSIS – especially SSIS stored in the SSIS Catalog – is not trivial. Promoting SSIS from one SSIS Catalog to another involves more than just deploying the SSIS project to the target host. The SSIS Catalog includes:

• Environments and Environment Variables
• Configuration Literals
• References and Reference Mappings

Scripting Environments and Environment Variables

To script an SSIS Catalog Environment (which includes a collection of Environment Variables), open SSMS’s Object Explorer and expand the Integration Services Catalogs node. Navigate the treeview to reach the Environment to script. To begin the process of scripting an SSIS Catalog Environment, right-click the environment and click Properties:

When the Environment Properties dialog displays. click the Script button:

An empty SSIS Catalog environment script is returned and displayed:

There is a workaround. As long as you click the Script button after configuring all environment variables but before clicking the OK button – or if you alter environment variables while the Environment Properties dialog displays and click the Script button before clicking the OK button – a script is generated for the environment variables added or altered:

Clicking the Script button after adding a new environment produces a script (as long as you remember to not click the OK button first):

Scripting Configuration Literals, References, and Reference Mappings

To view an SSIS Catalog project’s configuration literals and reference mappings, right-click an SSIS project node in the Integration Services Catalogs node of SSMS’s Object Explorer and then click Configure:

The Parameters page displays when the Configure dialog opens. There are three sources of SSIS parameter values in the SSIS Catalog:

1. Design-time defaults – denoted by no text decoration.
2. Configuration literals – denoted by bold text decoration.
3. Reference mappings – denoted by underlined text decoration.

Clicking the Script button after the Project Parameters are configured results in a progress bar appearing momentarily in the upper right of the Configure dialog, but no script is displayed.

As with the workaround for scripting Environments and Environment Variables, any changes – new or altered values – result in a script of the new and altered artifacts (as long as you remember to not click the OK button first):

Click the References page to view references:

Each reference defines a relationship between the SSIS project and one SSIS Catalog Environment. You may configure many references, but only one reference may be selected at execution time.

As with parameters, clicking the Script button after the Project References are configured results in a progress bar appearing momentarily in the upper right of the Configure dialog, but no script is displayed.

As with the workaround for scripting References, any changes – new or altered references – result in a script of the new and altered references (as long as you remember to not click the OK button first):

To promote SSIS to another DILM tier, we need a way to script the configuration literals, references, and reference mappings.

How Should We Then Promote SSIS?

Several enterprise data integration developers have worked around these scenarios. If you search, you will find many clever solutions that employ PowerShell and T-SQL that generates T-SQL, to name a couple workarounds.

SSIS Catalog Compare

At Enterprise Data & Analytics, we built SSIS Catalog Compare to address these scenarios.

Scripting in SSIS Catalog Compare Enterprise Edition is fast and painless. Simply connect to an SSIS Catalog, right-click the SSIS Catalog Folder you containing the SSIS project(s) and configurations data you desire to promote, and then click Generate Scripts for Folder and Contents:

Select a target folder for the script using the Browse for Folder dialog:

Click the OK button to generate T-SQL scripts and ISPAC files:

Please note SSIS Catalog Compare Standard Edition and Enterprise Edition generate T-SQL script files and ISPAC files, as shown above. The artifacts are numbered to indicate SSIS Catalog object dependencies.

Let’s examine the contents of an Environment script:

(click to enlarge)

All T-SQL scripts generated by SSIS Catalog Compare are idempotent, which means the scripts may be re-executed and produce the same results. The results messages are designed to be copied from the SSMS Messages tab and pasted into a ticket’s Notes field:

But Wait, There’s More!

SSIS Catalog Compare Enterprise Edition takes an additional step. Enterprise data integration developers specify a location for SCCPAC files in the options dialog:

Navigate to this folder using Windows Explorer:

Double-click the SCCPAC file to open the SCCPAC Deployment Wizard:

Enter the name of the SQL Server instance that hosts the target SSIS Catalog (Please note: the target SSIS Catalog may be used by an Azure-SSIS integration runtime, and the Azure-SSIS IR must be running to permit ISPAC deployment), and then click the Execute button.

The SSIS Catalog Folder and all SSIS projects, packages, configuration literals, environments, environment variables, references, and reference mappings are deployed to the target SSIS Catalog:

Did the automated SCCPAC deployment process miss something? No, the SCCPAC Deployment Wizard is automated and never misses an artifact. You may compare the source and target SSIS Catalogs in SSIS Catalog Compare to double-check, however.

Source Control Your SSIS Catalog

Wouldn’t it be awesome to source-control SSIS Catalog folders, projects, packages, configuration literals, references, and reference mappings? SSIS Catalog Compare Enterprise Edition can help.

In Azure DevOps, Software Development, and Git Part 4 – Command-Line with Azure DevOps, I blogged about using PowerShell to manage SCCPAC files in git. Instead of using SSIS Catalog Compare to generate scripts for one SSIS Catalog folder, simply generate scripts for the entire SSIS Catalog by right-clicking the SSISDB node ant then clicking Generate All Catalog Scripts:

After selecting a target folder, a file system folder – filled with T-SQL scripts and ISPAC files – is created for each SSIS Catalog folder:

Again, one method for managing these files in git is outlined in my post titled Azure DevOps, Software Development, and Git Part 4 – Command-Line with Azure DevOps.

Confidence

SSIS Catalog Compare enables enterprise data integration DevOps with confidence.

SSIS Catalog Compare Enterprise Edition enables enterprise data integration DevOps and supports infrastructure as a service for SSIS Catalog folders, projects, packages, configuration literals, references, and reference mappings.

Trial Edition?

SSIS Catalog Browser presents a unified surface for the SSIS Catalog, whether on-premises or in the cloud. Catalog Browser is read-only, it cannot be used to generate scripts.

Interested in Learning More?

Reach out to Enterprise Data & Analytics today to learn how you can save money and boost confidence in your enterprise data integration lifecycle management.