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 ««123»»

Dynamic connections in SSIS Expand / Collapse
Author
Message
Posted Tuesday, January 29, 2008 7:57 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 2:28 PM
Points: 33,062, Visits: 15,174
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
Post #448862
Posted Wednesday, January 30, 2008 7:46 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
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
Post #449392
Posted Tuesday, February 5, 2008 8:17 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 10:31 PM
Points: 167, Visits: 409
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
Post #451659
Posted Tuesday, February 5, 2008 8:39 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, July 18, 2014 1:31 PM
Points: 3,461, Visits: 1,784
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 :) ).

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
Post #451677
Posted Friday, May 2, 2008 12:48 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:25 AM
Points: 1,262, Visits: 3,419
Oh no my choice was 3rd answer and it is wrong ... Steve are you going to add 1 point to my score!



============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #494062
Posted Friday, May 30, 2008 4:09 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 24, 2008 1:27 PM
Points: 350, Visits: 229
So what happens if I answered 3 but got it wrong, do I get the point still?
Post #508937
Posted Thursday, August 26, 2010 3:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 1:32 AM
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
Post #975482
Posted Wednesday, October 31, 2012 11:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 11, 2014 3:19 AM
Points: 3, Visits: 361
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
Post #1379469
Posted Wednesday, October 31, 2012 1:46 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, July 18, 2014 1:31 PM
Points: 3,461, Visits: 1,784
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
Post #1379510
Posted Monday, November 5, 2012 10:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 11, 2014 3:19 AM
Points: 3, Visits: 361
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
Post #1381184
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse