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.

Answer 1

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

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

5. Use the following expression

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