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...