Lookup with more possibilities?

How can I do a lookup which doens't directly link two columns but uses another statement?

I tried in advanced with:

Code Snippet

select * from
(select * from [dbo].[Employees]) as refTable
where [refTable].[EM_ID] = ? and [refTable].[EM_From] <= ? and
([refTable].[EM_Until] > ? or [refTable].[EM_Until] IS NULL)

and adding 2 parameters.

Error 1 Validation error. Fill Planning: Lookup [2376]: An input column with the lineage ID 1760, referenced in the ParameterMap custom property with the parameter on position number 1, cannot be found in the input columns collection. Package.dtsx 0 0

So I guess that's not the way to go. Any other way to tackle this?

Aren't you missing a ")" at the end? I have sucessfully tried using the advanced tab to input parameters into the lookup transform in the past....

Here is a decent article on this type of action: http://www.julian-kuiters.id.au/article.php/ssis-lookup-with-range

|||Nope no ) missing. I got 2 ( and 2 ) so that's all good. Strange, it should work, I'll play some more with it. Strange thing, it works when I don't do the advanced stuff so something must be wrong there.|||

Just thought of something:

Is this ok to do:

I have:

Code Snippet

Parameter0 EM_ID

Parameter1 PL_Date

Parameter2 PL_Date

Is it ok to use PL_Date twice?

|||

rept wrote:

Just thought of something:

Is this ok to do:

I have:

Code Snippet

Parameter0 EM_ID

Parameter1 PL_Date

Parameter2 PL_Date

Is it ok to use PL_Date twice?

Sure.|||

yes.

Notice that parameter 1 and 2 of the julian kuiters article are both "modifydate".

|||

Just curious, why are you doing the select * from (select * from table) as reftable ? Why not just select * from table as reftable?

|||

" Just curious, why are you doing the select * from (select * from table) as reftable ? Why not just select * from table as reftable?"

I have the same question. It looks like that SQL is more complex that it should be; and I know for sure that SSIS is not very good at finding the parameter in subqueries. Give it a try without using that in-line-view and see if that fix the problem.

|||

Thanks for all the replies!

I just extended what SSIS had by default (same as in Julian Kuiters article as well BTW). I replaced it now but no difference however.

|||

Finally figured it out.

You need to make sure that every parameter that you use in the query is also connected graphically in the columns tab! It doesn't matter if the relation you draw doesn't make sence, you need to for SSIS to be able to find the input column! Hope this will save someone a lot of time someday

Thanks for all who replied!