Using a variable in a lookup in SSIS

Question

I am trying to do a loop through a few data flow tasks. With each loop it increases the date variable by 1 (which is stored in a variable). Inside the data flow tasks I have a lookup task that is supposed to use the variable which is being incremented as part of the sql statement, something like this:

Select
    *
From
    Table
Where
    Date = @[User::Date]

But this doesn't work, I get this error:

Must declare the scalar variable "@".

Any help in trying to get this variable into the sql of the lookup would be greatly appreciated. I've also tried using parameters within the lookup but also get an error saying there isn't enough parameters

Solution

Use a derived column before the lookup and the set the value of the new column to the variable. Then in the lookup transform you can just do a straight mapping with the new derived column and the date column in your table and lookup intended columns.

In your case it looks like you would need to use SQL due to the nature of your requirement. Hence after changing your Cache Mode to Partial Cache, go to advanced tab and change your SQL to -

Select * 
From Table 
Where Date <> ?

Click on parameters button and map your derived column to Parameter0. Hence the Lookup Match output would now essentially give you all the dates that does not match your derived column that is set by the variable @[User::Date].

Comments

[Caveman42]

I did this, but I am using the lookup no match output, which will give me a dump of all dates that don't match the one I want to query on.

[TMNT2014]

SSIS Look up would not work that way since your input for lookup is the @[User::Date] and the table you are looking up values from is "Table". Will update my answer shortly with another approach.

[Caveman42]

The main problem from this is the derived column isn't appearing in the parameter list when I try to edit the SQL in the advanced section.

[TMNT2014]

Ahh... well you would need to go back to the column's tab and map your derived column to the date field in the table initially for it to show up in the list of parameters.