Extracting data from multiple servers SQL 2005 SSIS
- by Raj
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