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!

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!

Lookup Transformation Fails on Oracle

Hi,

I am trying to use a lookup in a package and check for some conditions. On the advanced tab, I am trying to modify the condition from = to <=. But the same doesnt work when the target is on oracle, but the same works fine on SQL Server and DB2.

Any idea regarding the same?

Thanks,

Manish Singh

Can you execute the SQL query against the Oracle database using SQL*Plus or another similar query tool? It may be possible that Oracle does not support the specific syntax you're trying to use; since it's not included in your post it is difficult to say.

Can you post the modified SQL statement here?

|||

Yes, the query gets executed against oracle database after passing some defalt values. Below is the modified sql which I am changing on the advanced tab. The same works for SQL Server and DB2

select * from

(SELECT A_KEY, KEY_ID, SOURCE_ID, EFFECTIVE_FROM_DT, EFFECTIVE_TO_DT, EFF_START_DT, EFF_END_DT

FROM TABLEA) as refTable

where [refTable].[SOURCE_ID] = ? and [refTable].[KEY_ID] = ? and [refTable].[EFFECTIVE_FROM_DT] <= ? and [refTable].[EFFECTIVE_TO_DT] >= ? and [refTable].[EFF_START_DT] <= ? and [refTable].[EFF_END_DT] >= ?

The default condition generated is as follows:

select * from

(SELECT A_KEY, KEY_ID, SOURCE_ID, EFFECTIVE_FROM_DT, EFFECTIVE_TO_DT, EFF_START_DT, EFF_END_DT

FROM TABLEA) as refTable

where [refTable].[SOURCE_ID] = ? and [refTable].[KEY_ID] = ? and [refTable].[EFFECTIVE_FROM_DT] = ? and [refTable].[EFFECTIVE_TO_DT] = ? and [refTable].[EFF_START_DT] = ? and [refTable].[EFF_END_DT] = ?

|||Thanks for the additional information, although I'm not sure what to tell you. It looks fine to me, so I do not know why Oracle doesn't like it.

Lookup Transformation fails on DT_STR (3) string match

The Lookup Transformation fails to match this datatype when full caching is enabled. When partial caching is activated (Edit > Advanced, Enable Memory Restrictions > Enable Caching) the lookup works.

This appears to be a bug to me.

By chance is it comparing against a CHAR field as opposed to a VARCHAR field?|||Yes, the source field was a varcahr(3) and the lookup field was a char(3). However, I modified the lookup field to be a varchar(3) with the same results. One note: this field is currently storing a two-digit value (ISO Country code, e.g. US). That is what originally led me to change it to a varchar(3) field originally. I have trimmed and converted until I have run out of options.|||

Phil,

What is the optimal data type set up for matching strings, i.e char or varchar, trim both prior, etc?

|||

I think the behaivior you guys described is alrady documented:

from http://msdn2.microsoft.com/en-us/library/ms141821.aspx

Integration Services and SQL Server differ in the way they compare strings. If the Lookup transformation is configured to use full precaching, Integration Services does the lookup comparison in the cache; otherwise, the lookup operation uses a parameterized SQL statement and SQL Server does the lookup comparison. This means that the Lookup transformation may return a different number of matches from the same lookup table depending on the cache type.`

And from connect: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126618

Rafael Salas