July 28, 2011 at 8:23 pm
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
July 28, 2011 at 8:31 pm
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.
July 28, 2011 at 8:34 pm
Thank you Ghanta. So "implemented correctly" is the key - anything obvious I have missed?
July 28, 2011 at 8:40 pm
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?
July 28, 2011 at 8:47 pm
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?
July 28, 2011 at 8:57 pm
http://www.mssqltips.com/tip.asp?tip=1405
Check this...
July 28, 2011 at 8:57 pm
Thank you for your patience. I have found this article http://www.mssqltips.com/tip.asp?tip=1405 which will hopefully resolve this for me.
regards,
Graham
July 28, 2011 at 8:58 pm
Snap! 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply