Hi All,

I am using SSIS 2012 and have kind of common requirement as below:

I need to fetch data from multiple servers (total 5 servers) In all these 5 servers the database name is same and also the table name is same (from which I need to fetch data using simple SELECT query)

And in my destination server (where I am finally loading data in fact table, from the above 5 servers)

As of now, I am already loading data from 1 server into the destination server's fact table. Now I want to do the same thing for the remaining 4 servers also, i.e. iterate through each server (using the same database name and same table name)

Any idea ?

Thanks

Refer the below example,

http://www.sanssql.com/2011/08/looping-through-sql-servers-using-ssis.html

Thanks RSingh, Forgot to mention one thing.

All this 5 servers are having credentials i.e. SQL server authentication (user name and password)

How to handle that ?

Please mention what if my input servers have sql server authentication (un/pwd)

I tried creating package as per the link given above and it failed at the time of FOREACHLOOP EDITOR (i think bcas servers require un/pwd)

And also how do i use the database name to fetch data from in each iteration ?

Here is my error. The case is the same as i mentioned above:-

[Connection manager "Source"] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80040E4D  Description: "Login failed for user 'sa'.".

Please try this link,

http://sql-developers.blogspot.jp/2010/07/dynamic-database-connection-using-ssis.html