May 27, 2013 at 12:14 am
I have an excel datasource which must be unpivoted, case statements run against it, joined with look-up tables to pull in additional values. Here's what I've got working so far:
1. Excel Source transform
2. Data Conversion transform (to handle for Unicode coming in from excel and sending to SQL)
3. Unpivot transform
4. Derived Column transform
5. Look up transform
I've performed the above steps (1-5). The look up transform is easy enough to configure ie. I supply the columns whose values must match and retrieve an associated value. But, the final output of the look up transform is now just the one value I looked up. Since I need all the values of the Derived Column output PLUS the looked up value from the look up transform to be merged together into final output, I have turned for a while to the Multicast and Merge transforms. But the Merge transform in particular requires so much niggling with Sort properties that before I proceed further I want to make sure I'm taking the best route with SSIS transforms.
Will someone please advise on the best method for joining the output of the Lookup transform to the output of the Derived column which precedes it, besides multicasting and merging? Is there a better way to represent Left Joins in SSIS besides Lookup tables? Is lookup transform the best one to use if I need error handling to capture rows in source table for which there is no lookup value returned?
May 27, 2013 at 5:55 pm
I found the following youtube video by Microsoft technical writer showing how to create a lookup against a cached referenced data set. http://www.youtube.com/watch?v=FB5OPkGsDtA
It shows how to create a lookup using a cache connection manager.
Trouble is that in video the final output of the lookup is added to an existing sql table in the ole db destination BUT I want to combine the results of my lookup to the results of my derived column transform before dropping combined output to an empty sql table.
Still would be happy to hear from anyone who knows how to do this.
May 27, 2013 at 10:36 pm
in case it helps someone else, the video that helped me get Lookups straight was the one posted earlier plus this one
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply