An Attempt to Make SSIS Package Deployment More Reasonable

By nycdotnet, 2011/11/03

Full disclosure: There might be a better way to do this. I am not aware of it.

Problems:

Maintaining multiple DB environments (Local, Shared Dev, Staging, Prod) means needing to maintain multiple SSIS environments.
SSIS packages are tricky to configure using the existing toolset – it’s easy to accidentally use the wrong config file, or forget that you turned config files off, etc.
There’s insanity regarding different developers configuring different connection strings.
There’s no good out of the box way to say “deploy package X to environment Y”, etc.
There’s no good way to keep your DTSConfig files for different environments in your Source Control system.

Idea:

I want to write a script (or something) to take an SSIS package that I have built and:

Have a sane way to copy that SSIS package to the correct spot on a file system for the “environment” that I have selected (Local, Shared Dev, Staging, Prod).
Ensure that “Use SSIS Config File” is enabled on the package that is copied to that location regardless of if it is enabled on the package that I am editing in Visual Studio locally.
Ensure that an up-to date dtsConfig file is deployed along with the SSIS package (if desired) and also to ensure that the SSIS package is hacked to use a relative path to that dtsConfig file such as .\myconfig.dtsConfig (if desired, absolute paths also supported).
Allow updating the SSIS config file appropriately with connection strings appropriate for that environment.

Result:

I’ve now built such a thing. It’s a VBScript where the top of the script looks like this (skipping Option Explicit):

CONST DtsPackage = "MyPackageName.dtsx"

CONST DtsConfig = ".\MyPackageName.dtsConfig"

CONST DeployTo = "\\MyProdServer\e$\SomePathGoesHere\Packages"

CONST DeployConfig = True

Const MaxDTSXFileAgeInMinutes = 10

Dim ConnectionStrings

ConnectionStrings = Array( "OutputTextFile","E:\SomePathGoeshere\Output.txt", _

"DBConnection","Data Source=MyProdServer;Initial Catalog=MyDatabase;Integrated Security=True;")

That’s the production one. The Dev one looks like this (differences highlighted):

CONST DtsPackage = "MyPackageName.dtsx"

CONST DtsConfig = ".\MyPackageName.dtsConfig"

CONST DeployTo = "\\MyDevServer\D$\SomePathGoesHere\DevPackages"

CONST DeployConfig = True

Const MaxDTSXFileAgeInMinutes = 10

Dim ConnectionStrings

ConnectionStrings = Array( "OutputTextFile","D:\SomePathGoeshere\Dev\Output.txt", _

"DBConnection","Data Source=MyDevServer;Initial Catalog=MyDatabase;Integrated Security=True;")

I am able to check both of these VBScripts into source control. This means any of my developers can use them. The way it’s written, if DeployConfig is set to false, you don’t need to put the connection strings stuff in the script so the production info can stay secure (you’d just have to pre-stage the config file and not grant the devs access to the destination folder).

In the current VBScript incarnation, to run the deploy script, you just open the SSIS project folder, open a command prompt, type CSCRIPT<space>, drag and drop the script onto the command line and hit ENTER. After a few seconds, your SSIS package is deployed to the correct environment. DONE!!! Once the script for an environment is written, this process takes about 15 seconds. Writing the script for an environment takes 15 minutes or so just to confirm all the details, give it a quick test, and get the new script into Source Control.

Downsides:

The current version of this uses VBScript. I know, I know – 2003 called and it wants its script engine back. I wanted to write v1 in PowerShell but I wanted to get it done more than I wanted it to be cool. I promise that rev 2 will be in PowerShell. This will allow the deploy scripts to really be just the config data on top and a reference to a shared ps1 file for the actual code. The current incarnation unfortunately requires copying and pasting the main VBS body between files and that is obviously poor practice.

Next Steps:

I need some help with ideas for this and I need some help testing this out. If you are interested and you support multiple environments/multiple servers/multiple developers and have your SSIS packages in some sort of source control system, please send me a PM on the SQLServerCentral.com forums (my handle is nycdotnet).

http://www.sqlservercentral.com/Forums/UserInfo680048.aspx

Of course this could completely crash and burn your SSIS environments so please only reach out if you’re really OK with some buggy code or issues with testing. Thanks!