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?
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.