Deploying multiple SSIS Packages from Development to Live Environments

This is as much a reminder for myself as a reference for others ;)

There are several ways of deploying SSIS dtsx packages under SQL 2005, but I like this one the best. It allows almost "one touch" style deployment of an entire project of packages. It makes use of package configurations. You may find you need to make several changes to every package in your solution, but you'll only need to do it once.

Once it's done you can copy your packages from DEV to LIVE without having to worry about different locations and connection strings in your live environment

Setting up your DEV and LIVE environments

1. Create a new database on your database server, I've called mine SSISConfig. Don't add anything to it, BI Studio will do that for us.



2. Create a new environment variable on each machine. I've called mine DW_CONFIG.
   
   Start -> (right click) My Computer -> Properties -> Advanced -> Environment variables -> New
   
   give this variable a value that points to what will become your configuration database (where all other csv file locations, excel connection strings etc will be stored). For Example, my database is called SSISConfig:
   
   Provider=SQLNCLI.1;Data Source=dur-vmdevolap-01;Integrated Security=SSPI;Initial Catalog=SSISConfig
   
   Note that your changes won't take effect until you restart. You can see what is in effect by opening a cmd window and typing
   
   SET
   
   You can make temporary changes that will be immediately effective by using
   
   SET DW_Config=SQLNCLI.1;Data Source=dur-vmdevolap-01;Integrated Security=SSPI;Initial Catalog=SSISConfig

Setting up your SSIS Packages

1. Open your DTSX project using BI Studio / VS 2005
   Go to project Properties -> Deployment Utility -> Create deployment Utility
   
   Set the value to true



2. In the "Data Sources" folder of your project, create a datasource pointing at your SSISConfig database as it is found on DEV. It's also a good opportunity to create a Data Source object for each database system your packages might connect to.


3. Open each package in turn and:


4. Create a new connection under Connection Managers. Select the "New connection from data source" option, select your SSISConfig data source


5. Right click on the design surface and select "Package Configurations" (you may have to enable them first)


6. Add a new package configuration, select "Environment Variable", and provide the value "DW_Config"
   
7. Now add another package configuration, select "SQL Server", choose the connection "SSISConfig", press the "new" option beside Configuration table and use the defaults. For "Configuration Filter" use a name that uniquely identifies this configuration parameter (eq "DataWarehouse Connection String"). Press next, then select the property you wish add to the configuration (normally "Connection String")

When making these changes to further packages, be sure to reuse the same "Configuration Filter" value when you conceptually mean the same thing. It will ask if you wish to "reuse" or "overwrite". Choose reuse.

Once you are finished, inspect the results of your work by running

USE SSISConfig
SELECT * FROM SSIS Configurations


You should see your configuration entries. Backup and restore this database to your live server, then change the data in the "ConfiguredValue" column to represent the locations/connections that exist on the live system.

Deploying Changes to LIVE

Build your package, copy the resultant .bin/Deployment folder to your live SQL Server (with SSIS on). Double click the file with the ".SSISDeploymentManifest" extension. The SSIS Wizard will load the files and validate them for you. It will also warn you if it can't find the files/environment variables listed in package configuration. Once this is complete you can execute your new SSIS packages.