Lookup Transformation with where-clause conditions asscociated from source table

Question

How to use Lookup Transformation to realize the following T-SQL?

select a.id, a.YrMon, b.StartMon, b.EndMon

from TABLEa a

left join TABLEb b

on a.id = b.id

and a.YrMon between b.StartMon and b.EndMon

In DFT (data flow), source table TABLEa has a column YrMon and id, the lookup table TABLEb, how to set up to include the other join condition:

a.YrMon between b.StartMon and b.EndMon

Thanks!

---

surfbidn

Answer 1

The SQL has a valid syntax, so what is the issue?

Make a OLDdb or ADO source to populate the cache

---

Arthur My Blog

Answer 2

How to use data flow to realize that left join function?

---

surfbidn

Answer 3

in lookup transformation, there is option of write sql query. please follow these step:

Step1; open lookup transformation.
step2: Use results of an SQL query Choose this option to browse to a preexisting query, build a new query, check query syntax, and preview query results.  

for more information please visit
http://msdn.microsoft.com/en-us/library/ms189697.aspx

---

Arthur My Blog

Answer 4

But this query below is kind of table join also, not a simple where-clause condition depending on one table.

a.YrMon between b.StartMon and b.EndMon

---

surfbidn

Answer 5

Assuming that your Table A is your input table in the DFT and you want to do the Lookup to Table B:

In the Lookup Transformation Editor:

In General section:
Choose Cache Mode: Partial Cache
Connection type:  OLE DB Connection Manager
"Redirect rows to no match output" (instead of Fail Component)

In Connection section:
For starter (we will modify this in later steps), choose the OLE DB Connection Manager and for the "Use results of an SQL Query:" enter the following:

select b.id, b.StartMon, b.EndMon
from TABLEb b

In Columns section:
Map the Available Input Columns from Table A to the Available Lookup Columns from Table B as follow:
id to id
YrMon to StartMon (we will modify this in the next step)

In the Advanced section:
Check the "Modify the SQL statement" checkbox
In the box, use the following query:

select b.id, b.StartMon, b.EndMon
from TABLEb b
WHERE b.id = ?
AND ? between b.StartMon and b.EndMon

Click "Parameters..."
Set Parameter0 to id
Set Parameter1 to YrMon

Click OK to save.

Although your "Connection" section will not have the parameters in there and it seems like you are mapping YrMon to StartMon, the CustomQuery from the Advanced section will actually be the one used instead.  Therefore, you will get the result you wanted with the checking of the date range.

The output of the Lookup Transformation with have 2 green arrows that you will then have to merge using Union All.  Therefore, both the Lookup Match Output arrow and the Lookup No Match Output arrows should be pointing to one Union All.

In the Union All, you will have one column that have id, YrMon, StartMon, and EndMon (this is where the JOIN succeeded).
You will also have another column from the Lookup No Match Output (this is for the left Join of Table A that has no entries in Table B).  In this column, you will have id, YrMon, <ignore>, <ignore>, respectively.

The output of the Union All is then the result you wanted.

Hope this helps!

 

Answer 6

you can follow the method yosedesh suggested

OR

use OLE DB Command and write parametric sql command and set ID and  YrMon as input parameters and get output with output parameters. here you can see how to fetch output parameters from ole db command as columns in data flow:

http://www.rad.pasfu.com/index.php?/archives/24-Output-Parameter-of-Stored-Procedure-In-OLE-DB-Command-SSIS.html

OLE DB Command will run the sql command for each row in the data stream and it is very powerful, because you can write whatever you want in a stored procedure and just run that in OLE DB Command, so if your query against tableB for each combination of ID and YrMon of tableA will return just single row, you can use the OLE DB Command simply, if there might be multiple rows per each combination then using lookup works better for this.

---

http://www.rad.pasfu.com

Answer 7

Thanks for your suggestion.

I tried it on. The performance is much worse than direct running SQL scripts in "execute SQL task".

Such as 5 mins vs 0.5 min.

Anybody has any idea? What is the disadvantage to using "execute SQL task" directly?

Thanks!

---

surfbidn