SQL Server Performance

This damn package won't report failure when scheduled!!

Discussion in 'SQL Server 2005 Integration Services' started by ws5926, Jan 21, 2009.

  1. ws5926 New Member

    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 Server90DTSBinndtexec.exe" /File "D:SSIS FilesIEXSECONS - 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

  2. satya Moderator

    Have you referred to this job history to see any other information is reported?
    One solution is to go to the package, and refresh the DataReader Source component and adjust the column mappings accordingly.
  3. ws5926 New Member

    First off, thanks for the reply. I'm new to this forum and was beginning to think there wasn't anyone here.
    There isn't anything in the job history because the job doesn't fail, the package fails. The job just sits there like the package is still running. I need the package failure message to make it back to the job TO fail. I end up having to stop the job and restart it.
    If the source data hasn't changed, then why would adjusting the columns do anything? I will look into it, but I don't see how I can 'adjust the columns accordingly' if the references are already correct. Please explain.
    Thanks again for the reply.

Share This Page