none
scheduled job fails

    Question

  • Hi,

    I have a scheduled job fails with following message.  The weird thing is that I can run the ssis package in BIDS with no problem.  Also I can MANUALLY run the job in SQL Server Agent.   can you help me determine what the cause for the job failure? thanks    

    "Executed as user: JHA\sqlsrvc. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.5500.0 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  6:50:00 AM  Error: 2013-07-19 06:52:05.15     Code: 0xC0029151     Source: Unzip Process Task Execute Process Task     Description: In Executing "C:\Program Files (x86)\7-Zip\7z.exe" "e L:\dwbi\STAGE\Zipped\*.7z -oL:\dwbi\STAGE\Unzipped -pagksecret  -y  " at "L:\dwbi\STAGE\Unzipped", The process exit code was "2" while the expected was "0".  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  6:50:00 AM  Finished: 6:52:05 AM  Elapsed:  124.859 seconds.  The package execution failed.  The step failed.

    "


    Thank You Warmest Fanny Pied

    Friday, July 19, 2013 10:53 PM

Answers

  • Hi Fanny,

    Reasons that the package may have failed are as follows:

    • The user account does not have the required permissions. Starting the job is not 'sa' or a member of sysadmin server role, we will not have enough permission to run the DTS/SSIS job step by default. You could create a credential (windows account & password which is remembered in SQL) and make a Proxy account (pointer to the credential for a certain job subsystem) then use the proxy account to run this job.
    • The user account that is used to run the package under SQL Server Agent differs from the original package author. Please check the user account.

    The following document lists the detail troubleshooting steps, we can refer to them. If there is anything unclear or some other error message, please let us know.
    SSIS package does not run when called from a SQL Server Agent job step
    http://support.microsoft.com/kb/918760/en-us

    Regards,
    Nicole Yu


    Monday, July 22, 2013 8:21 AM

All replies

  • Check whether SQL server agent service account has permission to run 7z.exe. And also try granting read/write permisson to "L:\dwbi\STAGE\Unzipped" folder.

    Or try creating a SQL server CREDENTIALS(under Security).


    Regards, RSingh



    Saturday, July 20, 2013 2:22 AM
  • Hi,

    I have a scheduled job fails with following message.  The weird thing is that I can run the ssis package in BIDS with no problem.  Also I can MANUALLY run the job in SQL Server Agent.   can you help me determine what the cause for the job failure? thanks    

    "Executed as user: JHA\sqlsrvc. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.5500.0 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  6:50:00 AM  Error: 2013-07-19 06:52:05.15     Code: 0xC0029151     Source: Unzip Process Task Execute Process Task     Description: In Executing "C:\Program Files (x86)\7-Zip\7z.exe" "e L:\dwbi\STAGE\Zipped\*.7z -oL:\dwbi\STAGE\Unzipped -pagksecret  -y  " at "L:\dwbi\STAGE\Unzipped", The process exit code was "2" while the expected was "0".  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  6:50:00 AM  Finished: 6:52:05 AM  Elapsed:  124.859 seconds.  The package execution failed.  The step failed.

    "


    Thank You Warmest Fanny Pied

    Just to add ,this  account JHA/SQLSRVC should have read/write permission on ur C drive as well as L drive (both are used).Also for exit code error 2 plzz see link with same discussion.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Saturday, July 20, 2013 5:28 AM
  • Hi Fanny,

    Reasons that the package may have failed are as follows:

    • The user account does not have the required permissions. Starting the job is not 'sa' or a member of sysadmin server role, we will not have enough permission to run the DTS/SSIS job step by default. You could create a credential (windows account & password which is remembered in SQL) and make a Proxy account (pointer to the credential for a certain job subsystem) then use the proxy account to run this job.
    • The user account that is used to run the package under SQL Server Agent differs from the original package author. Please check the user account.

    The following document lists the detail troubleshooting steps, we can refer to them. If there is anything unclear or some other error message, please let us know.
    SSIS package does not run when called from a SQL Server Agent job step
    http://support.microsoft.com/kb/918760/en-us

    Regards,
    Nicole Yu


    Monday, July 22, 2013 8:21 AM