SSIS: Dynamic Lookup Query

Question

I would like to implement the following logic to my Lookup query as an expression:

SELECT  ID,
        CASE @[User::ConnectionStringID]
        WHEN 1 THEN "NE"
        WHEN 2 THEN "BALT"
        WHEN 3 THEN "NY" 
        WHEN 4 THEN "PA"
        END AS Region
FROM    dbo.Table;

My package is executed in a loop where the connection string is dynamically updated using a config file and it loops 4 times as shown in the above logic.

I realized today that the Lookup transformation has no expressions available to it and I haven't been able to find another suitable solution. Has anyone found a way around this or a custom transformation script?

Answer 1

Lookup control has expressions, but cannot accessed directly from it just go to your Dataflowtask properties and access the expressions like shown in the image below