Need to implement left join using look up

Question

Need to implement left join using look up

komal145

SSCrazy Eights

Points: 9918
More actions
November 8, 2016 at 11:21 am

#331530

I have a stage table S and new columns from other table T , i want to do left join on stage S to get all columns and new columns from T.
example:
Table A
column1 column2 column3
Table B
Column3 Column4 Column5
Insert it into another table C ( which is created with all these columns)
Select column1,column2,column3,Column4,
Column5
FRom TABLEA left join TableB on A.Column3=B.Column3
Need to do using look up....how can we do. Explain Flow of the package.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

My experience has been that this is faster and easier using plain ol' T-SQL. Why not just create a script task and use the query that you queries that you posted?

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

komal145 SSCrazy Eights Points: 9918 More actions · Answer 2

I would do it in tsql ...but wanted to do implment in ssis.

Because table a in one server and table b on other

Phil Parkin SSC Guru Points: 246979 More actions · Answer 3

komal145 (11/8/2016)
I have a stage table S and new columns from other table T , i want to do left join on stage S to get all columns and new columns from T.
example:
Table A
column1 column2 column3
Table B
Column3 Column4 Column5
Insert it into another table C ( which is created with all these columns)
Select column1,column2,column3,Column4,
Column5
FRom TABLEA left join TableB on A.Column3=B.Column3
Need to do using look up....how can we do. Explain Flow of the package.

Flow of package:

Within a Data Flow

Select Source table columns (TableA)

Lookup component (TableB, match on A.Column3 = B.Column3)

Map to Destination table columns

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

komal145 SSCrazy Eights Points: 9918 More actions · Answer 4

I did use lookup on column3 but this way we get only matched data right? i need all data + matched one too to insert into new table.

Phil Parkin SSC Guru Points: 246979 More actions · Answer 5

komal145 (11/8/2016)
I did use lookup on column3 but this way we get only matched data right? i need all data + matched one too to insert into new table.

Have you tried sending the 'Lookup No Match Output' to the same destination table?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

komal145 SSCrazy Eights Points: 9918 More actions · Answer 6

i am doing look up on destination to see if there is key already exists if not insert new record. SO i cannot send both match and unmatched to destination loookup.

Phil Parkin SSC Guru Points: 246979 More actions · Answer 7

komal145 (11/8/2016)
i am doing look up on destination to see if there is key already exists if not insert new record. SO i cannot send both match and unmatched to destination loookup.

So, rather than a lookup, you may have to consider using the (rather slow) MERGE JOIN component.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

komal145 SSCrazy Eights Points: 9918 More actions · Answer 8

komal145

SSCrazy Eights

Points: 9918

November 8, 2016 at 1:42 pm

#1911288

Thank you. Merge join worked .

MMartin1 One Orange Chip Points: 27799 More actions · Answer 9

komal145 (11/8/2016)
Thank you. Merge join worked .

Just a note, make sure the input streams are sorted in the same manner. I have seen where they were not and the data would come out incorrectly, no errors thrown.

Are you using SQL Command as your data access mode or Table or View or ...?

----------------------------------------------------

ShinyK Grasshopper Points: 20 More actions · Answer 10

By default, the Lookup operates as an INNER JOIN - but we need a LEFT (OUTER) JOIN. Click the "Configure Error Output" button to open the "Configure Error Output" screen. On the "Lookup Output" row, change the Error column from "Fail component" to "Ignore failure". This tells the Lookup transformation "If you don't find an INNER JOIN match in the destination table for the Source table's ColID value, don't fail." - which also effectively tells the Lookup "Don't act like an INNER JOIN, behave like a LEFT JOIN".

Read it in this blog - http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspx

Phil Parkin SSC Guru Points: 246979 More actions · Answer 11

ShinyK (11/10/2016)

By default, the Lookup operates as an INNER JOIN - but we need a LEFT (OUTER) JOIN. Click the "Configure Error Output" button to open the "Configure Error Output" screen. On the "Lookup Output" row, change the Error column from "Fail component" to "Ignore failure". This tells the Lookup transformation "If you don't find an INNER JOIN match in the destination table for the Source table's ColID value, don't fail." - which also effectively tells the Lookup "Don't act like an INNER JOIN, behave like a LEFT JOIN".

Read it in this blog - http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspx%5B/quote%5D

Why not just change the Join Type? See this image.

--Edit: Apologies ... I see that you are talking about the Conditional Split, even though the Merge Join has been accepted as the solution by the OP.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

MMartin1 One Orange Chip Points: 27799 More actions · Answer 12

ShinyK (11/10/2016)

By default, the Lookup operates as an INNER JOIN - but we need a LEFT (OUTER) JOIN. Click the "Configure Error Output" button to open the "Configure Error Output" screen. On the "Lookup Output" row, change the Error column from "Fail component" to "Ignore failure". This tells the Lookup transformation "If you don't find an INNER JOIN match in the destination table for the Source table's ColID value, don't fail." - which also effectively tells the Lookup "Don't act like an INNER JOIN, behave like a LEFT JOIN".

Read it in this blog - http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspx%5B/quote%5D

The problem here is that if you use Full Caching then the the lookup can miss a lookup value that does exist (depending on the size of the underlying recordset), rendering a false positive on the resulting NULL. Hence I think the MERGE is better especially with the large data sets. Lookup to me gives the message that there should be a value pulled (just by the very name of the transform), and I would use the ignore failure mode just to aid with testing at a certain level.

----------------------------------------------------

komal145 SSCrazy Eights Points: 9918 More actions · Answer 13

So, Merge is good ? I am little confused to use lookup or merge.

MMartin1 One Orange Chip Points: 27799 More actions · Answer 14

Since you are unsure and want to implement a left join I easily recommend MERGE. The Lookup transform is a little different. Try reading more about them on MSDB. For sure I would not use the Lookup for "off label" use (Something for which it was not intended).

----------------------------------------------------