SSIS: Dynamic Lookup Query
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?
One Solution collect form web for “SSIS: Dynamic Lookup Query”
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