September 24, 2013 at 10:19 am
In my SSIS Packages, I have my Package Configurations set up to first, use an XML Configuration File to set the configuration settings of my "CONFIG" connection. From there, my various other connections receive their values from a SQL Server Configuration Type via a Configuration Table that is located on each of my environments.
I am a bit hesitant to store a value for the Server in each connection even though it will get re-written on runtime from the configurations. I am hesitant because if for some odd reason, the XML Configuration File is missing, or the package is pointing to the wrong path, it will default what ever server has been hardcoded into the value of the connection.
Ideally, I would like to put a value like 'XXXXXXX' for the Server in all connections. In the case that an XML file could not be found, there would be no chance of the package inadvertantly running on the wrong server.
Is there an easy way to loop through all connections and set the values of the server to 'XXXXXX' prior to the Configuration component running? Perhaps a script task in an event handler?
Thank you,
Marc
September 24, 2013 at 12:29 pm
Instead of setting the connection server to the package configuration value, set it to a variable under Properties/Expression/Server name for the connection.
Use the package configuration to set the variable value.
Set the default variable value to "XXXXX".
September 25, 2013 at 7:48 am
Grasshopper. Thanks for your post!
I agree with your approach but I am starting to get a bit confused as to what my best set up should be.
My LIVE environment consists of 2 servers (A_Live and B_Live .... A_QA and B_QA).
Prior to your post, I have the XML file on each server pointing to a CONFIG table on the appropriate environment. This table will then set the connections based on their names and the Configuration_Filter field.
Are you suggesting that I do not use a SQL Server Configuration Table and strictly rely on Variables (varServerA and varServerB) to set the connections appropriately?
Sounds simple. I would just need an XML file on each server with the two variables with the appropriate values.
September 25, 2013 at 7:52 am
Marc M-454371 (9/25/2013)
Grasshopper. Thanks for your post!I agree with your approach but I am starting to get a bit confused as to what my best set up should be.
My LIVE environment consists of 2 servers (A_Live and B_Live .... A_QA and B_QA).
Prior to your post, I have the XML file on each server pointing to a CONFIG table on the appropriate environment. This table will then set the connections based on their names and the Configuration_Filter field.
Are you suggesting that I do not use a SQL Server Configuration Table and strictly rely on Variables (varServerA and varServerB) to set the connections appropriately?
Sounds simple. I would just need an XML file on each server with the two variables with the appropriate values.
You can still use the SQL Serve config table (indirect configuration), but instead of directly configuring the connection managers, you configure variables instead.
These variables are in turn used in expressions on the connection managers.
But, as you said, you can do this directly in the XML config file (direct configuration)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 25, 2013 at 8:16 am
Gotcha. Makes Sense. Thanks for the clarification.
Does it make sense that I would want to "XXXXXX" out the value stored in the packages (either in variable or coded in the connection) when being saved to either LIVE or QA? My concern is having a package saved in QA pointing to LIVE.
I have noticed "XXXXX"ing out the Server Variable that talks to the Connection slows things down when opening in the VS Designer.
I guess its a Pros vs Cons scenario.
September 25, 2013 at 12:25 pm
Marc M-454371 (9/25/2013)
Gotcha. Makes Sense. Thanks for the clarification.Does it make sense that I would want to "XXXXXX" out the value stored in the packages (either in variable or coded in the connection) when being saved to either LIVE or QA? My concern is having a package saved in QA pointing to LIVE.
I have noticed "XXXXX"ing out the Server Variable that talks to the Connection slows things down when opening in the VS Designer.
I guess its a Pros vs Cons scenario.
It makes sense to have "XXXXXX" if the configuration is missing, to make sure you don't mess up another environment.
However you should have a configuration for the development environment as well, otherwise you get the time-out delays you experience when opening the package.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply