Job Step Error Msg

Last Post 09 Feb 2008 12:56 AM by SQLUSA. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Aaron DBA
New Member
New Member

--
06 Feb 2008 12:25 PM
In SQL Server 2005 I have a job with one step that runs an SSIS package that copies data from DB2 tables to SQL Server. The job completes successfully without reporting any errors on the surface. However when I look in View History, I see the following error message "Invalid character value for cast specification".

Consequently the transformation fails, the table is not populated with data, but yet SQL Server reports that the job succeeded.

Why does SQL Server report that the job runs successfully when actually there is a data problem with one of the transformations? I need to know if the job does not work correctly.

Thanks for any insight you can provide. See History below. Notice that it says the step succeeded regardless of the invalid cast error:

Message
Executed as user: DOMAIN1\SQLUser. ...0.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 5:00:01 AM Error: 2008-02-02 05:02:14.88 Code: 0xC0202009 Source: LOAN_APPL_PROCESS Destination 46 - LOAN_APPL_PROCESS 1 [12205] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification". End Error Error: 2008-02-02 05:02:14.88 Code: 0xC020901C Source: LOAN_APPL_PROCESS Destination 46 - LOAN_APPL_PROCESS 1 [12205] Description: There was an error with input column "DT_APPROVED" (12231) on input "Destination Input" (12218). The column status returned was: "Conversion failed because the data value overflowed the specified type.". End Error Error: 2008-02-02 05:02:14.88 Code: 0xC0209029 S... The package executed successf... The step succeeded.
Aaron DBA
New Member
New Member

--
06 Feb 2008 02:53 PM
Thanks for the reply.

SQL level is sql2k5 sp2.

The package is run by a scheduled job (hence the job history log listed above) running in SQL Server Agent with an SA level account. It's puzzling that the job history log shows the cast error and abandons the table load; yet it labels the job step a success.

Any ideas why it's behaving this way? If there is ever any sort of data load problem acknowledged by SQL Server Agent , I want it to fail the step, and not show it as successfully completed.
SQLUSA
New Member
New Member

--
06 Feb 2008 04:23 PM
What are the setting for:

FailPackageOnFailure
FailParentOnFailure

properties?

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/orderaccess2007/ The Best SQL Server 2005 Training in the World!
Aaron DBA
New Member
New Member

--
07 Feb 2008 07:21 AM
Thanks SQLUSA,

Both of those properties are set to false.

I suspect you may have discovered the problem. Presumably I should set the FailPackageOnFailure to true if I want the package to report a failure if any step in the package fails? I wil test it today. Thanks.
Aaron DBA
New Member
New Member

--
08 Feb 2008 06:23 AM
Looks like that was the problem. Thanks much for your help.
SQLUSA
New Member
New Member

--
09 Feb 2008 12:56 AM
Great!

Thanks for the feedback.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005ssis/ The Best SQL Server 2005 Training in the World!
You are not authorized to post a reply.

Acceptable Use Policy