SSIS: dynamic search query

Question

I would like to implement the following logic for 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 runs in a loop in which the connection string is dynamically updated using the configuration file, and it passes 4 times, as shown in the above logic.

Today I realized that the Lookup transformation has no expressions available, and I could not find another suitable solution. Has anyone found a way around this or a custom conversion script?

Answer 1

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