Dynamic Connection TO DIFFERENT INSTANCES in SQL SERVER SSIS 2008

Question

Dynamic Connection TO DIFFERENT INSTANCES in SQL SERVER SSIS 2008

varma1412-1092791

Valued Member

Points: 63
More actions
February 15, 2010 at 10:38 am

#98989

Hi,
It would be great if someone can help me out.
I have a table which has a column with list of Instances in it. By calling this table I have a test table which has to go into each instance and same database name in all the instances.
In simple words, We have a test table which has go into list of instances which are located in a table.
And database names are same in all the instances.
The connections should be dynamic.
can any one help me out with above issue.
Thanks

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 1

I see you doing this with a Exec SQL Task to return a full resultset that is used by a For-Each Loop to hold a data-flow task.

CEWII

SQLRNNR SSC Guru Points: 281324 More actions · Answer 2

I agree with Elliott.

Here is a link on how to do that

http://www.codeproject.com/KB/database/foreachadossis.aspx

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 3

I'm saving that link for future use, I hate re-explaining that everytime this question comes up and it seems to come up a lot..

CEWII

SQLRNNR SSC Guru Points: 281324 More actions · Answer 4

2nd or 3rd time in the last couple of days that I have seen it. It is sometimes easier to just give a reference.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 5

Elliott Whitlow

SSC Guru

Points: 102296

February 16, 2010 at 10:09 am

#1119364

That reference definitely shows all the steps.

CEWII

varma1412-1092791 Valued Member Points: 63 More actions · Answer 6

Please help me out with the above question. No one answering .

SQLRNNR SSC Guru Points: 281324 More actions · Answer 7

varma1412-1092791 (2/16/2010)
Please help me out with the above question. No one answering .

An answer and a resource on how to do it was posted. Is the question different than the answer and resource provided?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 8

CirquedeSQLeil (2/16/2010)
varma1412-1092791 (2/16/2010)
Please help me out with the above question. No one answering .
An answer and a resource on how to do it was posted. Is the question different than the answer and resource provided?

Agreed, as you explained your need the posted link is absolutely the resolution.

CEWII

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 9

Only problem I see with the solution provided, it doesn't work in SQL Server 7/2000 which is where this post occurs as it is for SSIS, not DTS.

SQLRNNR SSC Guru Points: 281324 More actions · Answer 10

Only requirement is that SSIS be installed. If it is installed, it will be able to perform this for SQL 2000.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 11

I'd say the title pretty clearly says SSIS regarless of what group it was posted to:

"Dynamic Connection TO DIFFERENT INSTANCES in SQL SERVER SSIS 2008"

CEWII

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 12

We have our SSIS packages setup to use dynamic connections. Works great and is easy to do in SSIS. In fact, I think it is easier is SSIS thatn it was in DTS, just seems more difficult because so much can be done dynamically in SSIS.

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 13

varma1412-1092791 (2/15/2010)
Hi,
It would be great if someone can help me out.
I have a table which has a column with list of Instances in it. By calling this table I have a test table which has to go into each instance and same database name in all the instances.
In simple words, We have a test table which has go into list of instances which are located in a table.
And database names are same in all the instances.
The connections should be dynamic.
can any one help me out with above issue.
Thanks

Just to verify, are you working with SQL Server 7/2000, based on where this thread is posted, or with SQL Server 2008?