Fetch data of same table from different server using SSIS.

Hi Nirav,

Thanks for the response

you are right we can achieve this by using SQL import and Export Wizard only when you are working with data of sql server table only. But what if data is coming from different database/source like Oracle,Excel, Xml or Flat file from different server?

so what i want is , i want to perform all transformation at SSIS application in visual Studio only.

After doing some R&D, i have got the solution using "Merge" and "Union All" transformation, i get what i was looking for.

Thanks for response.

Hi,

When you make SSIS package at time you have to select "Write Query to specify the data to transfer" in the "SQL Server Import Export Wizard"

 

 

Now write following query :

 

SELECT * FROM [SERVER1\SQL2012].[DATABASE1DB].[dbo].[tblPayment]

union all

SELECT * FROM [SERVER2\SQL2012].[DATABASE2DB].[dbo].[tblPayment]

 

 

 

Now click on OK and cont. your process.

 

NOTE : for this process you need the create link server between you local PC and server ,

create a two source destinations and in edit mapping give their IPS and crredentials like databasename and passowrd and map those two sources to one destination ie in your case excel