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.