Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Setting All Connections at run time. Expand / Collapse
Author
Message
Posted Tuesday, September 24, 2013 10:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 8:46 AM
Points: 4, Visits: 32
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
Post #1497963
Posted Tuesday, September 24, 2013 12:29 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 2:28 PM
Points: 64, Visits: 672
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".

Post #1498009
Posted Wednesday, September 25, 2013 7:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 8:46 AM
Points: 4, Visits: 32
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.
Post #1498364
Posted Wednesday, September 25, 2013 7:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:26 AM
Points: 13,622, Visits: 10,514
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)




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1498368
Posted Wednesday, September 25, 2013 8:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 8:46 AM
Points: 4, Visits: 32
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.

Post #1498390
Posted Wednesday, September 25, 2013 12:25 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:26 AM
Points: 13,622, Visits: 10,514
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1498535
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse