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 error with code 0x80070057

Hi there,

I'm currently designing an ETL process and I'm using lookup transformations.
In one of them, I encountered an 0x80070057 error which I cannot explain.
When I'm looking at the number of rows already processed, the number is not always the same when the error occurs. This is the first strange thing. A second strange thing is the explanations given by SSIS (log):

OnError,DWHAPP1,AWW\RS9906,ODSTran1_1_1_DFT1,{002D0747-8F3E-43EF-A0EA-FE925E668ECB},{BAF1A259-7A26-49ED-B4E5-4BB9BB0BF004},08/03/2006 13:01:15,08/03/2006 13:01:15,-1073450974,0x,
The ProcessInput method on component "ODSTran1_1_1_D1_LU2" (15452) failed with error code 0x80070057. 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.

I really don't get it :-(

To explain you a bit what I'm doing, I do a lookup to check if the codes used in the facts (transaction table) exist in the referential tables (dimensions). The lookup in which the problem appears is a simple select on a table.

If someone has an explanation or (better) a solution, shoot! :-)

Renaud

I reply to myself :-)

I've just tried by removing the transformation linked to the Lookup Error path and by making my transormations in another way and it seems to be ok now.

So definetely, there was a random error (because not always at the same moment. I.e. not always with the same data) linked to the Lookup Error path and the way the Lookup managed it.

Still strange to me because the error wasn't explicit at all and the solution is more a workaround than a real solution to a real problem.

Bug ? This is definetely an open question :-)

|||

When you hooked up the component to the lookup's error output did you configure the output to redirect rows on error?

Thanks,

Matt

|||

Matt David wrote:

When you hooked up the component to the lookup's error output did you configure the output to redirect rows on error?

Thanks,

Matt

Yes, I did so first of all but I had to change it to 'ignore failure' and used a workaround to avoid the random error.

The workaround works fine, so I won't waste my time on looking for the real explanation of it.

Thanks Matt :-)

Looking for ideas

Hi everyone

We've got currenlty around 500 dts 2000 in production.

In order to know in what ETL processes we have Oracle connections, or FTP tasks or whatever, we did a VB6 app using dtspkg.dll which load all the properties for each DTS into Sql server tables. So that, then you could see from a specific DTS how many connections, Sql Tasks it had and so on..

How to accomplish the same with SSIS? I know, doing the same but using .Net, of course, but is there any else approximation? I am little bit concerned when we will have hundreds of them up.

Maybe 2005 is offering this feature automatically, I don't know.

Thanks in advance for your time/advices/ideas,

Try this -

Download details: SQL Server 2005 Business Intelligence Metadata Samples Toolkit
(http://www.microsoft.com/downloads/details.aspx?FamilyID=11daa4d1-196d-4f2a-b18f-891579c364f4&DisplayLang=en)

Even if the tools are visualisation is not what you want, it will have populated some tables with details of packages and objects that you can probably use. And if that is still not good enough the source code included will give you a start in writing your own tool.