A column in one table on say server 1 needs to be updated by doing a left join of 4 or 5 tables on another server, say server2. Currently, I'm using an update statement in an Execute SQL task of SSIS and a 4 part naming convention to join the tables on Server 2, with server1(linked server concept),to achieve this. How can I do this without using a linked server in the joins and by using look up transformation?
I'm doing something like this:
Update table 1 --table 1 is on server 1 and database1
left join (select
ROWNUMBER() OVER(PARTITION BY E.COL1, E.COL2,E.COL3,E.COL4,F.COL1,F.COL1)
ORDER BY E.COL1,E.COL2,E,COL3,E,COL4)
FROM TABLE3 E
LEFT JOIN server2.database2.TABLE 4 F ON E.COL1=F.COL1--server 2 is the linked server
INNE R JOIN server2.database2.TABLE 5 G ON F.COL1=G.COL1
LEFT JOIN server3.database2.TABLE 7 H ON G.COL1=F.COL1
LEFT JOINserver4.database2.TABLE 8 I ON E.COL1=J.COL1
LEFT JOIN -------
GROUP BY C.COL1,C.COL2,C.COL4,C.COL5)b
Can someone please help me using look up transformation or any other method in ssis, to avoid using linked server? Greatly appreciate your help! Thanks a million in advance!