fbpx

SSIS 2008 Package Configuration Issue

Posted on

In SSIS 2005 a single config source (XML or SQL Server) with many connection strings could be used to setup the connections for multiple packages even if those packages did not contain all of the connections specified in the config source. In this scenario the native package configurations would read the source and attempt to apply connection strings for all those specified in the source. In cases where the connection did not exist in the SSIS package a warning would be reported however, these warnings could be ignored by setting the ‘SupressConfigurationWarnings' property to ‘True' within the package. This configuration strategy worked well for me in past experience since I would only have to manage the connection strings for almost all packages within a single config location. This method however, does not work in SSIS 2008 🙁

Below is the solution I decided on however I am not totally satisfied with it. If you are also running into this issue and have something to share please comment below. Thanks!

Variable Approach
1. Inside the package there is a variable for every connection manager that will contain it’s connection string
2. The value for these variables will be derived from a package configuration pulling from a SQL Server source
3. Connection managers all are then set by an expression assigning the value of the variable to their connection string property

Benefits
1. A warning (versus error) is thrown when there is a variable in the database not found in the package that can be ignored by updating the ‘SupressConfigurationWarnings' property to true. This is also set by another configuration.
2. A single place to manage all connection strings for all SSIS packages

Cons
1. Extra steps are involved when adding new connection managers to a package

Below are the results from my tests using the same strategy in SSIS 2005 and SSIS 2008.

2005 Output

Warning: 0x8001F02F at Package: Cannot resolve a package path to an object in the package “.Connections[Conn2].ConnectionString”. Verify that the package path is valid.
SSIS package “Package.dtsx” starting.
SSIS package “Package.dtsx” finished: Success.

2008 Output

Error at Package: The connection “Conn2″ is not found. This error is thrown by the Connections collection when the specific connection element is not found.”

, , , ,

6 Comments

  1. I ran into this error as well when moving to 2008 ssis. My solution was to create a seperate config file for each connection string my packages might need. I only add the config files I need to each individual package. The pros are that it was simple to setup and I can still easily update one file when we have a server move. The con is that I have one file for every connection instead of just one file containing all my connections.

  2. Hmmm…interesting, that seems like it would be a lot to manage. Have you considered storing them all in one SQL table and using separate config filters for each connection?

    My goal with this was to create a package template that would automatically apply all the appropriate connections w/o any additional work by the developer other than following the naming convention for their connection manager…

  3. It’s not really much to manage since we only have 6 or 7 databases that we connect to. So that’s one file for each connection that is stored in the same directory on each of our servers (dev/test/prod). My goal was to make it as easy as possible to move a large number of packages from dev to test to prod. With this set up there are no changes that need to be made to the packages when they are moved to different servers. I can simply copy and paste them.

    I didn’t think too much about storing them in a SQL table. I guess I could have just have one configuration file with the connection to that environment’s configuration server/table. Then in that table I could store all the other connections in the configuration table….so many possibilities.

  4. One other way to solve this issue is to not collect all of your connections’ strings into a single configuration file. Several of the people most familiar with configuring and deploying SSIS packages recommend that you store ONE connection manager’s connection string per configuration. That way, you can pick and choose which configurations you need for a particular package – as it’s a one-to-one relationship between configuration files and connection managers. This also means that if you need to change a connection, you (still) only have one place to change it – and you no longer receive any warnings OR errors.

Comments are closed.