Lookup transformation problem

Hello all,

I needed to lookup some table values based on a join of two fields...

I've configured the lookup transform to get the values via a SQL statement to minimize loading time.

However, when creating the relationships between the input columns and the lookup columns I receive following error:
input column [BATCH_ID] has a datatype which cannot be joined on

I've checked both input and lookup columns, both are of type DT_R8... Both columns in the different tables do have the same datatypes

Any idea how to solve this problem?

Thanks in advance

DT_R8 is a floating point data type, it is an approximate value which means it cannot be joined on. Try a decimal data type instead.|||

Hello,

Thanks for your reply but how can I change datatypes fetched directly from a SQL statement in the lookup query?

|||

lookup supplied many functions can cast datatype

i think you also can use convert or cast in sql statements

|||

Hello,

I've used Dataconversion and SQL functions to perform the requested conversions...

works fine however when executing the package it fails when loading the cache of the lookup components...
this is the message I get from the progress window:

[GET HOLDING_ID [2998]] Error: An OLE DB error has occurred. Error code: 0x80040E07. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E07 Description: "ORA-01722: invalid number ".

and

next line:

[GET HOLDING_ID [2998]] Error: OLE DB error occurred while populating internal cache. Check SQLCommand and SqlCommandParam properties.

However, I never changed the SQL command of the transform in the advanced tab...

Any Idea?

Thanks in advance

LookUP Transform...

Dear friends,

I have a ETL that have a Lookup transform to get a rate from a table SpotRates.

The problem is when the match od some date in SpotRates Table doens't exist...

And for that records I need to lookup for next date...

For example...

SpotRate Table

Date Currency Rate 05-04-2006 0:00 DOLAR ESTADOS UNIDOS 1,2262 06-04-2006 0:00 DOLAR ESTADOS UNIDOS 1,2312 07-04-2006 0:00 DOLAR ESTADOS UNIDOS 1,2179 10-04-2006 0:00 DOLAR ESTADOS UNIDOS 1,2099 11-04-2006 0:00 DOLAR ESTADOS UNIDOS 1,2105 12-04-2006 0:00 DOLAR ESTADOS UNIDOS 1,2125 13-04-2006 0:00 DOLAR ESTADOS UNIDOS 1,2094 18-04-2006 0:00 DOLAR ESTADOS UNIDOS 1,2252 19-04-2006 0:00 DOLAR ESTADOS UNIDOS 1,2346 20-04-2006 0:00 DOLAR ESTADOS UNIDOS 1,2346 21-04-2006 0:00 DOLAR ESTADOS UNIDOS 1,2315 24-04-2006 0:00 DOLAR ESTADOS UNIDOS 1,2365 25-04-2006 0:00 DOLAR ESTADOS UNIDOS 1,2425

When I first try to lookup the date 17-04-2006, doesnt give me any records... and I need to create a new lookup for the next date from 17-04-2006. And in this example the next date is 18-04-2006.. How can I do it?

I made a sql query date gives me the next date with 2 parameters ... but I'm having some errors...

SELECT TOP 1 Data
FROM Spot_Rates
WHERE (Currencies_Name = ?) AND (Data > CONVERT(DATETIME, ?, 102))
ORDER BY Data DESC

In this exampple, the parameters returned from lookup1 is:

Currencies_name= 'DOLAR ESTADOS UNIDOS'

DATE='17-04-2006'

I need to create a second lookup transform to return the next date/currency for each row that didnt match in the first lookup...

Regards,

Pedro

What are your errors?

I also think you'd want "Data >= CONVERT(DATETIME......)"|||

I have tried this way...

In lookUp transformation in 1oTab "Reference Table" I inserted SQL query:

SELECT Data, RevalRate, Currencies_Name
FROM Spot_Rates

In 2oTab "Columns" I'm inserted the relations between input/output and check the field that I want for output.

In 3oTab "Advanced" I'm inserted :

