CODEWITHSUNDEEP
sqlsql-serverssisetlssis-2012
Ice
Ice

Reputation: 459

SSIS using variable in Lookup Transform

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.

Upvotes: 4

Views: 2920

Answers (2)

Dave Sexton
Dave Sexton

Reputation: 11188

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:

enter image description here

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:

enter image description here

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

Upvotes: 2

Hadi
Hadi

Reputation: 37313

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] + "')

enter image description here

Upvotes: 4

Related Questions