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.