October 27, 2009 at 8:43 am
Source: SQL 2000
Destination: SQL 2005
Issue with DT_DBTIMESTAMP when run through SQL Agent and not through IDE.
I enabled the package logging and it tells me the following:
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Invalid character value for cast specification.".
There was an error with input column "ENT_TISTMP" (6955) on input "OLE DB Destination Input" (1923). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (1923)" failed because error code 0xC020907A occurred, and the error row disposition on "input "OLE DB Destination Input" (1923)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Destination" (1910) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.
This only occurs for datetime column. Has anyone faced such type of issue? Any ideas how to resolve this issue?
October 27, 2009 at 11:34 am
Is it possible that you have package configs set up so that when the package runs on your server it uses a different source/destination then when you run the package locally?
Until you can prove otherwise, assume that the problem is exactly what the error message says it is, an overflow error on a column.
If the destination column's data type is datetime, query your source for any records with dates that are outside of the range of 01/01/1753 - 12/31/9999.
October 27, 2009 at 12:42 pm
Hi Erik,
Thanks for your reply. I appreciate your help.
I am 100% sure that the connections used are one and same either from IDE or through SQL Agent. It works perfectly fine in IDE. Only issue is with SQL agent. I wonder.. I have checked the deployed version/build number of package in msdb. it is deployed correctly. but shows same error in datetime column alone.
The datetime stamp in my source connections fall between 01/01/1753 - 12/31/9999. I see no such exceptions.
Both the source and destination are of datetime datatype. Only thing is they are different versions of SQL server
Source: SQL 2000
Destination: SQL 2005
I am tried casting the datetime. however, no luck yet. some sites are telling me that SQL 2000 datetime and SQL 2005 datetime are different in terms of their storage.
I am trying to use convert statement with the full format YYYY-MM-DD HH:MI:SS.MMM
CONVERT(DATETIME, ENT_TISTMP, 121)
Let me test this and post you.. I cant test this until my DBA comes back from lunch.. :Whistling:
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply