Automating SSIS Package Deployment

2 Flares Twitter 1 Facebook 0 StumbleUpon 0 Google+ 1 LinkedIn 0 2 Flares ×

I seem to end up dealing with automated deployment a lot in my work life – whether its part of a continuous deployment environment, or simply an automated deployment tool. This is probably because I’m very much a proponent of continuous deployment, and always push the issue wherever I’m based – often this means doing the work yourself.

Just this week I’ve had to automate the deployment of SSIS packages – to my surprise the sql server managed DTS library made this simple. For me, there are 2 key stages – firstly the packages needed to be versioned somehow and secondly the packges needed to be deployed.

As a preface, I am running TFS build, and have the packages in my working directory (I’m not going to get too much into the TFS build specifics). I am also referencing the Microsoft.SqlServer.ManagedDTS.dll (which I had to rip from the GAC)

Versioning the DTS Package

I created a simple code activity that found all my dtsx packages and added some version information. I decided to include the changeset number in the SSIS package version comments – it isn’t a full solution, but it did what I needed:

This procedure is pretty straight forward – I’m simply finding all dtsx files in my working directory, and adding the changeset as the version comment. If you want to modify the VerisonMajor and VersionMinor you can modify them in the same way.

Automating Deployment

The second stage is automating deployment of the dtsx – this is also pretty simple.

This method simply picks up the specified SSIS package and deploys it to MSDB on the specified sql server – again, its not rocket science, but it gets the job done.

, , ,

The Essential App Marketing Kit
Subscribe To My Newsletter To Get an Entire Chapter From The Book for FREE
Never display this again
2 Flares Twitter 1 Facebook 0 StumbleUpon 0 Google+ 1 LinkedIn 0 2 Flares ×