select TOP 1 * from
(SELECT Data, RevalRate, Currencies_Name
FROM Spot_Rates) as refTable
WHERE ([refTable].[Currencies_Name] = ?) AND ( [refTable].[Data] > CONVERT(DATETIME, ?, 102))
ORDER BY Data DESC

But at this moment the package are spending a lot of time to finalize... I still waiting... :-(

Regards,

pedro

|||

And it's not being return the correct revalrate for each row with Currencies_Name/Date...

It's returning the last date for this currency and not for input parameter date for each row... :-(

Regards

|||Do you have indexes at all on the Spot_Rates table? How large is the Spot_Rates table?|||

USE [dbRentabilidade]

GO

/****** Object: Table [dbo].[Spot_Rates] Script Date: 04/16/2007 15:36:35 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Spot_Rates](

[Currencies_ShortName] [nvarchar](3) NULL,

[Currencies_Name] [varchar](32) NULL,

[Data] [datetime] NULL,

[RevalRate] [float] NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

About 55.000 rows...

|||

Pedro,

I would like to make some points:

The query needs to have an OR clause; so it retrives the exact match or the nearest greater one. If you are going to use TOP 1; the query must guarentee that resultset is ordered ASC If you use a Lookup transform the way you are using it (query w/parameters) it will work using partial cache; which menas it will run the query for each row passing through. That is way it is so slow. How many rows are passing through? If the Spot_dates table and the rows passing through the pipeline are in the same database; you are better of creating a database function to retrive the right RevalRate. That way the DB engine will carry the overhead.|||

In spite of looup transform, i'm trying to use OLE DB Command... executing the following SQL Stored Procedure:

USE [dbRentabilidade]

GO

/****** Object: StoredProcedure [dbo].[R_SP_GET_NextDayRate] Script Date: 04/16/2007 18:17:33 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[R_SP_GET_NextDayRate]

@.MyCurrency varchar(32),

@.MyDate datetime

AS

SELECT TOP 1 Data AS TESTE

FROM Spot_Rates

WHERE (Currencies_Name = @.MyCurrency) AND (Data > CONVERT(DATETIME, @.MyDate, 102))

ORDER BY Data ASC

RETURN

But the problem is that I dont know how to read the values returned from SP in order to continue the dataflow with this values... IT'S Possible?!

|||It looks like you cannot get columns back to the dataflow pipe line via oledb command. But here is the thing, even if you find the way; the performance will be about the same than the one you get using the Lookup component. That is why I was asking if you could include the join or function right on your source component.|||

And what you think about insert the non matched values into temp table, and use a lookup to find the rate in this temp table to continue the dataflow?

what you think?

|||

I have created a OLEDB Command to insert values that didnt match the lookup in a table, and have a loopup to match the values in thsi table...

The problem now, is that in the dataflow, the values inserted by OLE DB Command are not refreshed in the dataflow in the moment that I do a new match in the lookup transformation to this table...

...

7. LookUp

7.1. MutiCast

7.2. OLE DB Command

7.2.1 LookUp

And the values inserted in OLE DB Command are in the table database, but not in the dataflow in the moment that data is passing throut lookup transformation....

Could someone help me?

Thanks

|||I am getting lost with your approach; but in general you could break the process in 2 data flows; the first one to insert the no matches into the temp table; the second to do the lookup. does this make sense?|||

PedroCGD wrote:

I have created a OLEDB Command to insert values that didnt match the lookup in a table, and have a loopup to match the values in thsi table...

The problem now, is that in the dataflow, the values inserted by OLE DB Command are not refreshed in the dataflow in the moment that I do a new match in the lookup transformation to this table...

...

7. LookUp

7.1. MutiCast

7.2. OLE DB Command

7.2.1 LookUp

And the values inserted in OLE DB Command are in the table database, but not in the dataflow in the moment that data is passing throut lookup transformation....

Could someone help me?

Thanks

Enable memory restriction in your second lookup (on the advanced tab). This will slow down the data flow, but it's the only way you will see the new data in the lookup.

I've been watching your threads on this over the last day. Have you considered just populating your rate table with data for each day? Even if you use a seperate data flow to create a "temporary" working table with all the dates filled in, it would still make your process much simpler to implement and maintain.

|||

Make sense and I already tried it before... but when I do the lookup the data not exist yet... :-(

Regards,

Pedro

|||

jwelch,

But we are talking about millions of rows...

I'm trying to include one more lookup... but I dont know if it will work,... i give you feedback soon!

Thanks!

Lookup Transform with Variable Parameter

Is it possible to use a VARIABLE in the Lookup Transform? I am setting the cache mode to partial and have modified the caching SQL statement on the advanced tab to include the parameterized query, but the parameter button only allows me to select columns to map to the parameter. I need to use a variable instead. I see the ParameterMap property of the transform in the advanced editor, but don't see how I can use this to map to a variable.

Can this be done, or do I need to use a new source, sort and left join component to accomplish the same thing?

Thanks!

Brandon

Brandon I don't believe this can be done with the Lookup Transform, as I have ran into this limitation before.
Adrian
|||

The way I did it was to use a derived column transform before the lookup transform that "transform" my variable in a column. By doing this, I can now see the new column in the input column of the "set query parametsrs" parameter window.

Ccote

Lookup transform with multiple matches

Please indulge my ignorance, as I have only been using SSIS for a couple of weeks.
I'm trying to create a data warehouse using two input tables.
A column needs to be added to one table by using a lookup into the second table.
SSIS seems to handle the "no matches" and "single match" cases perfectly.
I can't for the life of me figure out how to properly handle multiple matches.
SSIS defaults to the first match, but I need to compute the "best" match.

Many thanks in advance
Scott!

What's the criteria for the "best" match? Knowing the criteria would make it easier to help |||Here are some specifics:
Table A contains repair records.
Table B contains inspection records.
Each piece of equipment can have multiple repairs and many more inspections.
A lookup can be performed based on the equipment number.

I need to determine which repair immediately proceeds each inspection.
This can be found by computing the smallest positive date difference.

Programmactically, I would normally do this with nested loops.
Using SSIS, I suppose it could also be done this way, using a foreach loop
and a condition...
|||

Interesting problem

I haven't tested this, but could you join the rows in SQL on the equipment, calc the date difference and order by it, and use a rank function to get the first row?

Lookup transform not finding blank match

I am having problems with a lookup transformation. I have a row in my lookup table for blank ('') source data. If I test the join using SQL the match is made, but the Lookup transform doesn't consider it a match and sends it to error output. Is there a property that I don't have set correctly or something else I am forgetting?

Check that you are not using empty string lookup against spaced "empty" strings or vice versa.

|||

I created the lookup table (dimension) and prepared the source table so I do know they are both empty strings.

Let me give a little more information. I have a package that runs prior to the fact table load (lookups). The package runs through the source and adds any dimension records that are not present. The problem arises when the lookup encounters an empty string from the source and it is unable to match on the empty string record already in the dim table, so it sends the record to the error output to be written to the dim table. But the write fails because the field is a primary key in the dim table and because the record exists it raises an error. In other words the Lookup transform can't see the empty string record, but the Write Transform can.

I hope that this explanation is understandable.

Dave

|||

Dave,

I've noticed when I pull back a string from tables, the string is padded with spaces to the full size of the field.

Try two things to see if one of these might be your problem:

1) After you pull in your columns from the source, add a derived column that concatenates a single character (like 'a') before and after the column that is return the empty string. Use a data viewer after this column, to see if the column looks like this: 'aa' or this 'a a'.

If it's the latter, change your derived column to trim the string, or trim the column when pulling it from the source.

2) In your initial lookup where you are trying to see if the empty string ('') exists, try using a SQL statement that selects the columns you are looking up. In your SQL statement, concatenate a single character in the same manner as step 1 to the empty string column and add it to your output. Then use a data viewer after this column to see what the column looks like.

If it's 'a a', trim your column in your SQL statement before returning it.

Good luck!

Jessica

|||

Like Jessica said, use the dataviewer to check the data.

SSIS is using .net string compare on strings during lookup process, which think '' and ' ' is different, but sql is ignoring trailing empty spaces during select joins.

If your oledb command insert statement check if the data exist before insert, then you may always insert ' ' into the database but always ignore the ''.

|||

Check out

http://blogs.conchango.com/kristianwedberg/archive/2006/02/22/2955.aspx

especially the comments at the end - you're not alone :-)

Cheers/Kristian

Lookup Transform error when linking using a DT_R8

I'm trying to lookup a value in another table linking on a column of datatype DT_R8. The lookup transform is complaining that I can't link on that datatype. However, the documentation says that it should work. I'm using the April CTP. Is this fixed in a later version? Any suggestions?

I'm sure DT_R8 does work in a Lookup. You may get an error such as -

[DDD, BackOfficeSoftwareKey]
One or more columns do not have supported data types, or their data types do not match.

This means that the source data type and the reference data type do not match. All lookups, the mappings between source and reference columns, must compare data types that match exactly, so you cannot compare a DT_I4 with a DT_R8 for example. One of them needs to be converted to the same type as the other before the match. You may be able to do this in the source extraction, or in the lookup reference table specification, use a SQL query with a CAST, or finally use a Data Conversion Tramsform or Derived Column.

|||I'm pretty sure that's not the problem becuase I've tried that approach. I've also double-checked the data types in the advanced editor and the datatypes do match. They are both DT_R8. It's not the usual error message that you mentioned. It's a different one that says "input column <column_name> has a datatype which cannot be joined on."|||DT_R8 is not allowed as a join column. So are DT_R4 and BLOBs (DT_TEXT, DT_NTEXT, DT_IMAGE).

Lookup transform - incompatible data types mistery

I have a package that works fine in development. I move the package over to test and it fails validation in the lookup transform.

Error 46 Validation error. Data Flow Task - PO Lines Interface: Lookup - LIST PRICE [29621]: output column "LIST_PRICE_PER_UNIT" (29667) and reference column named "LIST_PRICE_PER_UNIT" have incompatible data types. SPO_TO_ORACLE_PO.dtsx 0 0

What strikes me as odd is the fact that I don't have a way of specifying the data types. I just specify the column I wish to return as a new column with the same name. Anyway, why would this work in one instance but not another?

thanks

John

Perhaps the data type on the lookup column is different in the new environment.|||

Check the regional settings of the development machine and que production machine!

Regards!

|||The tables are identical on both machines but I have noticed some subtle differences in the actual data values contained within the tables. I'm going to take a closer look at that. In one table the column has "0" values but in the other they are defined as "0.0000000". I'm actuall surprised that package validation would catch these kinds of differences.|||

Yeah!! :-)

Let we know if you resolved your problem!

Regards!!

Lookup Transform

I want to do something relatively simple with SSIS but can't find an easy way to do this (isint it always the case with SSIS )

I have a column lets say called iorg_id, and I want to lookup the matching rows for this col in a table.

In this table iorg_id may have several potential matching rows. In this table there is another col called 'Amount'.

I want to retrieve for each iorg_id the matching iorg_id in the other table but only the row with the largest value in the 'Amount' col.

I couldn't find a way to do this all in the Lookup Transform.

I can match the iorg_ids and retrieve the Amount column, but can't find a way just to retrieve the matching row with the largest value in the Amount col. The only way I can think to do this is then run the output from the Transform through an Aggregate function and determine the Max (although haven't tested this yet).

Seems strange to me in that the SQL in the Advanced tab gives me something like:

select * from
(select * from [dbo].[Table1]) as refTable
where [refTable].[iorg_id] = ?

where I believe the first 'select *' is retrieving all the cols that are listed in the LookupColumns list in the Columns tab.

I thought I would be able to amend this to something like:

select max(amount) from
(select * from [dbo].[Table1]) as refTable
where [refTable].[iorg_id] = ?

but I get a metadata type error.

So, questions are:

Is it possible to do this all in the Lookup Transform are do I have to use the Aggregate function as I think ?

Why is it not possible to amend the sql in the Advanced tab to manipulate the returned data ?

If I understand the problem correctly, couldn't you simply use (in the Lookup) some SQL similar to: -

SELECT iorg_id
, MAX(amounT)
FROM <YourTable>
GROUP BY iorg_id

A match on iorg_id should then give you what you want

|||

Yes, Paul you're right. Thanks.

My colleague had actually pointed that out to me in the interim.

Sometimes it's difficult to see to the wood for the trees !

Lookup Transform

Hi!
I am a newbie, grateful for some help. I have a Source Ole DB w sql-command selecting the customer.salary and customer.occupation, which I want to match with demo_id in Ole DB destination.
salary, occupation also in dim_demographic.
But in Lookup editor I find no column demo_id... how do I do this?The lookup must select against your table with demo_id in it... We need more information here... If you can't find demo_id in your lookup table, and you need it to be there, well then you probably have the wrong table defined in your lookup.|||

Not sure what your problem is. In general, Lookup transform can be basedon a table or on a query; if you don't see an specifc column in the Column is because that column is not on that table/query.

Could you provide more details on how you have set up your package.

BTW, Have you considered to just include the lookup table as a part of the OLE DB Source query and not use the lookup transform at all?

|||Ihave a select customer.salary, customer.occupation in source DB. These should be Lookup columns matched with the id from dim_demography. How?|||The source has nothing to do with the lookup.

In the lookup, you need to specify a lookup table or query. When that's done, then you map columns from the dataflow (your source) to columns in the lookup table. You'll have to have an ID column coming from the source query though.

I'm sorry, but your response didn't say anything more clear than your original post.|||How would you do if you had a demo_id in db Destination, which should be matched with the values salary and occupation in Dim_demographic (containing columns id, salary, occupation) or de db Source sql customer.salary, customer.occupation?|||Please provide your schema:

Table1:
Column1
Column2
....

Table2:
Column1
Column2
...

We'll go from there.|||OLE db Source column:
SELECT Sum, k.sum-p.price AS Profit, convert (char(8),date,112) as Date_id, salary, occupation, k.shop_id, k.customer_no, salesperson_id, p.articlenr,campain_id
FROM Purchase k join product p on k.articlenr=p.articlenr join customer ku on k.customernr=ku.customernr

Destination column:
demo_id, Profit, customer_id, product_id, shop_id, date_id, sum, salesperson_id, campain_id

Demo_id = id from Dim_demographic.
Dim_demographic columns are id, salary, occupation

How do I get the Dim_demographic_id to match demo_id? by comparing salary and occupation from Source column with Dim_demographic salary, occupation.. may be in an sql in Lookup?
What should be used in Lookup as connection table? or SQL Select demographic.id where demographic.salary= fact_purchase.salary...
|||

curiousss wrote:

OLE db Source column:
SELECT Sum, k.sum-p.price AS Profit, convert (char(8),date,112) as Date_id, salary, occupation, k.shop_id, k.customer_no, salesperson_id, p.articlenr,campain_id
FROM Purchase k join product p on k.articlenr=p.articlenr join customer ku on k.customernr=ku.customernr
Destination column:
demo_id, Profit, customer_id, product_id, shop_id, date_id, sum, salesperson_id, campain_id
Demo_id = id from Dim_demographic.
Dim_demographic columns are id, salary, occupation

How do I get the Dim_demographic_id to match demo_id? by comparing salary and occupation from Source column with Dim_demographic salary, occupation.. may be in an sql in Lookup?
What should be used in Lookup as connection table? or SQL Select demographic.id where demographic.salary= fact_purchase.salary...

Use a lookup transform with a query like:

Take the output of the OLE DB Source component to a Lookup transform. Inside of the lookup write a query like:

Select dim_demographic_id, salary, occupation
From Dim_demographic

Then in the columns tab draw a line to join Salary and occupation; in the bottom part, choose id from the dropdown list to bring the dim_demographic_id (from dim_demographic) to the pipe line. Now when you connect the pipeline to the destination component you should have that extra column; all you have to do is to create the mapping between thedestination column demo_id and the column in the pipeline.

BTW, it is better to provide a query inside the lookup tranform than select the tablename from the list...it save resources(memory) improvng performance.

|||Thank you!
Now lines bw Available lookup (salary, occupation) and matching Available Input columns. Available Lookup id marked, and on first line in Lookup column: id, Lookup operation: <add new column>

But OLE db destination Preview is empty. although Destination column: demo_id is matched with Input column: id

Execution error:
[Lookup [3882]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.

[OLE DB Destination [3924]] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.".

[OLE DB Destination [3924]] Error: There was an error with input column "salesperson_id" (3972) on input "OLE DB Destination Input" (3937). The column status returned was: "Conversion failed because the data value overflowed the specified type.".|||The OLE DB destination preview shows you what's in the destination table, not what's in the data flow. You have nothing in your table, hence nothing shows up in the preview.|||

curiousss wrote:

Thank you!
Now lines bw Available lookup (salary, occupation) and matching Available Input columns. Available Lookup id marked, and on first line in Lookup column: id, Lookup operation: <add new column>

But OLE db destination Preview is empty. although Destination column: demo_id is matched with Input column: id

Execution error:
[Lookup [3882]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.

[OLE DB Destination [3924]] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.".

[OLE DB Destination [3924]] Error: There was an error with input column "salesperson_id" (3972) on input "OLE DB Destination Input" (3937). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

Ok Few things here:

The preview in the OLE DB Components shows data that is already in that table; if this is your initial load, the table is empty, then the preview will show nothing.

The first message you in the execution error is warning you about duplicates in the Dim_Demographics; meaning, there is more than one row for a combination of salary and occupation. This is treated as a warning as SSIS will use any of those values; so be careful on that. Provide a query and joins that retrieves unique values.

The other 2 messages are actually errors; check the data types of the source and destination for salesperson_is column; they have to be the same.

|||Hi!
Thank you.. its getting late...wouldn't this eliminate duplicates?
SELECT DISTINCT id, salary, occupation
From Dim_demographic|||by the way.. is there any logic/rule/order when changing data types (just shows error now)

Purchase Source both salesperson_id are DT_18

Purchase Destination salesperson_id Input DT_18
Purchase Destination salesperson_id external DT_14

...Dim_salesperson salesperson_id is DT-14|||

I don't think the Distinct will eliminate the duplicates as each row (I guess) has a unique Id value.

To convert data types there is a Data conversion tranform in the toolbox of the data flow.

Lookup cachetype = none question

HI, I use a lookup transform on one of my dataflow. My data look a bit like this (the actual data is more complicated, kit is a combination of type 1 - description and type 2 - code):

ID Code Description
-- -
1 AAA PRODUCT1
2 AAA PRODUCT2
3 AAA PRODUCT3
4 BBB PRODUCT4

The problem is simple: I would like to insert ID 1 and update it with subsequent rows that have the same code.

If the lookup transform finds a match, the row is updated, else, the row is inserted (using the error path of the lookup) via an OLE_DB command. The lookup cachetype is set to none. My problem is all rows are inserted. But if I use a second lookup that gives me the ID using the Code column, the second lookup sees the inserted data.

My question is why the second lookup is able to find out the inserted data while the first one cannot? The SCD wizard cannot resolve this either. I resolved this by using an asynchronous script component and manage the logic in there. But, still, a lookup with no cache (roud trip to the DB every time) should be able to do the job.

Thank you,
Ccote

Generally this is because the dataflow works on buffers not rows. All the rows in a buffer are processed by a component before the rows are passed on to the next component. So the 1st lookup doesn't find the data because it most likely hasn't actually been inserted yet, while the 2nd one finds it because the data has actually been inserted.

HTH,

Matt