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.

  • Problems with Date Java8 Hibernate5.2
  • Rounding issue with SQL Server and REAL datatype
  • Column encryption in ASP MVC app with SQL Server 2016 using .net Core / EF Core
  • TooManyRowsAffectedException with encrypted triggers
  • SQL User Defined Function Within Select
  • How do I set the default database in Sql Server from code?
  • 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

    enter image description here

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.