For a limited time, I am making DtsConfig Management – a feature of SSIS Catalog Compare – available in the latest (free) version of SSIS Catalog Browser. Am I awesome? Well, no. I am not awesome. This feature could use more testing. I can hear some of you thinking,…
“Why Do I Need to Manage DtsConfig Files, Andy?”
I’m glad you asked! The answer is: What’s old is new again. At least when it comes to SSIS execution. You see, back in the old days (when we used to carve our SSIS packages out of wood), we used DtsConfig files to externalize (or parameterize) SSIS package property and variable values.
A student of my Master the SSIS Catalog </ShamelessPlug> class recently asked if one could use SSIS Package Configurations with packages deployed to the SSIS Catalog. The answer is, “YesAsterisk” (not a typo). The * is: It’s not generally a good idea to mix and match SSIS Deployment Models. There are two SSIS deployment models: Package Deployment Model and Project Deployment Model. Using a Package Deployment Model feature – like Package Configurations – is supported; meaning “it will work” even in a Project Deployment Model SSIS package. But that doesn’t mean you should do it. There are caveats, and the top caveat is: Support personnel – including Future You – may be confused by this configuration.
I recommend using SSIS Catalog Environments and References to externalize / parameterize packages deployed to the SSIS Catalog, and SSIS Package Configurations (of which dtsConfig files are the most popular) for packages executed in Package Deployment Model (of which the file system is most popular). Learn more at:
– Deployment and Execution – Level 18 of the Stairway to Integration Services
– A Custom Execution Method – Level 19 of the Stairway to Integration Services
– SSIS Catalog Environments– Step 20 of the Stairway to Integration Services
What’s Changed?
You may now execute SSIS packages from Azure File Shares using the Azure Data Factory Azure-SSIS Integration Runtime. To learn more, check out How To: Execute Azure-SSIS Packages From Azure Files.
There’s a catch (isn’t there always a catch?): If you opt to use a dtsConfig file in your Azure-SSIS IR, there can be only one (it’s like Highlander that way…) as shown in the screenshot below:
If your SSIS package runs from the file system and references more than a single dtsConfig file, and you copy your SSIS package into an Azure File Share, you need to combine the dtsConfig files referenced by your SSIS package and then store the combined-version of your dtsConfig file in the same Azure File Share. </CertifiedRunOnSentence>
To make the Combine dtsConfig files functionality work in SSIS Catalog Browser, select – or multi-select – the dtsConfig files you wish to combine:
Next, pick a spot and name for the combined dtsConfig file:
Just like that, a brand new dtsConfig file is created containing the configurations stored in the selected dtsConfig files:
And… I prettified it just for you:
I can hear some of you thinking, …
“Fine. I Will Just Move to the SSIS Catalog.”
Hold on there, Sparky. You can reference as many SSIS Catalog Environments as you want for your SSIS project in the Catalog, but you must select one (1) to assign at package-execution time.
“What?!”
Yep. Same issue. Different mechanism.
Fear not. I’ve built a different solution for this issue:
Script Environment
Similar to the Combine functionality, except you have to let the Environment know where it will live:
Also similar to combining dtsConfig files, you must select the dtsConfig files to combine into an SSIS Catalog Environment script:
Finally, enter a location for your SSIS Catalog Environment script:
A script similar to the one shown is generated:
An SSIS Catalog Environment is created:
And the SSIS Catalog Environment contains Environment Variables gleaned from the dtsConfig files:
Conclusion
Again, this functionality will not always be available in SSIS Catalog Browser. It is for now, though.
Enjoy!
:{>
Comments