Extracting data from multiple servers SQL 2005 SSIS

Question

I have created an SSIS package to connect to multiple SQL servers, create a database, a table and a stored procedure. The package also creates a job and schedules it to run every 5 minutes.

The requirement is to collect performance metrics. I am using an ado object variable to get the server names and all the above tasks are in a for each loop and everything works fine.

Now the problem:

I need to create a data flow task, which will connect to each of these servers in turn, copy the performance metrics data over to a central server and purge the source table. I am unable to get this task to work. This task fails with "Unable to obtain Connection" error.

Any help will be greatly appreciated.

SQL Server Version : 2005

Thanks, Raj

Answer 1

I haven't done it with servers but I have done this with file connections. Instead of a data flow, start with a For each loop that will loop through a list of servers and set the connection from a variable that you populate in this loop. Then put the data flow inside the looping task.