SSIS Package Configuration using Table as Source

Question

SSIS Package Configuration using Table as Source

graham.paramore

Mr or Mrs. 500

Points: 560
More actions
July 28, 2011 at 8:23 pm

#242228

Firstly my apologies if this is the incorrect forum but would appreciate some advice on how to implement package configurations using a table. I am using SSIS in Visual Studio 2008 and SQL Server 2008.
We need to make dynamic connections for packages so we can easily migrate packages from one server to another. I have been following the article http://msdn.microsoft.com/en-us/library/ms140213.aspx but using a table rather than XML.
This is what I have done so far...
1) I have created a variable in my package called PackageVariable.
2) I have created a table SSIS Configurations with the PackagePath column having \Package.Variables[User::PackageVariable].Properties[Value] and the configuredvalue as TableValue so I can see if the package variable is used or the SSIS Configurations table.
3) In the package, I right clicked in the Control Flow and selected Package Configuration > Add then on the next screen selected a configuration type of SQL Server, added the connection to my db table and chose my configuration filter. All good so far.
4) After clicking next I get a warning message "Do you want to reuse this configuration or overwrite information in the table for this connection name with new configuration settings" with buttons Reuse Existing and Overwrite. I chose Reuse Existing as I had already created a row in the SSIS Configurations table.
When running the package, I expected to see the variable in the package overwritten by the value in the SSIS Configurations table, but to my surprise the opposite occured - the SSIS Configuration table was updated. Hardly makes sense to have a config table receiving the value rather than passing it.
Any ideas?
regards,
Graham

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ghanta Hall of Fame Points: 3999 More actions · Answer 1

The variable in the package will have the one you had hardcoded last time; however, the package will use the value from configuration table if it is implemented correctly.

graham.paramore Mr or Mrs. 500 Points: 560 More actions · Answer 2

Thank you Ghanta. So "implemented correctly" is the key - anything obvious I have missed?

Ghanta Hall of Fame Points: 3999 More actions · Answer 3

graham.paramore (7/28/2011)
Thank you Ghanta. So "implemented correctly" is the key - anything obvious I have missed?

Did you create the Environmental Variable?

graham.paramore Mr or Mrs. 500 Points: 560 More actions · Answer 4

Mmm that may be it. I created a variable in the package (right click in Control Flow then Variable) - Is Environmental Variable something different to that?