Expert SQL Server™ 2005 Integration Services

2.3. Making Connections Configurable and Dynamic

Connections are the most common object within a package that needs to be updated. This is because packages in development most often point to development databases. When you are ready to deploy your package to a test machine or a production machine, then you must have that connection updated. The wrong answer is to manually open the package and hard-code the new connection string because when the package is modified, you introduce the possibility of new bugs creeping in.

Another common example that requires connection strings to be dynamic is when file names change because of a change of date or because you are given a set of identical files that you need to loop over.

Within SSIS, there are many ways to accommodate dynamic connection strings. A developer can choose from one the following options:

• Package Configurations—The connection string is stored somewhere external to the package (such as a file or database table) by leveraging SSIS Package Configurations. The connection can then be changed without touching the package. With this approach, the connection is updated once at the very start of the package execution. Setting up configurations is relatively straightforward and the details can be found in Books Online, or in Chapter 16 of the Professional SQL Server 2005 Integration Services book.

• Property Expressions—The ConnectionString property of a package connection can be dynamically updated through the use of SSIS Property ...