SSIS - Intermittent error "Conversion failed because the data value overflowed the specified type."

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.

Two options you can try,

1. Use Data Conversion in Data flow task to convert necessary values.
2. Set specific data types in derived column itself, if your column is available there.

Data conversion is handy when this type of error occurs commonly.

SSIS date time format should be used correctly. The below article explanins difference clearly. Use the correct format, which is required for you.

http://consultingblogs.emc.com/jamiethomson/archive/2005/11/15/SSIS_3A00_-What_2700_s-the-difference-between-DT_5F00_DATE-and-DT_5F00_DBTIMESTAMP.aspx