Have you ever tried to move some (or all) of the contents of one SSIS Catalog to another? Deploying an SSIS Project to a different SSIS Catalog is straightforward: Execute the ISPAC file, configure the new SSIS Catalog as the target, click a few times, and Boom; done.
What about the rest of it? I can hear you thinking:
“What Rest Of It, Andy?”
Some background may help. The SSIS Catalog has many awesome features. One less-publicized (in my opinion) feature is Catalog Environments. If you’ve used Package Configurations in the past, you may have encountered some unexpected behavior. Catalog Environments is an elegant – albeit complex – solution that overcomes many of the “quirks” of Package Configurations. I explain some features of Catalog Environments in SSIS Catalog Environments– Step 20 of the Stairway to Integration Services as part of the SSIS Catalog Environments– Step 20 of the Stairway to Integration Services series at SQL Server Central.
The “rest of it” to which I refer is the project and package configuration metadata stored in the SSIS Catalog.
I began building data integration frameworks (years ago) to support the common enterprise need to manage connections. My first Framework was designed in Data Transformation Services (DTS). It used INI files and the Set Properties functionality in DTS to facilitate DTS packages “finding” their connections at run time. I built a similar solution for SSIS 2005. Automating connections management is tedious but worth it: done correctly, it strongly mitigates the possibility of executing Development and Test executions and having them impact Production. (Running DTS in Development and loading data into Production is what inspired me to build the first solution with DTS.)
Reference Mappings 101
In the image above we see one basic example of SSIS configurations metadata in the SSIS Catalog (click the image for a larger view). The circled items make up the constituents of a Reference Mapping that configures the ConnectionString property of a package (Stage_Member.dtsx) Flat File Connection Manager named “FFCM_Member_Source” to one of two potential values stored in a Catalog Environment Variable named “MemberFilePath”. The two different values are stored in separate Catalog Environments named “Env1” and “MedicalData”. A “link” is configured between each Catalog Environment and the Stage_Member.dtsx package; this “link” is called a Reference.
A Reference Mapping establishes a “link” from a Catalog Environment Variable, through a Catalog Environment, via a Reference to a project or package, to a project or package parameter. Only one reference can be selected at run time, so the value of the project or package parameter is sourced from the value of the Catalog Environment Variable in the Catalog Environment that is “linked” to the Reference selected at run time.
As I mentioned in passing above, complex. But also elegant.
A Catalog Environment can hold dozens of Variables and Values. All that is required to completely reconfigure the execution of an SSIS Package is to select a different reference.
“How Do You Move an SSIS Catalog Environment and Its Variables, Andy?”
Using out of the box functionality, there’s no good answer to this question. If I open a Catalog Environment in the Integration Services Catalogs node of SQL Server Management Studio’s Object Explorer, there’s a button labeled “Script”:
Once Catalog Environment configuration is complete, clicking this button opens an empty SSMS query window.
To complete the Reference Mapping, one needs to create the Reference between the Project or Package and the Catalog Environment, and then map the parameter to the desired Catalog Environment Variable. Using the Integration Services Catalogs node of SQL Server Management Studio’s Object Explorer, one can right-click the Project or Package and click “Configure” to open a Configure dialog. This dialog also contains a “Script” button. Clicking this button after configuration changes have been stored does nothing:
I am not the first data integration developer to notice these issues. Several data integration developers have produced scripts that query the SSISDB database (the SSIS Catalog database) to surface this information.
SSIS Catalog Compare: Catalog Environments, Variables, References, and Reference Mappings
SSIS Catalog Compare provides two solutions: scripting and deployment. As shown below, a Catalog Environment (or Reference) can be scripted:
If a data integration developer of DevOps configuration manager scripts the Reference…
… scripts are automatically generated for dependent objects (the dependencies for a Reference are the Folder and the Catalog Environment):
Deployment is even easier. Connect to two SSIS Catalog Instances:
Right-click the artifact you wish to deploy and click “Deploy <artifact>”:
Deploying References and Reference Mappings requires selection of a target Project and a target Catalog Environment:
That’s it. You’re done.
The Beta Continues… But Not for Long!
At the time of this writing (5 Aug 2016) SSIS Catalog Compare is in beta. If you purchase SSIS Catalog Compare now, you pay only $95 USD for a non-expiring license that includes lifetime maintenance upgrades.
This offer will expire soon!
SSIS Catalog Compare v1.0 will be released soon and this offer will expire. The license and yearly maintenance prices will both increase when SSIS Catalog Compare v1.0 is released.
Act now! Contact me for information on bulk pricing.
:{>
Comments