Loop Error

I have the following code in my selection criteria (using CR 10):

Global datetimevar firstDate:= datetime(year(CurrentDateTime), month(CurrentDateTime),1,0,0,0) ;
Global datetimevar lastMonth:= dateadd("m",+0,firstDate);
lastMonth:=dateadd("d",-1, lastMonth);

if {ENG_ORD_MERGE_VW.SHP_DT} = lastMonth
then
Do
lastMonth:=dateadd("d",-1, lastMonth)
While {CTDATE.WRK_DAY} <> 1.00 ;

The report appears to be searching records but then part way through it kicks me out with this error message:

"A loop was evaluated more than the maximum number of times allowed"

What is the maximum number of times a loop can be evaluated? I didn't think there was a limit. Does anyone know how I can write this so that it won't kick me out?You need to increase or decrese {CTDATE.WRK_DAY} in the loop
Otherwise it will result in infinite loop|||I can't increment the WorkDay because all workdays need to equal 1, will it work if I put in a dummy counter, just to increment 1?

Lookups and their error flows

So I have three lookups in a row in my data flow. Basically they are doing data quality checks for me using a reference table.

I want to be able to take the error flows of the three lookups and merge them together (union all) so that I can insert the "errors" (or non matches) into a table.

Can't do it. Because SSIS deems non-matches as "errors" you automatically get the errorCode and errorColumn fields. When you try to union a lookup error output with another lookup's error output, you can't do it.

What I would like to see is a lookup act more like a conditional statment where you have three outputs of a lookup table: match found, no match found, and error. Either that, or I'd like to be able to edit the names of the errorCode and errorColumn fields.

Am I missing something here, or do I need to just add an OLE destination for each lookup error flow when I only want one? 'Course the problem then is that I want to count the number of rows that are in "error" across all of the lookups.

Phil Brammer wrote:

Can't do it. Because SSIS deems non-matches as "errors" you automatically get the errorCode and errorColumn fields. When you try to union a lookup error output with another lookup's error output, you can't do it.

Phil,

What do you mean with 'Can't do it'. Are you actually receiving an error?

I was able to set that up; two error outputs from lookup transformations going to an Union All; it seems to work; but that was with a simple test scenario I made. Yes, ErrorColumn and ErrorCode were there as well but is up to you to use them or remove them from the pipeline. What is actually no there is the lookup value but I would not expect to be since the lookup fail.

Another way to accomplish something like this is to configure lookups to 'ignore errors' and then at the end to use a conditional split to send the rows having nulls on the lookup values column to the error branch of the dataflow. Just a thought

Rafael Salas

|||User error... You made me think about it again, and when I looked at what I was trying to do, I realized my mistake. Never-the-less, what I was doing was taking the error "redirect" from the lookup and adding a new column with a derived column transformation. This was repeated on the other lookup error flows. Then my mistake (an accident!) was taking the *error* output of the derived column transformations instead of the data flow output and pushing them to the union all transformation.|||

oh, I see!. BTW, in my previous post I meant ignore (instead redirect) error as an alternative way. I corrected it though.

Rafael salas

sql

lookup transformation validation error

below is the error message:

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Data Flow ACH Validate File and Header Info [Lookup ACH Batch Number [506]]: input column "ID" (571) and reference column named "BANKBATCHNBR" have incompatible data types.

"Lookup ACH Batch Number" is the name of the lookup transformation. input column "ID" has a string data type. reference column "BANKBATCHNBR" has a varchar(50) data type. can someone please tell me how i should go about debugging this validation error? thanks.

What is the length of the input column? I think (but may be wrong) that the lengths have to be the same.

