Fetch data of same table from different server using SSIS.

Question

Hi all I am a starter learner of SSIS. I have created two sample application. 1st: Import data of a table to excel sheet 2nd: import data from two table linked with primary and foreign key relation into excel now i want to fetch data from a single table let's say tblPayment from two different server to excel sheet in my local system. can any one help me please??

Vishal Patel · Answer

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 OracleExcel 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.

Nirav Daraniya · Answer

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 : In above query replace you server name database name and table name as per your requirement. NOTE : for this process you need the create link server between you local PC and server  so you network SQL server can access easily.

Akshay Phadke · Answer

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