1

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.

Hadi
  • 36,233
  • 13
  • 65
  • 124
KingTen87
  • 11
  • 1
  • 1
  • 3
  • 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: http://msdn.microsoft.com/en-us/library/ms141036(v=sql.105).aspx – Tab Alleman Dec 31 '14 at 16:42
  • 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 Dec 31 '14 at 16:54
  • 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 Dec 31 '14 at 17:23
  • 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 Dec 31 '14 at 17:44
  • 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 Dec 31 '14 at 17:46
  • The error clearly says.. conversion failed. You can try Data Conversion option in data flow. Check out the below link for data types in ssis 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 – Govind Jan 02 '15 at 09:35
  • As a sanity check, always verify this: connect the error output of the OLE DB destination to some passive dummy component (multicast or derived column...) and enable data viewer on that path. Run it and view the values of the offending columns. Now go into a SQL Server client (a la SSMS) and attempt to do an insert with that _exact_ same value. It should fail there too. If it doesn't ... then you're allowed to go insane. – Michael Plautz Feb 19 '17 at 03:18
  • Are there any clues in the error message as to whether the error is in the source or destination component? Are you certain that `dtCreated` is mapped through from source to destination? Based on your explanation, it's strange that this happens at all, let a,lone that it happens intermittently – Nick.Mc Sep 24 '17 at 23:20

1 Answers1

0

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

Govind
  • 979
  • 4
  • 14
  • 45