CODEWITHSUNDEEP
ssis
Hugh Mullally
Hugh Mullally

Reputation: 736

SSIS - Can you use a parameterised query to populate a Lookup Transformation?

I am using a stored procedure to populate a Lookup Transformation, but would like to restrict the size of the lookup cache.

I could do this by supplying a parameter to the stored procedure, but I can't see any way of parameterising the query on the Lookup Transformation component.

Upvotes: 2

Views: 12934

Answers (3)

questionto42
questionto42

Reputation: 9532

These are screenshots for the other 2012 answer.

enter image description here

Join the columns under the "Columns" tab.

If you then go to "Advanced", you will see that you already have an automatic parametrization. Your whole code will be embedded in

select * from ([whole code]) [refTable]
where [refTable].[id] = ?

enter image description here

You can change this setting, you do not need to embed the whole code in this new select * from (...) [refTable]. Often, you want to parametrize to filter the query on the run so that less data needs to be loaded, therefore, this embedment will often not be the needed code.

For example

[whole code of the lookup table]:

select A.a, B.b from A join b on A.id = B.id

The filter could be:

where A.[id] = ?

The ? is the parameter that you get from the main table.

Click on the "Parameters..." button and choose the right input parameter. You can only choose those that are already linked from main table to lookup table.

enter image description here

Upvotes: 0

Valentino Vranken
Valentino Vranken

Reputation: 5815

You can use the Advanced page of the Lookup Transformation properties to modify the SQL statement. That allows the use of parameters. But you'll need to change the Cache Mode to either Partial or No Cache because otherwise the statement cannot be changed.

Upvotes: 3

John Saunders
John Saunders

Reputation: 161773

If you are using SQL Server 2008, then you can use the new Cache Transformation. It can be populated from an OLE DB Source (which you can, of course, parameterize), and can either keep its rows in memory for use by one or more Lookup transformations, or can save the cached rows in a file, where they can be used by several different Lookup transformations, even in different packages.

See How to: Implement a Lookup Transformation in Full Cache Mode (SQL Server Video). It's only nine minutes, but shows the essentials.

Upvotes: 3

Related Questions