question

VishalVamja avatar image
VishalVamja asked

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??
ssissql server
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image
JohnM answered
You should be able to use either a MERGE or MERGE JOIN transformation to facilitate this. Merge - https://msdn.microsoft.com/en-us/library/ms141703.aspx Merge Join - https://msdn.microsoft.com/en-us/library/ms141775.aspx You would setup to connection managers to your data sources and then use either of the transformations above to pull the data into a single data stream to be inserted into your destination table. Another reference: https://www.simple-talk.com/sql/ssis/ssis-basics-using-the-merge-join-transformation/
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hey John, Thanks for the response. The links you have provided is all about merging and joining data of different table. But what exactly i am looking for is, i have one table 'tblPayment' on two different server with same columns and i want all the data of that table from two server to a central server into excel file.
0 Likes 0 ·
Forgive me, but why then wouldn't a merge work? It's purpose is to stream two different tables into a single data flow, which if I understand is what you have. You essentially have two tables (even if the schema is the same) on two different servers. What am I missing here?
0 Likes 0 ·
Hey John, you are right "Merge" transformation works fine with two source inputs but still "merge join" is creating some issue. some how previously it was not working i was using merge join and perhaps i was missing some steps. now it works fine. thanks a lot. thanks for helping me
0 Likes 0 ·
nitish1300 avatar image
nitish1300 answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hey nitish, Thanks for helping me. "Union All" works fine as well. "Merge" also works with two input which is in sorted order. Thanks again
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.