Executing SSIS Package

Question

I have just started learning about SSIS package, so plz bear if answer of my query seems obvious to you :-)

I have an SSIS package that need to be installed in different environments as DEV/SYSTEST/DTE/LIVE. Please note that SQL Server connection string is different in each environment - so need different config files.

The way I am thinking to deploy this is

Create separate package configuration file for different environments
Create an environment variable say 'MyPackageConfigPath' to hold the path of config file.
Run the package using DtExec command with /ConfigFile switch

My questions:

Is this a good way of deploying SSIS package in different environments?
Any other better way of executing package in this scenario?

Thank you!

billinkc billinkc · Accepted Answer · 2012-07-11T20:02:51

Assuming 2005-2008 R2 or 2012 with Package Deployment Model, your options for configuration are

Parent Package
Registry value
Environment variable
Command line assignment
XML file
Database table

You can generally group them into single-value configuration (1-4) and multi-value configuration (5-6). There are pros and cons for all of them but I find I have the best experience with a database table as my primary configuration repository. All my values are stored in there for a given environment so each environment (dev, test, load, stage, prod) server would have a dedicated catalog with a table for configuration (as well as logging and other ETL specific things)

Table approach

If you don't like my table, you can create your own or let BIDS/SSDT do it.

CREATE TABLE dbo.SSISConfig
(
    id identity(1,1) NOT NULL PRIMARY KEY
,   ConfigurationFilter nvarchar(150) NOT NULL -- Package Name
,   ConfiguredValue nvarchar(255) NULL -- Value of setting, such as a connection string
,   PackagePath nvarchar(255) NOT NULL -- Target within SSIS package where value is written
,   ConfiguredValueType nvarchar(20) NOT NULL -- Data type such as String, Int32, or DateTime
)

And an example on the DEV server would look like

id  | ConfigurationFilter| ConfiguredValue                                                                         | PackagePath                                              | ConfiguredValueType
100 | Default.2005.Sales | Data Source=DEV;Initial Catalog=SALESDEVDB;Provider=SQLNCLI.1;Integrated Security=SSPI; | \Package.Connections[SLSDB].Properties[ConnectionString] | String

That same row in production would look like

id  | ConfigurationFilter| ConfiguredValue                                                                          | PackagePath                                              | ConfiguredValueType
123 | Default.2005.Sales | Data Source=PROD;Initial Catalog=SALESPRODB;Provider=SQLNCLI.1;Integrated Security=SSPI; | \Package.Connections[SLSDB].Properties[ConnectionString] | String

We have configuration deployment scripts that factor in the server being deployed to to handle the database and server name changes so there is only one script to maintain. I find XML files are harder to keep in sync for cases like that (crap, we forgot to update production's config file and we pulled in the wrong data).

The trick

The trick to making this work, as you have identified in your second bullet, is that you need some mechanism of telling SSIS to use a different set of configurations. If you do not have multi-instanced machines, then I find using an environment variable to be an excellent approach. Define it once per server that executes packages and you are done. If you work in a multi-instanced environment, then it becomes a trickier situation.

I though HLGEM had a rather clever approach in their answer of using different service accounts with local environment variables in multi-instanced environments. You can scroll down to see my approach on multi-instanced machines.

Assorted answers where I covered configuration

Executing SSIS Package

3 Answers

Table approach

The trick