Using variables in Lookup transformation

Question

Using variables in Lookup transformation

pwalter83

SSChampion

Points: 14575
More actions
June 18, 2019 at 10:59 am

#3654678

Hi,
I have a requirement to use variables in a lookup transformation, does anyone how can this be implemented in SSIS.
The SQL statement is - select distinct WEEK_NUMBER from DIMM_DATE where WEEK_START_DATE = ("20" + SUBSTRING(@[User::File_Name],72,2) + "-" + SUBSTRING(@[User::File_Name],69,2) + "-" + SUBSTRING(@[User::File_Name],66,2))
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99133 More actions · Answer 1

You'll need to add the value of the variable first as a column, using a Derived Column Transformation (Like I showed in your other topic). Then, instead of the variable reference that (new) column.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

pwalter83 SSChampion Points: 14575 More actions · Answer 2

Thom A wrote:

You'll need to add the value of the variable first as a column, using a Derived Column Transformation (Like I showed in your other topic). Then, instead of the variable reference that (new) column.

Sorry I didn't understand your reply.

Is it possible to use an alternate to Lookup transformation in this case ? Thanks.

Thom A SSC Guru Points: 99133 More actions · Answer 3

pwalter83 wrote:

Thom A wrote:
You'll need to add the value of the variable first as a column, using a Derived Column Transformation (Like I showed in your other topic). Then, instead of the variable reference that (new) column.
Sorry I didn't understand your reply. Is it possible to use an alternate to Lookup transformation in this case ? Thanks.

To do what? You haven't stated what you're trying to achieve here with the Lookup Transformation, just that you want to use a variable's value within the lookup tranformation. If you're trying to lookup a value, then a Lookup is the correct transformation.

What didn't understand about my reply? I'm afraid I don't really know how I can make it much clearer, as my other post covers how to use a Dervived Column Transformation, and if you don't know how to reference a column, then the problem is far more fundamental.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Phil Parkin SSC Guru Points: 246979 More actions · Answer 4

Create a variable expression to hold the entire SQL statement.
Add the lookup to the dataflow.
At the Control Flow level, get properties for the dataflow and go into Expressions.
Set the SqlCommand property for your lookup to the variable you created.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

TangoVictor SSChampion Points: 10975 More actions · Answer 5

I've never used Phil's suggestion, that sounds like a good route, i know using parameters in a lookup has pretty bad performance at least it has the times i've tried to use them.

What i've done is a merge join instead of a lookup then you can use your variable as a parameter in the source component. Now of course you'll need to sort both inputs this can also be a bad hit as well but if you by chance have or can sort the main input by the lookup key (using an order by in the SQL and setting the sort key in advanced editor), then its not a problem or if you're dealing with a smaller set of records. Do the merge as a left join just make sure the right side is distinct. Then handle your nulls however you wish using a derived column downstream.

Its another option, hth.