SSIS using variable in Lookup Transform

Question

In the Lookup Transformation , I specify a reference data set to use as follow:

SELECT DISTINCT Client_ID 
FROM     dbo.CLIENT
WHERE  (ENROLLMENT_DATE >='2016-07-01') AND (DE_ENROLLMENT_DATE <='2017-06-30')

If I want to replace the hard-coded '2016-07-01' and '2017-06-30', does anyone know how I can do it? Thank you for your help.

you can always load your data to a stage table, then do a insert by T-SQL/Stored Procedure. Thus you have whole lot of flexibilities to do whatever you want to transform. — Wendy, May 15 '17 at 20:35

Hadi · Answer 1 · 2017-05-15T20:43:26.553

You can use expressions to achieve this, just follow these steps:

Create two variable (ex @[User::strBeginDate] and @[User::strEndDate]) of type string
Mouse click on the DataFlow Task and press F4 to show the properties Tab
On the properties Tab, Go to Expressions
You will find [Lookup Transformation].SqlCommand

Use the following expression

"SELECT DISTINCT Client_ID 
FROM     dbo.CLIENT
WHERE  (ENROLLMENT_DATE >='" + @[User::strBeginDate] + "') AND (DE_ENROLLMENT_DATE <='" + @[User::strEndDate] + "')

score 2 · Answer 2 · answered May 15 '17 at 20:48

Before your Lookup Transformation add a Derived Column Transformation, in this create two new columns to get the month and year from a date variable. Like so:

In your lookup transformation use the following query:

SELECT 
  Client_ID
  ,YEAR(ENROLLMENT_DATE) AS ENROLLMENT_YEAR
  ,MONTH(ENROLLMENT_DATE) AS ENROLLMENT_MONTH
FROM CLIENT

Then in the Columns page of your Lookup Transformation wire up your query like so:

I don't think you need DISTINCT in your query because the Lookup Transformation just returns the first row that it finds.

SSIS using variable in Lookup Transform

2 Answers2