There is a property of the LOOKUP transform that contains an XML snippet. I *think* that snippet contains the metadata of the lookup cache. I'm afraid I can't be absolutely certain about these things (and I can't remember the name of the property) because I don't have a BIDS instance to hand.

-Jamie

lookup transformation validation error

below is the error message:

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Data Flow ACH Validate File and Header Info [Lookup ACH Batch Number [506]]: input column "ID" (571) and reference column named "BANKBATCHNBR" have incompatible data types.

"Lookup ACH Batch Number" is the name of the lookup transformation. input column "ID" has a string data type. reference column "BANKBATCHNBR" has a varchar(50) data type. can someone please tell me how i should go about debugging this validation error? thanks.

What is the length of the input column? I think (but may be wrong) that the lengths have to be the same.

There is a property of the LOOKUP transform that contains an XML snippet. I *think* that snippet contains the metadata of the lookup cache. I'm afraid I can't be absolutely certain about these things (and I can't remember the name of the property) because I don't have a BIDS instance to hand.

-Jamie

Lookup Transformation

Is there a way to ignore the unmatched rows using Lookup without creating another destination (Error Transformation for red connector)?Sure thing. Once the mappings have been defined in the lookup, click on the "Configure Error Output..." button and set the Error for Lookup Output to "Ignore failure." Then don't hook up the red flow to anything. Just leave it be.|||

shafiqm wrote:

Is there a way to ignore the unmatched rows using Lookup without creating another destination (Error Transformation for red connector)?

Yes, Just use 'Ignore failure' in the error output...the unmatched rows will go into the green output having null in the looup columns

|||

Rafael Salas wrote:

shafiqm wrote:

Is there a way to ignore the unmatched rows using Lookup without creating another destination (Error Transformation for red connector)?

Yes, Just use 'Ignore failure' in the error output...the unmatched rows will go into the green output having null in the looup columns

And if you truly need to ignore the error (unmatched records) then use the red flow and add it to a row counter. That way, you can capture the number of rows and they'll end up being separated from the valid records.

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 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 :-)

Lookup error redirection problem

Hi,

Maybe someone can assist me in solving the following error.
I created a facttable with surrogate keys. In the package that processes the fact data a lookup-task is supposed to find the appropriate dimension key.
If a certain dimension row is not found, this error is redirected.
In this errorflow a stored procedure will add the missing row in the dimensiontable, after which another lookup takes place. Then the results of both lookups are brought together in a union all task.

When I test this errorflow I notice that the missing dimension row is indeed added to the dimensiontable. The only task that turns red is the first lookup. Please refer below for the errormessages.
I understand the first one; that is why I created the redirection. But what does the rest mean? And even if I redirect the error for further processing is it still counted as a raised error? If the maximum allowed is 1 (as indicated) will the task still fail?

Any help will be greatly appreciated. Thanks in advance and regards,

Albert.

The errormessages are as follows:
Error: 0xC020901E at Xforms, lookups en wegschrijven, Lookup LosplaatsKey [5071]: Row yielded no match during lookup.
Error: 0xC0047072 at Xforms, lookups en wegschrijven, DTS.Pipeline: No object exists with the ID 5317.
Error: 0xC0047022 at Xforms, lookups en wegschrijven, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Lookup nwe losplaats" (5315) failed with error code 0xC0047072. 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. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at Xforms, lookups en wegschrijven, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread1" has exited with error code 0xC0047072. There may be error messages posted before this with more information on why the thread has exited.
Error: 0xC0047039 at Xforms, lookups en wegschrijven, DTS.Pipeline: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread2" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
Error: 0xC0047039 at Xforms, lookups en wegschrijven, DTS.Pipeline: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread3" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
Error: 0xC0047021 at Xforms, lookups en wegschrijven, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread2" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
Error: 0xC0047021 at Xforms, lookups en wegschrijven, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread3" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
.
.
.
Task failed: Xforms, lookups en wegschrijven
Warning: 0x80019002 at FactRittenInit: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (8) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "FactRittenInit.dtsx" finished: Failure.

It looks like it is not set to redirect. The error "Row yielded no match during lookup." hints this.
if you are certain the row is in the dim, is the case the same? Lookups are case sensitive.

Try deleting the lookup and re-adding it?|||

Hi Crispin,

Thanks for your reply. What I described is not yet a production situation, but a test in development.

The testcase is such that indeed one lookup should not yield a match. This row is then redirected and added by means of a stored procedure. After this a new lookup is performed and then the result is unioned with the first flow.

| Error redirect

V

Lookup Key --> OLE DB Command (proc adds row)

| |

V V

Union all <-- Lookup new key

|

V

The behaviour is that 'Lookup Key' notices that one dimension row is missing (which is true) and turns red. The lookup is redirected as expected, the 'OLE DB Command' adds the missing row in the dimension table and the 'Lookup new key' and 'Union all' succeed.

Nevertheless then the process stops because of the errormessages mentioned before.

Albert

|||I'm more concerned with this error:

Error: 0xC0047072 at Xforms, lookups en wegschrijven, DTS.Pipeline: No object exists with the ID 5317.|||It looks like your second failure is on the second lookup - after the new row is added. Lookups, by default, cache all their data at the beginning of the data flow. So even if you are adding the new rows, the cached values in the second lookup won't have it. To work around this, go to the Advanced tab of the second lookup, and check enable memory restriction, which turns off caching.|||

Hi John,

You are right! Never thought of that. Thanks (you save me a headache).

Albert.

Lookup error redirection

Hi,

I would like to know if the following is possible or if there is another way to implement this. I have a lookup transformation which i check to see if a specific record is stored. If nothing is returned i would like to insert a record into the table and then try the lookup again so that this time it will find the record and continue processing. How could i redirect the data flow to allow something like this to be done. I have tried linking the lookup failure constraint to an oledb destination and then attempted to link this back to the lookup but this has not worked.

Does anyone have any ideas on this?

Thanks in advance,

GrantInstead of using an OLEDB Destination component, use an OLEDB Command component. The Destination component will terminate flow while the OLE DB Command will pass records through.|||I seem to have problems when i try to use this. Should it allow me to use a subquery which obtains a record count from a table and if that returns a 0 then insert values from the input into another table?

Thanks

Grant|||I generally use stored procedures as the SqlCommand property. [Syntax is EXEC dbo.StoredProcedureName ?, ?, ?] One of the reasons I like stored procedures is that on the Column Mapping tab, the parameter names are displayed and it's easier to verify that I have the correct data mapped.|||

Another solution is to use multicast and union components: split the error output of Lookup component, direct one output to Sql or OLEDB Destination component, then merge another output with successful output of Lookup component using Union All component.

Lookup -> Multicast -> Destination
| |
Union All
|

|||

Martin, would you mind explaining in a little more detail?

At present, I use the OLEDB Command quite frequently, but with SQLCommands like, "UPDATE tblTEMP SET TempID = ?, SSN = ?,... WHERE ID = ?" etc. As you mentioned, it's a pain to map all of the fields to generic param0, param1, etc.

So what you are saying is that I can create a SP to "map" the parameters?

I appreciate any advice that you can offer a newbie...

Lookup Error handling in SSIS

Hi,

I am new to using SSIS. I need to know how can I retrieve the records in a Lookup component that cause an error to use them in a Data Transfer task. I created the error event handler but I don't know how to retrieve the records causing the error to use them in the Data Transfer task.

Thanks in advance for help!

Thanks,

Aref

I tried this and it worked,

I will redirect the record in the error configuration to be the data source to the data destination.

Thanks,

Are

|||Just to be clear for anyone not sure of this, the error flow is not always nasty errors, it can be good stuff to. Think of error as the non-default condition perhaps. For a lookup, error rows are those that the lookup failed to match, so often when loading a table that has rows in already, you would the Redirect option to send "new" rows down the error output, and then insert them.

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!

lookup - error msg

hi all,

good day!

i have dataflowtask_a and dataflowtask_b

i also have a send mail task

inside dtf_a i have a very long transformation which has 10 lookup task

each lookup task is configured to have an error output. should every lookup

encountered an error, the error output of the

lookup sends a unique error msg per lookup and is appended to a

string variable errorlst. Should dtf-a encounter an error it sets the variable

@.nogo=true.

In the control flow if @.nogo=false it proceeds to dtf_b otherwise it proceeds to

sendmail the consolidate error message.

Questions:

1. I only want to have one unique error message sent per lookup. if there are two

lookup error it should send only one message

2. the message should look like this: "error lookup1 : error lookup2 :and so soon"

how can i do this

thanks!

joey

.

I assume you are using the Script component to append messages to your errorlst variable. If you do, then it should not be a problem to append only the first lookup error.

Thanks.

|||

yeah i'm using the script component

can anyone please post a script for

the script component on how to append on a

message on a variable @.errlst with package scope

thanks

Looking for sample code for doing store procedures

I'm looking for any good samples that anyone might be aware of on how to write a SQL Server 2005 stored procedure with error handling (using TRY-CATCH blocks) that I can use as a model.

I did find the article "Detecting and Reporting Errors in Stored Procedures - Part 2: SQL Server 2005 TRY-CATCH Blocks" by Rob Garrison. It's pretty good. The article was written based upon an early beta so the author (understandable) wasn't sure about some possible features. I also was disappointed that the examples didn't flow back to showing how the application handle the resulting errors.

If you know of any good article or samples, please let me know.

TIA,

Richard Rosenheim
Please refer to the INSTAWDB.sql script installed with the samples. It has several SP's which use the new TRY...CATCH syntax.|||

Please take a look at the TRY...CATCH topics in Books Online. They also contains lot of examples.

TRY...CATCH (Transact-SQL)
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/248df62a-7334-4bca-8262-235a28f4b07f.htm

Using TRY...CATCH in Transact-SQL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a5711f5-4f6d-49e8-b1eb-53645181bc40.htm