locked
How do I troubleshoot SSIS packages failed execution in a SQL Agent job? RRS feed

  • General discussion

  • [This is just for sharing information for those common asked questions collected from forums. If you have any better way or feedback, please directly reply in this thread so that community users can benefit from your answers.]


    Question:
    I have a SSIS package which is running fine in BIDS but when I run it with a SQL Agent job, it fails. What is the problem?
     
    Answer:
    When you see a SSIS package fails running in a SQL Agent job, you need to first consider the following conditions:
    1. The user account that is used to run the package under SQL Server Agent differs from the original package author.
    2. The user account does not have the required permissions to make connections or to access resources outside the SSIS package.

    You can find the detailed information in this KB article:
    An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step
    http://support.microsoft.com/kb/918760

    You can check SQL Server Agent’s activity logs, Windows Event logs and SSIS logs to get more clues. Also the tool Process Monitor is helpful to track the cause of registry or file access related issues.

    The following 4 issues are common encountered in the SSIS forum. We also would like to list them here for your reference:
    1. The package's Protection Level is set to EncryptSensitiveWithUserKey but your SQL Server Agent service account is different from the SSIS package creator.
    2. Data source connection issue.
    3. File or registry access permission issue.
    4. No 64-bit driver issue.
     
    Package Protection Level issue
    For the 1st issue, you can follow the following steps to troubleshoot this issue:
    1. Check what the Protection Level is in your SSIS package.
    2. If the Protection Level is set to EncryptSensitiveWithUserKey, check the Creator in your SSIS package and compare it with the SQL Server Agent Service account.
    3. If the Creator is different from the SQL Server Agent Service account, then the sensitive data of the SSIS package could not be correctly decrypted, which will lead to the failure.
     
    A common solution to this issue is that you create a proxy account for SSIS in SQL Server Agent and then specify the proxy account as the "Run as" account in the job step. The proxy account must be the same as the SSIS package creator. You can refer to this article for how to create a proxy account:
    How to create a proxy account
    http://msdn.microsoft.com/en-us/library/ms190698.aspx
     
    You can also change the SSIS package protection level to EncryptSensitiveWithPassword and specify the password (/P XXXX) in the command line in the job step. For the command parameters, you can refer to this article:
    Dtexec utility
    http://msdn.microsoft.com/en-us/library/ms162810.aspx
     
    Besides you can import your SSIS package into your SQL Server MSDB database via SQL Server Management Studio (connecting to Integration Services). When you import the package to MSDB, you need to set the ProtectionLevel to "Rely on server storage and roles for access control". After that, add your SQL Server Agent Services account to be a user of MSDB with the db_ssisadmin role. Then connect to your SSIS in SSMS again, right click your SSIS package, click Package Roles, and set both the ReaderRole and Writer Role to db_ssisadmin.
     
    Data Source Connection Issue
    For the 2nd data source connection issue, it usually happens when you are using Windows Authentication for your data source. In this case, you need to make sure that the SQL Server Agent Services service account or your Proxy account has the permission to access your database.
     
    File or Registry Key Access Issue
    For the 3rd file or registry access permission issue,  if you know which file or folder your SSIS package need to make sure that your SQL Server Agent Services service account or your Proxy account has the permission to access the file or folder. If you are not very sure of this, you can run SQL Server Process Monitor to monitor your SQL Server Agent Services process and dtexec process when you start running the job. You can analyze the trace to check if there is any ACCESS DENIED message. You can download Process Monitor here:
    Process Monitor
    http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx
     
    64-Bit Driver Issue
    For the 4th issue, this usually happens on a 64-bit server since there is no corresponding 64-bit driver in a 64-bit process. Think about that when your SSIS package is accessing Excel or Access database file while there is no related 64-bit Jet driver, the job of executing your SSIS package will fail. To resolve this issue, before SQL Server 2008, you need to use 32-bit DTEXEC utility in your SQL Server Agent job to execute the SSIS package. In SQL Server 2008, you have a better choice, using the option “Use 32-bit runtime” under the tab “Execution options” of your job step with the “SQL Server Integration Services Package” type.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Tuesday, June 22, 2010 3:45 AM

All replies

  • I am running Microsoft SQL Server 2005 - 9.00.4060.00 (Intel X86) on a Windows Server 2003 Enterprise Edition SP2. Oracle Installed is of 11g Release 1 (11.1) for Microsoft Windows (32-Bit).

    I am importing data from an Oracle database using the Native Oledb\Microsoft Oledb Provider for Oracle. I can successfully run the package directly in SSBIDS but when I try and run it as a job I get the below error:

    Message Executed as user: AMERICAS\processamcwindows. ...5.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 6:24:10 AM Error: 2013-09-06 06:24:12.85 Code: 0xC0202009 Source: RSDomsStatus Connection manager "UDM" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". End Error Error: 2013-09-06 06:24:12.86 Code: 0xC00291EC Source: Execute SQL Task - Check and Create the Temp table in Oracle 1 Execute SQL Task Description: Failed to acquire connection "UDM". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 6:2... The package execution fa... The step failed.

    I am not finding any clear direction on where else to go for this problem. Any advise would be appreciated.

    In the SSIS package I have created two connection managers: 

    i. .net provider\sqlclient data provider -- this is used to connect to a sql server database which is working fine. 
    ii. native oledb\microsoft oledb provider for oracle -- this is used to connect to oracle database , and the error is for this only. 
    DSN is not there , but I have checked the "Microsoft ODBC driver for Oracle" is already installed/configured on the server.

    1.Created a proxy based on the inputs from your post. Ran the job which invokes the SSIS package. Got error (which is same as the previous one): Message Executed as user: AMERICAS\Abhishek_Chakrabort1. (-- only difference it got executed by the proxy name).

    2.Made changes to the package to make it 32 bit and then uploaded in integration services and also made changes in job to make it run as 32 bit. Ran the job but still the same error persists. 

    3.Tried creating a System DSN with the "Microsoft ODBC driver for Oracle" and ran the job but the issue still persists. 

    Monday, September 9, 2013 11:21 AM
  • Does it run successfully as an SSIS package on the server?

    It looks like you either don't have the datasource set up on the server, or your SQL Agent account does not have access to the datasource?

    Thursday, July 20, 2017 8:48 PM