SQL Server Package Configuration

September 16, 2014 2:12 PM

How do I use a value from a Package Configuration in the Connection Manager? I am using an SQL Data Source, not an Environmental Variable.

I have set up the Package Configuration on an SSIS package to point to a table that holds some Connection Strings. How do I set up a source or destination in the Connection Manager that uses this Connection String? I am using Visual Studio 08.

I took over a project that will one day be moved from a development server to a production server. When that happens, we will want to update many of the connections to use a new location. A config table already exists and if I can get the connection strings passed to the packages, I can just update the location on the table instead of editing each package. What I've read about Package Configurations leads me to believe this is one way to do that, but I have not found any explanations on how. Many descriptions of how to set up and connect to the config table, but nothing on how to use the value you pull from the table to connect to a source.

I am not looking for other methods to do that right now. I want to understand how this method is supposed to work. Thanks

posted by soelo to Computers & Internet (3 answers total)

SSIS configurations are set on the assumption that you're using the same names for variables, connection managers, etc. All you should need to do is assign the config to the package itself at the package level, and it will farm out the values appropriately.
posted by tigerjade at 4:49 PM on September 16, 2014

Here's a decent walkthrough on package configuration from a config table. Feel free to ping me if you need more help.

http://mikedavissql.com/2011/10/04/using-configuration-tables-in-ssis-20082005/
posted by mrdaneri at 7:58 PM on September 16, 2014

Response by poster: That walk through is helpful in adding new lines to the config table or creating a brand new config table. But I already have a config table with my filters and connection strings set up. You have to reuse existing filters to handle those.

The issue I was having was "how to use the value you pull from the table to connect to a source". After brainstorming with some coworkers, we now understand that the OLEDB connection is updating itself based on the connection string. This is the part that I feel is missing from all of the tutorials I've been reading.

There is no indication in VS 2008 that the connection is based on the Package Configuration instead of hard-coded, is there? If the configurations exist, can I assume that the connections are using them?
posted by soelo at 8:27 AM on September 18, 2014