SSIS Deployment Strategies

I have been looking at several deployment strategies for SSIS and wondering which one would be easiest to maintain. I have been leaning toward using SQL Se开发者_StackOverflowrver Configurations to house the connection strings and then run a proc after I have imported a package from the development server to change the connection strings. However, I have 75 packages and this seems somewhat tedious. Can anyone suggest a good deployment strategy?

I have a Dev, Stage, and several live servers to deploy to.

We are using SQL configurations for most of the packages built by my team. What we did to resolve the migration issue was to add a second configuration based on an environment variable which tells the package which configurations database to use. This may not be a good option for everyone but it works well for our setup.

Details:

• Our packages are always run from agent jobs.
• Each of our environments is on a seperate machine (we don't use named instances except for sandboxing.)
• We have a copy of our configurations DB in the default SQL instance of each machine and it uses the same db name and schema in each environment.
• The package looks at the machine name environment variable to tell which machine is executing the package.
• The package then looks for the configurations database on the executing machine to get the connection strings for the actual work to be done.

When we build a new package we have to migrate the SQL configurations to each environment and adjust them as necessary. But from that point on we only need to worry about them if we make a change to which connections the package uses or which server is executing it.

Doing it this way the package always knows which server is executing it and always uses the configurations associated with that server. So the ongoing maintenance and deployment of the individual packages is usually straight forward. All we generally have to worry about moving is the package itself and any underlying schema changes associated with the update.

I've generally found it easier to have a Package Configuration in an XML file, which contains all the required database connections. This gets amended as required when the packages are deployed to each environment (this can happen as part of the Deployment Manifest install). Each of your 75 packages can share the same configuration file which makes the management of it very straightforward.

We have an environment variable on each machine that points to a directory. In that directory we have one SSIS config file. The config file has one entry - it configures the connection string property of a connection manager found in all of our packages - called SSIS_CONFIG. This connection string points to the database that has our config table for that environment.

The config table has configurations for connection managers, as well as various other configurations. The ConfigurationFilter for the connection manager rows are set to the database name and the ConfiguredValue has the connection string for that database.

Each package has the SSIS_CONFIG connection manager. All other connection managers are named the name of the database to which they connect (instead of the SSIS default naming of the server and database).

The SSIS_CONFIG connection manager is configured by package configurations with a configuration type of indirect XML where the configuration location is stored in an environment variable. Every other connection manager uses configuration type SQL Server, the connection SSIS_CONFIG, and the configuration filter of the name of the database to which they connect.

If a new package needs to connect to a database, the odds are that another package has had to also, so the configuration needed for that Connection Manager is already in the config table, so we reuse that value when we build that package's package configurations.

Each environment has the environment variable and its own version of the database and config table. The only difference in the config table between environments is the connection strings in the ConfiguredValue column. For example, the connection strings in the DEV environment point to the DEV versions of the databases, and the QA environment entries point to the QA versions of the databases.

Editing packages as you promote them between environments invalidates testing. This approach allows us to promote packages without touching them. The design is also extrememly flexible which makes development and testing much easier.

We are able to use this approach with multiple instances running on the same machine using this as a guideline: http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/69739/