asked
SQL LINKED QUERY VS SSIS
Hi , I am using a linked query to join two tables from different servers and insert the data to a table. It takes hours to execute and I am trying to improve its performance. I thought of using Join transformation in SSIS Dataflow tasks to achieve the same. Since Joins are blocking transformations , I am not sure if it is better. I am yet to get access to execute it against the data. Is moving to SSIS a better approach ? Is there a better option to do this?? Thanks Kannan
I'll start with "It depends." It's difficult to say what the best approach is without more details, but I can make a few suggestions. All of my suggestions will be to use SSIS because I believe you are much more likely to develop a good solution with that rather than using linked servers. If you need 1 row from table A and 1 row from table B to combine together and form 1 row in table C, try a join or maybe a lookup. If you need rows from table A and rows from table B to both be inserted into table C (but as separate rows), your best bet might be to sort the data in the source SQL and then use a Merge transformation. You will just need to use the advanced editor to indicate that the sources are already merged to avoid using the Sort transformation.
18 People are following this question.
