This damn job won't report failure when scheduled!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

This damn job won't report failure when scheduled!

Author

Topic

ws5926
Yak Posting Veteran

88 Posts

Posted - 2009-01-21 : 14:02:45

I'm running Windows 2003 64x. I am connecting to and IEX server using a 32bit driver. I have setup a SSIS package to down the data and have the job scheduled to run daily. Everything works fine as long as I don't get any kind of error from the IEX server.

If I run the package in BIDS manually and it errors, the job stops.

If I run the package through a command line in a scheduled job and get an error, then the error never makes it back to the job scheduler. The job just hangs like it's still running. Here's my CmdExec line that I use to run the job:

"c:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" /File "D:\SSIS Files\IEXSECONS - Small Table Transfers.dtsx" /Reporting V

I use the x86 version of dts because I have 32bit odbc drivers.

The package is set to not run in 64bit mode.

EVERYWHERE I could find I set the FailPackageOnFailure and FailParentOnFailure to true.

I have tried to setup an error handler to stop the job and it didn't do anything. The output looked like it was stopping the job, but it didn't.

The output shows an error. Here is the part of the output that shows the error. All of the other tables in the package give a percentage download status, but when it errors it gives something a little different.

Info: 2009-01-21 03:32:05.89
Code: 0x4004300C
Source: Agtavail DTS.Pipeline
Description: Execute phase is beginning.
End Info
DataFlow: 2009-01-21 03:32:07.75
Source: Agtavail
Component "Convert agtavail 1" (122) will receive 4519 rows on input "Data Conversion Input" (123)
End DataFlow
DataFlow: 2009-01-21 03:32:07.76
Source: Agtavail
Component "IEXAGTAVAIL 1" (1) will receive 4519 rows on input "OLE DB Destination Input" (14)
End DataFlow
DataFlow: 2009-01-21 03:32:09.28
Source: Agtavail
Component "Convert agtavail 1" (122) will receive 4519 rows on input "Data Conversion Input" (123)
End DataFlow
DataFlow: 2009-01-21 03:32:09.29
Source: Agtavail
Component "IEXAGTAVAIL 1" (1) will receive 4519 rows on input "OLE DB Destination Input" (14)
End DataFlow
DataFlow: 2009-01-21 03:32:11.34
Source: Agtavail
Component "Convert agtavail 1" (122) will receive 4519 rows on input "Data Conversion Input" (123)
End DataFlow
DataFlow: 2009-01-21 03:32:11.35
Source: Agtavail
Component "IEXAGTAVAIL 1" (1) will receive 4519 rows on input "OLE DB Destination Input" (14)
End DataFlow
DataFlow: 2009-01-21 03:32:13.54
Source: Agtavail
Component "Convert agtavail 1" (122) will receive 4519 rows on input "Data Conversion Input" (123)
End DataFlow
DataFlow: 2009-01-21 03:32:13.56
Source: Agtavail
Component "IEXAGTAVAIL 1" (1) will receive 4519 rows on input "OLE DB Destination Input" (14)
End DataFlow
DataFlow: 2009-01-21 03:32:15.78
Source: Agtavail
Component "Convert agtavail 1" (122) will receive 4519 rows on input "Data Conversion Input" (123)
End DataFlow
DataFlow: 2009-01-21 03:32:15.79
Source: Agtavail
Component "IEXAGTAVAIL 1" (1) will receive 4519 rows on input "OLE DB Destination Input" (14)
End DataFlow
DataFlow: 2009-01-21 03:32:18.01
Source: Agtavail
Component "Convert agtavail 1" (122) will receive 4519 rows on input "Data Conversion Input" (123)
End DataFlow
DataFlow: 2009-01-21 03:32:18.03
Source: Agtavail
Component "IEXAGTAVAIL 1" (1) will receive 4519 rows on input "OLE DB Destination Input" (14)
End DataFlow
DataFlow: 2009-01-21 03:32:20.29
Source: Agtavail
Component "Convert agtavail 1" (122) will receive 4519 rows on input "Data Conversion Input" (123)
End DataFlow
DataFlow: 2009-01-21 03:32:20.31
Source: Agtavail
Component "IEXAGTAVAIL 1" (1) will receive 4519 rows on input "OLE DB Destination Input" (14)
End DataFlow
DataFlow: 2009-01-21 03:32:22.53
Source: Agtavail
Component "Convert agtavail 1" (122) will receive 4519 rows on input "Data Conversion Input" (123)
End DataFlow
DataFlow: 2009-01-21 03:32:22.54
Source: Agtavail
Component "IEXAGTAVAIL 1" (1) will receive 4519 rows on input "OLE DB Destination Input" (14)
End DataFlow
Error: 2009-01-21 03:32:22.67
Code: 0xC02090F5
Source: Agtavail agtavail 1 [56]
Description: The component "agtavail 1" (56) was unable to process the data.
End Error
Error: 2009-01-21 03:32:22.68
Code: 0xC0047038
Source: Agtavail DTS.Pipeline
Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "agtavail 1" (56) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is define

Basically I just want the error message to make it back to the scheduler. Then I can setup a retry and then send me a message if it still fails. Any ideas?

--------------------------------------------------------------------------------
Live to Throw
Throw to Live
Will Summers

Live to Throw
Throw to Live

Terry Dart
Starting Member

2 Posts

Posted - 2009-08-17 : 05:45:59

Hello

Did you ever figure out how the fix this issue, as I am having exactly the same problem.

Regards

ws5926
Yak Posting Veteran

88 Posts

Posted - 2009-08-27 : 21:48:59

Nope.

Live to Throw
Throw to Live

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-28 : 00:06:25

what driver are you using?

Terry Dart
Starting Member

2 Posts

Posted - 2009-08-28 : 03:43:11

Hello

I am not 100% sure if I have fixed the problem or not but I changed the settings failparentonfailure to true and maximumerror count to 0, in visual studio for the package in question.

A few days later a very similar error happened and the job ended as a failure. Again I am not certain that this fixed the problem as it was a slightly different error that happened.

Regards

ws5926
Yak Posting Veteran

88 Posts

Posted - 2009-08-28 : 08:00:14

I tried the maxerror and the fail parent/child thing everywhere I could find it in my ssis package and it still did not fail. I am using the latest Simba driver. I have contacted IEX and Simba and they have verified this.

I even told them that they could give me the source code for the 32 bit and that I would convert it for free to 64 bit and give it back to them, but I don't think they thought I was serious.

Live to Throw
Throw to Live

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources