Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available
Well here’s a crackingly helpful error message if there ever was one:
[<Component Name>] 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 Native Client” Hresult: 0x80040E21 Description: “Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.”.
What does it all mean? Fortunately the answer is simple – the OLE DB component is trying to deliver a column to the target table and the target database won’t accept the input data type. The component has allowed the mapping because it should / might work but when it tries to execute – blecch! Tortellini!
In my case this was because I tried to push a Text Stream SSIS data type flow into a Varchar SQL Server column. Mapped fine, but failed when trying to run. Apparently a common problem when running data into a different database type to the source, e.g. Teradata to SQL Server.
OK, thanks for the explanation. Now what is the solution? What do we need to do to get data from, in my case, an excel spreadsheet column into a SQL Server field (nvarchar(15), null)? Do I need to cast it? If so how? I tried doing this but it didn’t work: CAST([MIPS] AS nvarchar(15)) AS [MIPS]
Take it in as the Excel Source provides it, then use a Data Conversion to change it to the type you want.
I faced this Issue when i was defining Parameter variables in SSIS SQL Task that calls a stored procudure like below
EXEC sp_Test ?,?,? OUT
and in the parameters list, I had
Parameter Name as 1,2,3 which should be 0,1,2.
That solved.