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

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 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 finds match on empty reference table

Hi all,

In BOL it says: "The Lookup transformation performs an equi-join between values in the transformation input and values in the reference dataset. Using an equi-join means that each row in the transformation input must match at least one row from the reference dataset. If there is no matching entry in the reference dataset, no join occurs and no values are returned from the reference dataset. This is an error, and the transformation fails, unless it is configured to ignore errors or redirect error rows to the error output. "

I have a lookup transformation which is supposed to find a match on two fields in the reference dataset (a table in my case) but strangely, when I execute my package and the reference table is empty the lookup still finds match for each row of my input dataset.

Does anyone have an idea why? I could'nt find anything about that in BOL.

Sbastien.

If the lookup is returning rows, yet now rows exists, it cannot be matching, the returned columns if any will be null. You can however allow this behaviour, by ignoring errors. A failed lookup match is classed as an error, and can be set under the Configure Error Outputs button/screen. See if you have set Ignore for errors.|||Indeed I have configured the error to "Ignore errors" but I thought it will simply avoid the package to fail if it couldn't find any match. I don't want to use Redirect rows either as I have nothing to do with the non matching rows.|||

Sebastian,

The solution here then is quite simple. Use a Conditional Split to filter out the columns that have NULL values in the lookup columns - thereby giving you the rows that yielded a match.

-Jamie

Lookup error

Hi,

I'm using a Lookup object, but it dosn't works. Anybody knows this error?

[Lookup [29018]] Error: Row yielded no match during lookup.

[Lookup [29018]] Error: The "component "Lookup" (29018)" failed because error code 0xC020901E occurred, and the error row disposition on "output "Lookup Output" (29020)" specifies failure on error. An error occurred on the specified object of the specified component.

[DTS.Pipeline] Error: The ProcessInput method on component "Lookup" (29018) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0209029.

It means exactly what it says. The pipeline value that you were looking up doesn't exist in your LOOKUP dataset.

If finding no match is a valid business scenario you can configure the component to ignore these errors. Edit the "Lookup Output" output which currently is set to "Fail component on error".

-Jamie

|||Thanks!