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!

Looking for some general feedback on working with SQL, SSIS and SAP

This is less of a specific question and more of a request for for some advice as to possibilities and directions. Here's the current situation. My company is using SAP for its purchasing, inventory, etc. This system is pretty much opaque to me - it's managed by another group within the company, and changes to it go through a complicated approval process. At the same time, the majority of our users, internal and external, are looking at this same data through a more accesible and more user friendly collection of web applications - done in classic ASP, up through ASP.NET 1.1 and 2 - and stored in an assortment of MS-SQL 2000 databases. Data is exchanged between SQL and SAP via DTS packages, some nightly, some run more frequently.

There's some issues here - data is never quite synchronized between the two sides, sometimes the same data must be updated twice, leading to possible data integrity issues, etc. Given that, we're going to be moving to SQL 2005 within the next year or so. From everything I've understood, within that context, there are vastly better ways of dealing with out situation than the way we're currently doing it.

So what I'm looking for is just a general impression of what can be done, with SSIS and SAP. Any approaches that might prove more fruitful, an y pitfalls to watch out for, that sort of thing.

How are you pulling data from SAP to SQL using DTS?

With SQL 2005, we have a preview version of the SAP .NET Data Provider for use within SSIS. You can check it out http://msdn2.microsoft.com/en-us/library/ms141761(SQL.90).aspx

SSIS will also be supported by the upcoming Biztalk R2 Adapter pack - which has adapters for SAP, Oracle & Siebel.

Go to https://connect.microsoft.com/ and look for this adapter pack - you can try out Beta2 around end-July.

|||

Currently we're not exactly pulling data. We're exporting data from SAP to a collection of flat files. Then we have a number of DTS packages that run - most nightly, one every 4 hours - and import the data into our database. Which seems damn clumsy.

Thanks for pointing me towards the .NET Data Provider, which I had a vague notion of, and to the Biztalk beta, which is something I hadn't heard of. I'll have plenty of things to research.