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

1
votes

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.

1
sql-serversql-server-2008ssistype-conversion
Maybe you are trying to put your SQL datetime into an SSIS datatype that is too limited, like DT_DBTIMESTAMP. What SSIS datatype are you using for dtCreated? See if this helps: msdn.microsoft.com/en-us/library/ms141036(v=sql.105).aspx - Tab Alleman
Hi Tab, I am using DT_DBTIMESTAMP, although unless I'm missing something then I'm not sure why that would be too limited for a SQL datetime? As per the link you provided, DT_DBTIMESTAMP is 'A timestamp structure that consists of year, month, day, hour, minute, second, and fractional seconds. The fractional seconds have a fixed scale of 3 digits'. - KingTen87
I know, but the article also says this: "On the other hand, DT_DBTIMESTAMP is represented by a structure that internally has individual fields for year, month, day, hours, minutes, seconds, and milliseconds. This data type has larger limits on ranges of the dates it can present." The phrase "larger limits" is .. confusing, does it mean more limited, or more tolerant? At first glance I thought it meant "more limited", but now I'm not so sure. - Tab Alleman
I think that means that, in comparison to DB_DATE (which can only hold values from 30th December 1899), the range of supported and storable dates is much larger for DT_DBTIMESTAMP due to the individual component fields. In a similar way to how an SQL SMALLDATETIME can hold values from 1/1/1900 up to 6/6/2079, whereas DATETIME can range from 1/1/1753 to 31/12/9999. I've set up some batch inserts followed by row by row inserts to try and trap offending rows so I'll see if that sheds any light. - KingTen87
Well that's what I was after, whether you were using an SSIS datatype that was too limited for your data, but on third thought, I think your interpretation is probably right, and you are using the least limited (other than DT_DBTIMESTAMP2) option, which shouldn't be a problem. - Tab Alleman

1 Answers

0
votes

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