none
Range Lookup in SSIS on Advanced tab

    Question

  • Typically, a Lookup Transform in SSIS is used for straight EQUAL joins. I want to do a Range Lookup and am following directions from several different posts, but getting an error.

    Here is the setup:

    On General tab: Partial cache

    On Connections tab: A SQL Command to limit the columns to 4: Dimension key, business Key, Active Date, Inactive Date

    On the Columns tab: Mapped the three latter columns above to columns in the pipeline, and added the [Dimension Key] column to the output.

    On the Advanced tab: A SQL statement like this:

    SELECT <columns list from above>
    FROM <table>
    WHERE [Business Key] = ? 
    AND Date >= [Active Date]
    AND ( [Inactive Date] IS NULL OR
              Date <= [Inactive Date] ).                 

    I click the Parameters button and map the three (named Parameter0, Parameter1, and Parameter2) to appropriate input columns. All looks good. But when I run the package, I get an error saying that the Parameter name is not recognized. I cannot re-name the Parameter0 values, and if I have anything other than a ? in the SQL statement, it bombs out. What's going on?


    Todd C - MSCTS SQL Server 2005, MCITP BI Dev 2008 Please mark posts as answered where appropriate.



    • Edited by Todd C Thursday, July 18, 2013 5:51 PM
    Thursday, July 18, 2013 5:49 PM

Answers

  • Thanks for all the suggestions, but I think I'll just go back to handling the batch in a stored procedure like I did before.

    Also, since not all matches can be made on the Business Key, there is additional logic that needs to be implemented, which would make a huge spaghetti mess of Conditional Splits and Unions on a Control Flow.

    Thanks for your time.


    Todd C - MSCTS SQL Server 2005, MCITP BI Dev 2008 Please mark posts as answered where appropriate.

    Thursday, July 18, 2013 8:32 PM

All replies

  • You say you mapped three parameters in the advanced tab, but I see only one question mark in your query.

    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Thursday, July 18, 2013 8:03 PM
  • Sorry, The query should read:

    SELECT <columns list from above>
    FROM <table>
    WHERE [Business Key] = ? 
    AND Date >= ?
    AND Date <= ?

    Three Question Marks, three parameter. I even duplicated the Date column in the pipeline so I would have the extra column to map to, but that doesn't seem to matter.


    Todd C - MSCTS SQL Server 2005, MCITP BI Dev 2008 Please mark posts as answered where appropriate.

    Thursday, July 18, 2013 8:06 PM
  • What did you fill in for Input Column? I guess the names of the three columns? (case sensitive I guess, so watch out)

    What are the datatypes of the three columns?


    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Thursday, July 18, 2013 8:17 PM
  • My Dimension Key column (the column I wish to add to the pipeline) is an integer. The Date columns are actually integers as well, in the format of YYYYMMDD.

    The only issue I could see is that the Business Key in the pipeline is NVARCHAR(100) and in the lookup table is NVARCHAR(50). Think that could be the issue?


    Todd C - MSCTS SQL Server 2005, MCITP BI Dev 2008 Please mark posts as answered where appropriate.

    Thursday, July 18, 2013 8:22 PM
  • With the lookup component you never know. It is stumbles easily over different data types, so you might want to thow in a cast to make sure data types match.

    Not 100% sure, but maybe the query expects single quotes around your business key. For example, if you paste the parameters into your query, you get something like this:

    SELECT columns
    FROM myTable
    WHERE [Business Key] = someValue
    AND Date >= 20130701
    AND Date <= 20130718

    This isn't of course correct SQL syntax for a string literal.

    You might want to try to put your SQL statement in a stored procedure with three parameters, it might work better. And maybe it even performs better as the compiled plan could be reused.


    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Thursday, July 18, 2013 8:29 PM
  • Thanks for all the suggestions, but I think I'll just go back to handling the batch in a stored procedure like I did before.

    Also, since not all matches can be made on the Business Key, there is additional logic that needs to be implemented, which would make a huge spaghetti mess of Conditional Splits and Unions on a Control Flow.

    Thanks for your time.


    Todd C - MSCTS SQL Server 2005, MCITP BI Dev 2008 Please mark posts as answered where appropriate.

    Thursday, July 18, 2013 8:32 PM
  • No problem Todd, hope it works out.

    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Thursday, July 18, 2013 8:33 PM