July 26, 2016 at 5:50 pm
Hi Guys,
Can someone help me in SSIS,
Here I have Flat File Source and .CSV Destination. In Between, I want to use something like this.
Select
Table1.StartDate
,Table1.EndDate
,Table2.Phone
,Table2.Address
from dbo.Table1
Inner join Table2 on Table1.ID = Table2.ID
Where
Table2.Phone = FlatfileSource.Phone
and FlatfileSource.Date between Table1.StartDate and Table1.EndDate
Note:- FlatFileSource is my Raw File data source in SSIS
My question is, How can I use range syntax in SSIS. I don't think I can use Merge Join. However, I think I can use Lookup
transformation. Please help me out to accomplish above SQL to get Table2.Address from SQL Table.
Please let me know if my question is not clear.
Thank You.
July 27, 2016 at 1:41 am
Hi Rocky,
You cannot use a Lookup as this is an exact match only and between is not permitted.
There are a number of ways that you could do this
1. Do the join in a T-SQL and not within a data flow i.e. load the data into a work table in the DB and then do the join using an Execute SQL task.
2. User a Merge Join to join on Phone only and then use a Conditional Split to output rows only where the date is between the start and end dates
3. Use could use a Script Component to retrieve the data and write some code to do the lookup using a between range for the dates. I've not done this myself but technically it is possible if your .Net skills are good enough
4. If, and only if, the date in the FlatFileSource is a narrow range (e.g. one date only or a few days at most) you could bloat Table1 to give a row per date and then use this in a Lookup on an exact match. This does have an overhead if you lookup table is very big so not recommended.
Jez
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply