Solved

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

Posted on 2015-01-18
16
130 Views
Last Modified: 2016-02-15
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
Comment
Question by:amukta
  • 8
  • 4
  • 3
  • +1
16 Comments
 
LVL 11

Assisted Solution

by:Nakul Vachhrajani
Nakul Vachhrajani earned 250 total points
ID: 40557052
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40557101
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
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40557684
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
 

Author Comment

by:amukta
ID: 40561084
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
 

Author Comment

by:amukta
ID: 40561085
There's a typo mistake, for server 3 and server 4, they are all server 2 in the subquery.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40561377
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
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40561938
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
 

Author Comment

by:amukta
ID: 40565656
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:amukta
ID: 40565671
what join does the look up transformation use by default?Is it inner join or left outer join?
0
 

Author Comment

by:amukta
ID: 40576468
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
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 40576825
lookup is a left join
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40577357
@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
 

Author Comment

by:amukta
ID: 40578811
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
 

Author Closing Comment

by:amukta
ID: 40578821
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40579319
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
 

Author Comment

by:amukta
ID: 40619149
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now