question

siugoalie78 avatar image
siugoalie78 asked

SSIS Lookup no match default value

I am building an SSIS package that uses a Lookup to pull an ID from another table based on text from the source table. For example, the Source may have "Item 1" in a text field. The Lookup table will have ID "XYZ" for Item 1. When "Item 1" does not exist in the lookup table, I want to select the default ID from the lookup table, which I must select from a SQL query, and use that ID instead. I have the Lookup set to output No Match values, but from that point, I don't see a way to set the value based on a SQL selection. Is there a way to do this?
ssislookups
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Tom Staab avatar image
Tom Staab answered
Use a separate Execute SQL Task to store the default value in a variable. Add a Derived Column Transformation to your data flow, and set the value to the value from the lookup or the default if the lookup value is null. Choose the option to replace the original column rather than create a new one.
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

siugoalie78 avatar image siugoalie78 commented ·
Thanks very much! I knew there had to be something I was missing!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.