October 4, 2011 at 3:13 pm
I am in middle of the package
I have a resultset of few columns like
ID1,Date1,X,Y,Z,,,,,, (It belongs to one database)
Now I need to get a value from a table1 in other database.
Table1 have columns as ID2,Date2,Date3,ValueX
Now I need to get ValueX by joining or looking up with ID1,Date1
ID1 should match exactly to ID2
Date1 should be in between Date2 and Date3.
Let me know if I am not clear
October 5, 2011 at 8:02 am
You have two options. If this is a 1-to-1 lookup, and you are just looking for the value to exist, then first, put a Lookup Transform in. Have the lookup transform contain the query:
SELECT ID2, Date2, Date3, ValueX
FROM Table1
Join the ID1 column from your first set to the ID2 column in the Lookup Transform. Get back the values for Date2, Date3, and ValueX. Then use a Conditional Split, and compare Date1 with Date2 and Date3. If The date fits in between, then continue with your operations, otherwise redirect row to nowhere so you can ignore it.
If this is a 1-to-many lookup, then you'll need to use the Merge Join Transformation. Have two datasets. Your first dataset will be the resultset you already have. Your second dataset will be from the OLE DB Source through the same query as the one provided above for the Lookup Transformation.
Make sure to sort both result sets by the ID column. If they aren't sorted from the source, add a Sort Transformation to sort them. If they are sorted at the source, then make sure to set the IsSorted property of the output to True.
Join the two through the Merge Join Transformation. Then, put a Conditional Split and evaluate the date ranges the same way as with the Lookup Transformation option above.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply