I have a SQL Job that is calling a DTS package and the last 3 days I've been
getting a failure at the DTS step. This code has run fine for many months.
It says invalid character for cast specific (and overflow error). The source
field is a date/time and the destination is a date/time. Cast is not used as
a function. To_Date is used but not on Result_DATS field (in error). The
Results_Dats field is a system generated date/time stamp from the source.
I'm not sure how an invalid character was input or even to find such a
character. I have queried the source for the dates in question and only see
regular dates or empty fields. Can someone help me out with this error?


Here is the Error (DTS SQL statement is below):

Executed as user: XXX\SYSTEM. ...
OnStart: DTSStep_DTSDataPumpTask_1 DTSRun
OnProgress: DTSStep_DTSDataPumpTask_1; 1000 Rows have been transformed or
copied.; PercentComplete = 0; ProgressCount = 1000 DTSRun
OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)
Error string: Error at Destination for Row number 1103. Errors encountered
so far in this task: 1. Error source: DTS Data Pump Help file:
Help context: 0 Error Detail Records: Error: -2147467259
(80004005); Provider Error: 0 (0) Error string: Insert error, column
14 ('RESULT_DATS', DBTYPE_DBTIMESTAMP), status 6: Data overflow. Error
source: Microsoft Data Transformation Services (DTS) Data Pump Help
file: sqldts80.hlp Help context: 30702 Error: -2147467259
(80004005); Provider Error: 0 (0) Error string: Invalid character
value for cast specification. Error source: Microsoft OLE DB Provider
for SQL Server... Process Exit Code 1. The step failed.

SQL Statement

SELECT UTRS.AUDIT_KEY,
UTRS.UNIT_ID, TO_CHAR(UTRS.TEST_SEQ_NO),
UTRS.TEST_ID, UTRS.RESULT_STATUS_1, UTRS.TB_ID,
TO_CHAR(UTRS.TB_SEQ_NO), UTRS.WELL_NUMBER,
UTRS.ABSORBANCE, UTRS.RATIO, UTRS.RESULT_DATS,
UTRS.INSERT_DATS, UTRS.EXTRACT_DATE
FROM UTRS, UTRS_AUDIT
WHERE UTRS.AUDIT_KEY = UTRS_AUDIT.AUDIT_KEY AND
TO_DATE(UTRS_AUDIT.AUDIT_INSERT_DATS, 'YYYY/MM/DD') =
TO_DATE(SYSDATE-1, 'YYYY/MM/DD')
GROUP BY UTRS.AUDIT_KEY,
UTRS.UNIT_ID, TO_CHAR(UTRS.TEST_SEQ_NO),
UTRS.TEST_ID, UTRS.RESULT_STATUS_1, UTRS.TB_ID,
TO_CHAR(UTRS.TB_SEQ_NO), UTRS.WELL_NUMBER,
UTRS.ABSORBANCE, UTRS.RATIO, UTRS.RESULT_DATS,
UTRS.INSERT_DATS, UTRS.EXTRACT_DATE

This is not a SQL Server source right? Oracle?

Different providers have different ranges for Dates and Times. This is
more than likely causing this error.

Have a look in BOL for SQL Server's ranges and then test your source
data in the 'RESULT_DATS' column.

Also have a look at the formatting.

An easy way would be to grab 1 row from the Source. Put it in an INSERT
statement in Query Analyser on SQL Server and see if it works.


Allan


"LeAnn" <LeAnn (AT) discussions (DOT) microsoft.com> wrote