Sql 2000 - dynamically changing connections to diff servers

[ReportDr00]

I am kind of new to DTS and i have requirement to execute same query on 60 diff servers and store the results in sql server.
I need to do this using DTS by changing server name and connection dynamically.
I have ODBC connections created to 60 diff servers that i can use.
I really appreciate your input.

Armani

 

[AlexCuse]

I think that a bit of looping mixed with use of some global variables or a dynamic properties task would be all you need. You are trying to set up just one task, and execute it for all 60 servers, right?

As you are new to DTS, I suggest this site:

http://www.sqldts.com

It is awesome. You will be able to find out all you'd want to know about the things I mentioned there.

Post back if you have any specific questions.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[ReportDr00]

Yes, i have distributed environment and will have to change the connection task dynamically after the query runs and stores it in sql table.
I have followed the wrox example and was able to create the package with execute sql task, two dynamic tasks and one send mail task. The two dynamic tasks is on success of sql and on failure of sql. I know this is not what i am after i just want to see how it works. If i could figure out or any body help me figure out how to make this package to loop through 60 different servers and get data from each server and store it in sql table.
I will also refer to the link in the mean while.
any sample available on the net will be great help.

Armani

 

[AlexCuse]

There seems to be problems at the website ATM but this is a really good article when it comes back up:

http://www.sqldts.com/246.aspx

This one might give you an example to work with aas well.

http://sqljunkies.com/Article/A8CB0AFE-D143-4B49-B865-4FBBFEDFCCD7.scuk

Either will need to be adapted to your particular task.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom