SSIS throws an "DTS_E_PRIMEOUTPUTFAILED" error in SSIS Data Flow with Excel source

I developing an SSIS process that extracts data from Excel files and loads them into SQL Server DB.

I have separate data flows for each worksheet in the source file. The Excel file contains data in different formats (but can contain invalid data as well), so I'm using explicit type casting.

In case of dates, I'm using a cdate(f1) as f1 construction in the SQL statement (with number changing according to the column), in order to prevent SSIS from deciding on the date format on its own.

This usually works fine. However, in some cases it throws an error when I try to run the process and there's an illegal value in the data:

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on My_Excel_Source returned error code 0x80040E21. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

I don't have any other errors.

When I try to preview the result from within the Excel Source Editor window, I do get the data grid with data sample, as expected. When there's illegal data, the text is displayed - which is fine. In other data flows, the value of '1899-12-30' is returned and I can do whatever I want with it.

I've tried to define the data type for this column explicitly as DT_DATE, but I still get the error. I've compared all kinds of possible property settings with other data flows (that don't throw this error), and it all seems to be identical. I've also tried to change the error handling behavior of the Excel Source component to "Ignore failure" instead of "Fail component" - no effect.

I should note, that there's no possible resource issue there. I'm dealing with just a handful of rows in each sheet (a few dozens, at most).

I expect the Excel Source component return the value of '1899-12-30' when the source cell contains an illegal value, rather than crashing the data flow.

Thanks, David

If you want to let Excel control date parsing, I would try some SQL expression along the line

CASE 
  WHEN IsDate(f1) THEN CDate(f1)
  ELSE NULL 
END AS f1

So if the f1 parses as date, it casts it to Date, otherwise it returns NULL. If you prefer, replace NULL with any sentinel value you might prefer, like CDate('1899-12-30').

Without IsDate guard, Excel OLEDB connector does what you told it to do - tries to cast the expression to date, and fails.

The functionality of testing per-cell whether the cast works and displaying text in case of failure is part of Excel application, not Excel OLEDB connector. So we need to emulate it here using the CASE expression above.