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.

  • Sqlcmd: Error: near command 'Ë'
  • Connecting to MSSQL with Codeigniter PHP
  • sql Optimize table valued function
  • How to use CHECKPOINT efficently in a application that uses FILESTREAM
  • Sporadic SQL Server error when trying to connect to the database
  • Update Statement with Sub-query
  • 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.