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


Dynamic connections in SSIS


Dynamic connections in SSIS

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: Administrators
Points: 63552 Visits: 19115
Sorry, bad question. Everyone gets points.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Tom Garth
Tom Garth
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1255 Visits: 1499
Ah...then that splains..

Correct answers: 94% (803)
Incorrect answers: 6% (52)
Total attempts: 855


--------------------------------------------------------------------------------

No - In SSIS connections can not be dynamically updated. 6%

--------------------------------------------------------------------------------

No - In SSIS connections can only be updated at run time using package configurations. 10%

--------------------------------------------------------------------------------

Yes - In SSIS connections can be dynamically updated during execution using package configurations. 46%

--------------------------------------------------------------------------------

Yes - In SSIS connections can be dynamically updated during execution using the DTS.Connections collection. 38%

Total scores and standings


Thanks Steve!

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

Lonely Rogue
Lonely Rogue
Old Hand
Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)

Group: General Forum Members
Points: 379 Visits: 436
Hi all,

Can some one give me solution for my problem...

Problem:
I have few SQL servers.. I need to design a DTS package that connects to all these servers and fetch the data ( eg query : select count(*) from master.sys.databases).
My approach :
I create a table with all the server names stored in it. Now, My DTS package shud connect to each server in the table and execute the query.. But for my the hurdle is ..how to change the ConnectionString dynamically..( i mean how to connect to all the servers one-by-one fashion)..

Any help/suggestion is appreciated.. can also suggest another approach also..

Thanks in advance
Vampire


--In 'thoughts'...
Lonely Rogue
Kenneth.Fisher
Kenneth.Fisher
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4326 Visits: 2033
I've actually had alot of success with 2 different methods.

1st and probably easiest:

Create 2 packages. The first one called "Cycle Servers" or something like it. All this one does is get the list of servers and loop through them putting the value into a variable.

I do this by creating an object variable and use an sql task to populate it as my result set. Then I use a For Each Loop container with the enumerator a Foreach ADO Enumerator, then my ADO object source variable is the variable (du Smile ).

Inside the loop I call my 2nd package.

Package 2: This one "does the work".

Here I have a Package Configuration using the "Parent Package Variable" that changes the connection. I my case I'm just changing the server name but you could change other parts of the connection also.

Now version 2 .. easier to explain but honestly I don't use it as often
Create a script task that changes the dts.connections("connection name").connectionstring.

Kenneth

Kenneth Fisher
I strive to live in a world where a chicken can cross the road without being questioned about its motives.
--------------------------------------------------------------------------------
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Link to my Blog Post --> www.SQLStudies.com
Dugi
Dugi
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2208 Visits: 3511
Oh no my choice was 3rd answer and it is wrong ... Steve are you going to add 1 point to my score!

w00t

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
skyline666
skyline666
SSC-Addicted
SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)

Group: General Forum Members
Points: 404 Visits: 229
So what happens if I answered 3 but got it wrong, do I get the point still?
vipin.narayane
vipin.narayane
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 3
Another approach on dynamic connections by Hari Sharma on
http://sql-bi-dev.blogspot.com/2010/07/dynamic-database-connection-using-ssis.html#comment-form
adam_pearson
adam_pearson
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 380
Hi Kenneth,
I've tried the option of a foreach loop both with dataflows in the foreach loop and also with a second package however always seem to encounter the problem that it only queries the first result from the table (connection string stored here).

I've added a message box and can see the connection text changing however the actual connection doesn't change.

Am I missing something obvious?

Thanks,
Adam
Kenneth.Fisher
Kenneth.Fisher
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4326 Visits: 2033
I would check and make sure your secondary package has a package configuration set up. It would need to be type "Parent Variable" and you can use it to change the servername (or database, or whatever) of your connection manager. These days I prefer to use expressions though. I've posted a quickie demo on my blog for you. Hope it helps.
Looping through multiple servers in SSIS

Kenneth Fisher
I strive to live in a world where a chicken can cross the road without being questioned about its motives.
--------------------------------------------------------------------------------
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Link to my Blog Post --> www.SQLStudies.com
adam_pearson
adam_pearson
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 380
Hi Kenneth,
Thanks for the example however no joy with that either. Using either a table as the source or hard coding it into the FOREACH container doesn't appear to work. When it executes it runs without problems however only returns the results from the local machine. The servername changes to the ones in the list (using a message box) however when putting items into a table (using the @@servername for example) it's only the location machine being picked up.

I've tried setting the 'delayValidation' setting on and off but still no joy. Tried connecting to different servers but that hasn't helped either.

Any suggestions?

Thanks,
Adam
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