how to fetch data of a single table from different server using ssis?
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??
So, what you actually mean is "you have two server sources with same table in it. You want to retrieve data from both servers (Single table of same table name each) and populate it to excel destination. If this is what u mean, u can do the following, step 1: Take a dataflow task in control flow pane. step 2: Define dataflow task with TWO OLEDB Source. step 3: Define each Oledb Source with their respective server names and table name. step 4: After defining Sources, Take a UnionAll Transformation. Link the Source connections to union all transformation. step 5: Check whether all columns are matching in the UnionAll Transformation. If you think that's fine with column names of tables. step 6: Take a Excel Destination, connect the UnionAll transformation output to Excel Destination. step 7: Define Excel Destination. That is all you have to do..!!! You're done importing data from different servers to single excel file. Note: Check the Datatypes when you send data from Oledb Source to Excel Destination. There will be data mismatch between source and destination. For example: Excel accepts nvarchar type and oledb accept varchar