Using variables in Lookup transformation

  • pwalter83

    SSChampion

    Points: 14519

    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.

     

  • Thom A

    SSC Guru

    Points: 98219

    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.

  • pwalter83

    SSChampion

    Points: 14519

    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: 98219

    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.

  • Phil Parkin

    SSC Guru

    Points: 243485

    • 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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • AlphaTangoWhiskey

    SSChampion

    Points: 10777

    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.

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

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