SSIS - Dynamic Lookup Query

Problem:

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?

Solution:

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

Original Post: https://stackoverflow.com/questions/42032542/ssis-dynamic-lookup-query/42032946#42032946