SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Setting All Connections at run time.


Setting All Connections at run time.

Author
Message
Marc M-454371
Marc M-454371
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 42
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
larskandersen
larskandersen
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 Visits: 1032
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".
Marc M-454371
Marc M-454371
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 42
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.
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27161 Visits: 13268
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Marc M-454371
Marc M-454371
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 42
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.
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27161 Visits: 13268
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search