SSIS Lookup Step Fails Without Meaningless Union All Step

Imagine an SSIS package which does a lookup by Column A, which can be null, or columns B and C if A is null (columns B and C cannot be null). In terms of SSIS steps, this is done as follows:

1) A Conditional Split where Condition 1 is "ISNULL(ColumnA)". Condition 1 output is named ISNULL, Default output is named NOTNULL.

2a) The NOTNULL output directs to a lookup task. The lookup task does an SQL query matched to the value of ColumnA. It is not cached.

2b) The ISNULL output directs to a lookup task. The lookup task does an SQL query matched to the values of ColumnB and ColumnC. It is also not cached.

3) Both lookups direct to a union task and flow continues.

I am currently working on a package containing this workflow and the package is failing. The only error message provided is:

"[Lookup [7708]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E21 Description: "Invalid input parameter values. Check the status values for detail."."

Strangely, if I direct the output from the NOTNULL condition into a Union All task, and then into the lookup, everything works perfectly. I have no idea why this would affect the outcome. It seems like a Union All task with one input should have no effect on the data.

Even more strangely, I have not been able to reproduce this problem outside of this particular package. I have tried creating a sample package with the same workflow, using the same source data and the same database, and it does not throw this error.

I don't expect anyone to be able to read this and know immediately what the problem is. However, I am hoping that someone knows of a known issue with SSIS that may apply, or anyone who knows how to get more details from that error message. (I have no idea what it means by "Check the status values for detail.")

Update: Providing some additional information by request.

SSIS is version 2008 R2 running in VS2008

The target SQL Server is 2008 SP2 (NOT R2)

Column A is a DT_I8/bigint, Column B is DT_STR(18)/varchar(18), Column C is DT_STR(25)/varchar(25).

There is one more piece of background data that may help spark a new thought. This package works by trying to do an OLEDB insert on a table with a unique constraint, and if that fails it redirects the failure rows to a section that does an update instead. The steps I described above are the piece that tries to do the Update after a failed insert. If I remove the Insert and go straight to an update the package starts to work. However, I tried to replicate just this section in a test package and it didn't fail at all, so I can't be sure this is the issue.

2008, not 2005 right? The short answer is I think your meta data is jacked.

Right click on the green line connector before your conditional split operation and note the data types and lengths/precision for columns A, B and C. Contrast that with the output of the Union All. The type of A must have changed between the two points for it to be behaving like this (or I've just gambled some reputation away). The problem might be higher up in the data flow where it seems to think it has an incorrect data type.

Generally, the engine is good about detecting changes but sometimes it doesn't get the signal, usually when there are size changes and you've got some async components between the source and the affected transform.

If that's not the case, what is the data type for A in the data flow and what is the data type in the target database (SQL Server as well?) for this column and can you provide the lookup query? Also, modify the failing lookup to redirect rows on error to a bit bucket (derived column, row count, script task) and drop a data viewer on that. What is the value of the offending row? Does that exist in the target database? Lookups are case sensitive, even if the database is not.