I have an SSIS package which intermittently fails with a handful of errors, the two most informative of which are:
DFT_PlaySummary SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Invalid date format". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Invalid date format".**
DFT_PlaySummary There was an error with input column "dtCreated" (2129) on input "OLE DB Destination Input" (2121). The column status returned was: "Conversion failed because the data value overflowed the specified type.".**
Both source and destination DB's are SQL Server 2008 R2. The column dtCreated in both source and destination is DATETIME (NOT NULL).
Now, I understand the basis of what the error message is saying, but I can't understand how the data or the conversion could be invalid in the package without having already been deemed invalid at the source.
Strangely, on some days the package will fail with this error, restart itself (as per the package settings) and then succeed without any manual intervention.
Any pointers as to what I should be looking for within the package steps that could be causing this? The data flow task itself consists of a couple of OLE DB sources, each with a derived column, followed by a merge of these data sets and then a conditional split to various OLE DB destinations depending on the dtCompleted value.
