Lookup Transformation with parameter

Has anyone else noticed this? I want to be able to use a paremter in my reference table of my Lookup Transformation. I couldn't find any way for the dialog to accept SQL with a parameter so I checked on MSDN How to: Implement a Lookup Using the Lookup Transformation and sure enough in the article is says to click on the Parameter button. I don't have a Parameters button on this dialog. Error? Is this possible?

6. In the Lookup Transformation Editor, on the Reference Table tab, select a connection manager in the Connection manager list, and then do one of the following:

Click Use a table or a view, and then select either a data source view, a data source reference, or an OLE DB connection manager.

Click Use results of an SQL query, and then build a query in the SQL Command window, or click Build Query to build a query using the graphical tools that the Query Builder provides. Alternatively, click Browse to import an SQL statement from a file.
If the query includes parameters, click Parameters to map parameters to variables. For more information, see How to: Map Query Parameters to Variables in Data Flow Components.
To validate the SQL query, click Parse Query.
To view a sample of the data that the query returns, click Preview.

Lookup supports Parameters in partial cache mode. Pls go to the "Advanced" page in Lookup custom UI, enable memory restriction and enable caching, choose to modify Sql command so as to enable the "Parameter" button. See

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/f3395c65-0320-47f9-8d83-daaa082d8713.htm

Thanks

Wenyang

|||

Unfortunately, we haven't got to support parameters in the reference query on the Reference page. As Wenyang said you can use them in the query used for caching on the Advanced page.

Thanks.

|||

The problem I'm running into is that I want to be able to restrict the lookup based on a field in the reference table. When I add the SQL parameter in the Advanced page I get a validation error regarding the parameter in the ParameterMap not being in the availaible input columns. The parameter can't be in the input columns since it is only in the reference table.

I don't know if there is going to be a way around this so I am going to look into alternative ways to acomplish what I need.

Thanks for the feedback.

|||

Hi Infrandom

Is this still a problem for you or have you solved it? If not, let me know and I will post the solution.

Jays :-)

|||

Hi Jays,

I was able to get around my particular issue by changing my dataflow, however, if you've actaully found a way to add a parameter to the SQL Query of the reference table I'd be interested in learning how.

|||

Hi Infrandom

Sorry for the delay, I can't seem to get email alerts working with the MSDN forums, so I had to keep checking back for your answer.

I used parameterised lookups to find surrogate keys for facts during a historical load. That is, find the correct surrogate key where:

[Natural_Key] = [Dimension].[Natural_Key] AND [DateTimeStamp] >= [Dimension].[Start_DateTime] AND [DateTimeStamp] < [Dimension].[End_DateTime]

To achieve this you must perform the following steps:

1. Enter the query to return the reference table as per normal.

2. In the paramter mapping, link DateTimeStamp to either Start_DateTime or End_DateTime as if you were trying to do a standard 'equality lookup'.

3. Go into the advanced section and change the SQL there to look as follows:

select * from
(select * from [dbo].[Lookup_Table]) as refTable
where [refTable].[Natural_Key] = ? and [refTable].[Start_DateTime] <= ? AND [refTable].[End_DateTime] > ?

4. Hit the parameters button and map the parameters as follows:

{Param0 = Natural_Key, Param1 = DateTimeStamp, Param2 = DateTimeStamp}

And thats it! :-)

Just a couple of notes:

* If you don't create the dummy link described in step 2, the lookup will complain with some nonsense error.

* Before doing step 3, you will have to enable memory restrictions, etc.. before SSIS will allow you to enter the SQL statement.

I hope this helps, any Qs please feel free to ask.

Jays :-)