SQL Server 2005 Integration Service Package - Configuring Connections
I've been porting some DTS packages from SQL Server 2000 into the new Integration Services packages that are supported in SQL Server 2005. For those that are unaware, DTS is no longer a feature in SQL Server 2005 and has been replaced by the Integration Services (SSIS) architecture. You can still run DTS packages on a SQL Server 2005 server but it is not the focus of my blog entry. What I wanted to know the most when beginning the conversion process was how can you set the specific database connection information for a connection object in an SSIS package?
What I always run into is deployment of packages and setting the proper connection information for the environment. Previously with DTS packages you could simply save the package to your target SQL server (say you're deploying a package from development to a QA environment) and then edit the connection in the package on the QA server and save it again. Done, the connection is now pointing to the appropriate database and credentials for QA. With SSIS though your packages are compiled before you put them on the server. You cannot open a package on a server anymore and edit it and having to compile a different version for each server just because the connection needed to indicate a different server/credentials seemed crazy (and is). I couldn't find a straight forward answer though for SSIS in books online on how you could change connection info in a compiled package. Most of the things I found indicated using package configurations which allow you to store configuration values for an SSIS package in an XML file, registry or system variables. This seemed cumbersome to have to track another file to deploy for just wanting to change a db name or credential for a connection so I didn't do it. But what I eventually found out is how straightforward it is right within the Execute Package and Schedule Package screens in SQL 2005! Once you import your package into the Integration Service on your target server when you go to execute the package you can switch to the Connections tab and it lists out all the connections from within your package and allows you to check a box and override the connection string. Perfect! This is exactly what I wanted to do and I don't have to maintain any 'package configuration' files. If you run your package on a scheduled basis using the SQL Agent you also get this option when creating the job. After you add the package as a step be sure to go to the connection tab and override whatever connection strings you need too.
I was pretty surprised I couldn't find a simple reference to this in any of the books online material! So in case anyone else searches on this topic I hope they find the simple answer here and it saves them from hunting when researching an approach and from thinking they have to make package configurations for something pretty simple. Package configurations have their place in complex packages that use variables and other options, but they're overkill in my book if all you need to do is change connection information.