Dynamic connections in SSIS

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Kenneth Fisher » Dynamic connections in SSIS

22 posts, Page 2 of 3

««1 2 3 »»

Dynamic connections in SSIS

Rate Topic

Display Mode

Topic Options

Author

Message

Steve Jones - SSC Editor

Posted Tuesday, January 29, 2008 7:57 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 3:19 PM
Points: 31,526, Visits: 13,863

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

Tom Garth

Posted Wednesday, January 30, 2008 7:46 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, February 04, 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

Lonely Rogue

Posted Tuesday, February 05, 2008 8:17 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 3:34 AM
Points: 165, Visits: 405

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

Kenneth.Fisher

Posted Tuesday, February 05, 2008 8:39 AM

Hall of Fame

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 2:29 PM
Points: 3,367, Visits: 1,575

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

Dugi

Posted Friday, May 02, 2008 12:48 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, April 02, 2013 1:48 AM
Points: 1,252, Visits: 3,367

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/

Post #494062

skyline666

Posted Friday, May 30, 2008 4:09 AM

Old 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

vipin.narayane

Posted Thursday, August 26, 2010 3:40 AM

Forum 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

adam_pearson

Posted Wednesday, October 31, 2012 11:40 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, June 14, 2013 4:00 AM
Points: 3, Visits: 307

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

Kenneth.Fisher

Posted Wednesday, October 31, 2012 1:46 PM

Hall of Fame

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 2:29 PM
Points: 3,367, Visits: 1,575

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

adam_pearson

Posted Monday, November 05, 2012 10:06 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, June 14, 2013 4:00 AM
Points: 3, Visits: 307

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 »

22 posts, Page 2 of 3

««1 2 3 »»

Permissions