Handling errors when using a Lookup Task

Hi

I am trying to use this painful new SSIS process. I basically need to use a lookup task to check to see whether a record exists or not. If not, then I need to insert the record. However, because this is treated as an error situation (which is stupid in itself), I get a problem when the number of records not found reach the MaximumErrorCount, and the rest of the package fails. Is there any other method of doing this type of thing, without simply increasing the MaximumErrorCounty to some ludicrous value. I could do this type of thing very very very easily when using DTS packages using the Data Driven Task, it seems so stupid that I can't perform the same kind of task using SSIS.

Any help would be appreciated

Thanks

Darrell

Darrell,

You need to configure the error output of the lookup component to redirect the rows that fail the lookup. you can then route the error flow to insert the records.

Frank

|||

Frank

I have already configured the error output of the lookup task to redirect the error rows, however, the task stops after processing the errors of the lookup task because the number of errors exceeds the MaximumErrorCounty, even though I don't want it to stop processing. For the error output I am redirecting the rows to a Derived field task, so that I can add additional fields ready for the SQL task of inserting the information. However, it doesn't even get to execute the derived task because of this problem with the MaximumErrorCount

Any other ideas?

Thanks

Darrell

|||

DarrellMerryweather wrote:

Frank

I have already configured the error output of the lookup task to redirect the error rows, however, the task stops after processing the errors of the lookup task because the number of errors exceeds the MaximumErrorCounty, even though I don't want it to stop processing. For the error output I am redirecting the rows to a Derived field task, so that I can add additional fields ready for the SQL task of inserting the information. However, it doesn't even get to execute the derived task because of this problem with the MaximumErrorCount

Any other ideas?

Thanks

Darrell

Darrell,

I've used this technique on many occasions and trust me - its not affected by MaximumErrorCount. I've diverted millions of rows down the error output of a LOOKUP component when MaximumErrorCount=1 and the data-flow succeeds.

Are you sure there isn't another error occurring somewhere?

-Jamie

P.S. For nomenclature clarity, the toolbox items that appear inside a data-flow ar called components, not tasks!

|||

DarrellMerryweather wrote:

Hi

I am trying to use this painful new SSIS process. I basically need to use a lookup task to check to see whether a record exists or not. If not, then I need to insert the record. However, because this is treated as an error situation (which is stupid in itself),

Why is that stupid? The objective here is to achieve a business requirement - does the specifics of how it is achieved really matter?

DarrellMerryweather wrote:

I get a problem when the number of records not found reach the MaximumErrorCount, and the rest of the package fails. Is there any other method of doing this type of thing, without simply increasing the MaximumErrorCounty to some ludicrous value. I could do this type of thing very very very easily when using DTS packages using the Data Driven Task, it seems so stupid that I can't perform the same kind of task using SSIS.

I promise you this CAN be achieved. Persevere - you'll find the problem eventually

Perhaps check the ForceExecutionResult property.

-Jamie

|||

Guys

I was actually getting an error on the input of the derived field, where it was truncating the value coming in.

Apologies and thanks for the help, the package is now running sucessfully

Thanks again

D