[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 149
  • Last Modified:

How do I update a column in a table on one server by doing a left join of tables another server in SSIS using look up or any other method and no linked server

Hi!

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
set a.col1=b.col1
from a
left join (select
               C.COL1,
               C.COL2,
               SUM(C.COL3),
               C.COL4,
              C.COL5
              FROM (
               SELECT
                    D.COL1,
                    E.COL2,
                    F.COL3
                    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 -------
                         -------) C
                 GROUP BY C.COL1,C.COL2,C.COL4,C.COL5)b
                 on a.col1=b.col1
                        a.col2=b.col2
                        a.col4=b.col4
                        a.col5=b.col5

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!
0
amukta
Asked:
amukta
  • 8
  • 4
  • 3
  • +1
2 Solutions
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
You can use a lookup transformation, however, depending upon the volume of data, the performance characteristics may vary. My vote would be to fetch the required data into a staging table in the server1 and then performing the operations on that staged data.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I agree on that, especially if the lookup table is small (an alternative would be a replicated table...)

as for the syntax of the update, check this article:
http://www.experts-exchange.com/Database/Miscellaneous/A_1517-UPDATES-with-JOIN-for-everybody.html
0
 
ZberteocCommented:
As I can see from your query you deal with 4 servers and it is also wrong. This part particularly:

                  SELECT
                        D.COL1,
                        E.COL2,
                        F.COL3
                        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
                      ...

I expect a comma before ROWNUMBER, which should actually be ROW_NUMBER.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
amuktaAuthor Commented:
This was just the pseudocode that I've put in for the question. However, my code has no syntax errors and works fine. I was asked not to use any staging table or anything as such. I'd need to replicate the above update
with the look up transformations. Could you please let me know , how I can perform this update using look up transformations?
0
 
amuktaAuthor Commented:
There's a typo mistake, for server 3 and server 4, they are all server 2 in the subquery.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so you want to refer to a remote server in your query, which is perfectly possible using linked server.
are you currently using that? if yes (as that does not use staging tables), I would not understand the question;
if no, you only need to set up the linked server (but then I don't understand how you could claim the sql works)

please clarify
0
 
ZberteocCommented:
If your problem is performance then you should understand that when you refer to a table through a linked server and used it on the current host server to join that WILL cause performance problems because the whole table has to be accesses and made available over the network and brough over and then joined  on the current host(server1). The best way to deal with this issue is to execute the code that references objects on the linked server on that server first and only bring over what you need so all those joins between tables on server2.database2 will take place on server2 and only the result will be brought over.

Basically what you should do is to create a view on the server 2 with all those table and access THAT using the linked server. Something like this:
-- on server2 database2:
create view vwOnServer2
as
	select <cols_list_here>
		TABLE 4 F  ON E.COL1=F.COL1--server 2 is the linked server
        INNER JOIN TABLE 5 G ON F.COL1=G.COL1
        LEFT JOIN TABLE 7 H ON G.COL1=F.COL1
        LEFT JOIN TABLE 8 I ON I.COL1=F.COL1
            LEFT JOIN -------
                -------	 

-- on server 1 use:
Update table 1 --table 1 is on server 1 and database1
set a.col1=b.col1
from a
left join (select 
               C.COL1,
               C.COL2,
               SUM(C.COL3),
               C.COL4,
              C.COL5
              FROM server2.database2.vwOnServer2 C
                 GROUP BY C.COL1,C.COL2,C.COL4,C.COL5)b
                 on a.col1=b.col1
                        a.col2=b.col2
                        a.col4=b.col4
                        a.col5=b.col5

Open in new window

0
 
amuktaAuthor Commented:
we are not SUPPOSED TO use linked servers anywhere. I was asked to modify my SSIS package to use look up transformations. my question is how do I achieve the above update using look up transformations. Can someone please help me with that?
0
 
amuktaAuthor Commented:
what join does the look up transformation use by default?Is it inner join or left outer join?
0
 
amuktaAuthor Commented:
can someone answer my question above? I think it'd be a good idea for me to close the experts exchange account and start googling stuff myself, because I don't get apt answers right away.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
lookup is a left join
0
 
ZberteocCommented:
@amika

You have to understand that all the experts here are volunteering. We are not paid to do this but we help people because we like to do it. We don't always meet the highest expectations from our askers but I would say that vast majority of questions are answered, maybe over 95%. Sometimes we don't have enough information to work with or we are simply mistunderstood.

Patience will overcome all these problems.
0
 
amuktaAuthor Commented:
Well, I've posted my question on the 22nd. Today is the 29th. I've been having patience for 1 week. So, then , is having patience for a month called having patience? Well, the reason we sign up for experts-exchange is to make our job atleast a bit easier in finding things. We pay atleast $140 membership fee. If following up on a question after a week, is not considered patience, what's the point in signing up by paying the above amount. I have not posted a question a second ago and followed up with you at this second. I still do not have the highest expectations instead of paying money. Most of the time, we don't get answers  faster and I try googling for stuff, most of the time. I only post questions on this site, when it is something that I'm not clear with, may be once in 3 months or probably 6 months. Most of the time, I get answers from you guys after sometime, when I figure it out in a day or two. With highly demanding jobs, it's no use, if we get answers a week after we post the questions. if we don't give points to a certain questions and we do not accept any solution after a few days, even when the solutions don't meet our expectations, then , the questions will be considered as abondaned and we are forced to accept a solution, But how about you guys taking a long time? Well, you said that you volunteer and not paid to answer these questions. You volunteer because to like it, and then, why is the delay. Most of my friends have closed their account with experts exchange for the same reason.
0
 
amuktaAuthor Commented:
Thank you! Hi! Mr.Hengel.But reading about look up, it says that the first matched row is joined between the 2 tables, but the left join only matching rows from the 2nd table and all the rows from the 2nd table. Could you please confirm if I can still use look up for my left joins?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
left join/lookup : you need to be very clear about what you define to be "table 1" and "table 2" ...

consider this lookup table:
person_title:
TITLE_CODE    TITLE_DESCRIPTION
MR                     Mister
MRS                   Miss

so far, it has only 2 values...

the FACT table would be a person table:
PK     TITLE_CODE   NAME
1       MR                    Hengel
2       MRS                  Schmit
3       DR                      Braun
4       MR                     Müller


select p.PK, p.TITLE_CODE, l.TITLE_DESCRIPTION, p.name
  FROM person p
   LEFT JOIN person_title l  ON l.title_code = p.title_code

it will return 4 rows from person, even if the 3rd row does not have a matching row in the "2nd / lookup" table.

if you reverse the query:
select p.PK, p.TITLE_CODE, l.TITLE_DESCRIPTION, p.name
  FROM person_title l
   LEFT JOIN  person p ON l.title_code = p.title_code

it will return only 3 records, as for the 4 row in persons (DR) there is not row in what is the the "first / fact " table.
0
 
amuktaAuthor Commented:
A quick question, if I have to update a column in a table on server A, by doing a left join on about 7 columns from a table on server. How can this be performed using look up transformation. Can updates be performed using look ups? Can someone please be able to provide me an example , of how this can be done using look up transformations?

Thanks a million in advance.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 8
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now