SSIS LookUp Transformation Advanced Editor(Modify

问题:

I have a doubt in the Look-up Transformation in SSIS, What is the use of Check Box(Modify the SQL statement) and the Parameters option.

Thanks in Advance. Zeelan.

---

Thanks for your answer Gowdhaman.

I have a scenario like my look-up(Reference) table is hitting DB2 and in DB2 we have Multiple Schema, my requirement is passing schema name as Parameter to a Look-up Transformation, below is the Query in the Look-up Transformation

select * from **Schema**.TableName

I need to pass Schema Name as Parameter in the Look-up, let me know that can we achieve this in Look-up Transformation.

Thanks in Advance, Zeelan.

回答1:

You can use the expression to prepare the SQL statement query at data flow level. Click on the dataflow task. Go to the expression, you can prepare the query using the schema using the expression editor. Following image shows you how dataflow expression will be shown.

Hope this helps.

回答2:

We can modify the statement for comparison. For example, you have date fields in your source file and wanted to take a column by comparing the startdate and enddate in the lookup table. Following query illustrate the functionality.

select * from (select * from [dbo].[DimTime]) [refTable]
where [refTable].[StartDate] <= ?
and [refTable].[EndDate] >= ?

The above statement compares the parameter with startdate and enddate. If you want to check your source parameter with some advanced conditions in your lookup table, you can use this options.

Hope this helps