none
ISSUES WHILE EXECUTING PACKAGE FROM SQL JOB

    Question

  •  

    We are facing the following issue-

    We were deploying SSIS package (which is accessing DB2 to fetch data) on a 64 bit machine.

    We were able to run the packages manually, but as soon as we scheduled them as a job they executed with the following error:

    Description: component "DataReader Source" (1544) failed validation and returned error code 0x80004005

     

    We found a solution on the net that-

    while manually running the package on a 64 bit machine , SQL Management Studio calls the 32bit version of dtexec.exe, and while scheduling the package as jobs and then executing on a 64 bit machine, the SQL Agent calls the 64bit version of dtexec.exe.

    The fix is to not use the "SQL Server Integration Services Package" type in the job step, but use "Operating system (CmdExec)" type. Then in the command box, call the 32bit (x86) version of dtexec.exe.

    After using the following statement on the PRODuction server and running it through SQL Agent

    "E:\ABCD\SQL (x86)\90\DTS\Binn\dtexec" /FILE "E:\ABCD\SQL\SG0\MSSQL$PRD\Pkg_JDE_Interface.dtsx" /DECRYPT csc /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E

    we got this error:

    Date 7/3/2008 3:53:40 PM

    Log Job History (CSC_JDE_Interface)

    Step ID 1

    Server ABCDSERVER\PRD

    Job Name CSC_JDE_Interface

    Step Name CSC_JDE_Interface

    Duration 00:00:00

    Sql Severity 0

    Sql Message ID 0

    Operator Emailed

    Operator Net sent

    Operator Paged

    Retries Attempted 0

    Message

    Executed as user: SQL-USERNAME. The process could not be created for step 1 of job 0x12F19D22B0044D4885F35260C1D4C113 (reason: Access is denied). The step failed.

     

     

    What could be the issue?

    Are we missing out anything?

    Pls suggest.

    Friday, July 04, 2008 11:41 AM

All replies

  • The windows account sql server is using does not have privilege to acces the DTSEXEC program or the package file.

    If your job owner is a member of SQL Server sysadmin, SQL Server uses the SQL Server service account to access the files. Check the sql server service account and grant the permission.

     

    If your job owner is not a member of sql server sysadmin, you need to define a proxy account. In SSMS, go to SQL Serevr Agent-->Proxies-->Operating system, create a proxy account by a windows account that can access the files.

     

    Friday, July 04, 2008 1:19 PM
  • This type of error is generally caused by the SQL Agent account not having the correct permissions. Check out this blog post and see if it gives you what you need:

     

    http://bi-polar23.blogspot.com/2007/12/ssis-and-sql-server-agent.html

    Friday, July 04, 2008 2:07 PM