SSIS package does not run when called from a SQL Server Agent job step

Article translations Article translations
Article ID: 918760 - View products that this article applies to.
Expand all | Collapse all

On This Page

Symptoms

When you call a Microsoft SQL Server 2005 Integration Services (SSIS) package from a SQL Server Agent job step, the SSIS package does not run. However, if you do not modify the SSIS package, it will run successfully outside SQL Server Agent.

Resolution

To resolve this problem, use one of the following methods. The most appropriate method depends on the environment and the reason that the package failed. Reasons that the package may have failed are as follows: 
  • The user account that is used to run the package under SQL Server Agent differs from the original package author.
  • The user account does not have the required permissions to make connections or to access resources outside the SSIS package.
The package may not run in the following scenarios:
  • The current user cannot decrypt secrets from the package. This scenario can occur if the current account or the execution account differs from the original package author, and the package's ProtectionLevel property setting does not let the current user decrypt secrets in the package.
  • A SQL Server connection that uses integrated security fails because the current user does not have the required permissions.
  • File access fails because the current user does not have the required permissions to write to the file share that the connection manager accesses. For example, this scenario can occur with text log providers that do not use a login and a password. This scenario can also occur with any task that depends on the file connection manager, such as a SSIS file system task.
  • A registry-based SSIS package configuration uses the HKEY_CURRENT_USER registry keys. The HKEY_CURRENT_USER registry keys are user-specific.
  • A task or a connection manager requires that the current user account has correct permissions.
Method 1: Use a SQL Server Agent proxy account

Method 2: Set the SSIS Package ProtectionLevel property to ServerStorage

Method 3: Set the SSIS Package ProtectionLevel property to EncryptSensitiveWithPassword

Method 4: Use SSIS Package configuration files

Method 5: Create a package template

Status

This behavior is by design.

Advanced information

Steps to reproduce the problem

  1. Log in as a user who is not part of the SQLServer2005SQLAgentUser group. For example, you can create a local user.
  2. Create an SSIS package, and then add an ExecuteSQL task. Use an OLE DB connection manager to the local msdb file by using the following string: ‘Windows Authentication’ -SQLSourceType: "Direct Input" -SQLStatement: "sp_who"
  3. Run the package to make sure that it runs successfully.
  4. Notice that the ProtectionLevel property is set to EncryptSensitiveWithPassword.
  5. Create a SQL Server Agent job and a job step. In the Run As list, click SQL Server Agent Service to run the job step.
The text in the SQL Server Agent Job History displays information that resembles the following:

Executed as user: DOMAIN\USERNAME. The package execution failed. The step failed.

Decrypt package secrets

The default setting for the SSIS package ProtectionLevel property is EncryptSensitiveWithUserKey. When the package is saved, SSIS encrypts only the parts of the package that contain properties that are marked "sensitive," such as passwords, usernames, and connection strings. Therefore, when the package is reloaded, the current user must satisfy the encryption requirements for the sensitive properties to be decrypted. However, the current user does not have to satisfy the encryption requirements to load the package. When you run the package through a SQL Server Agent job step, the default account is the SQL Server Agent Service account. This default account is most likely a different user than the package author. Therefore, the SQL Server Agent job step can load and start to run the job step, but the package fails because it cannot complete a connection. For example, the package cannot complete an OLE DB connection or an FTP connection. The package fails because it cannot decrypt the credentials that it must have to connect.

Important Consider the development process and the environment to determine which accounts are needed and used on each computer. The EncryptSensitiveWithUserKey setting of the ProtectionLevel property is a powerful setting. This setting should not be discounted because it causes deployment complications at first. You can encrypt the packages when you are logged in to the appropriate account. You can also use the Dtutil.exe SSIS command prompt utility to change the protection levels by using a .cmd file and the SQL Server Agent command subsystem. For example, follow these steps. Because you can use the Dtutil.exe utility in batch files and loops, you can follow these steps for several packages at the same time.
  1. Modify the package that you want to encrypt by using a password.
  2. Use the Dtutil.exe utility through an Operating System (cmd Exec) SQL Server Agent job step to change the ProtectionLevel property to EncryptSensitiveWithUserKey. This process involves decrypting the package by using the password, and then re-encrypting the package. The user key that is used to encrypt the package is the SQL Server Agent job step setting in the Run As list.

    Note Because the key includes the user name and the computer name, the effect of moving the packages to another computer may be limited.

Make sure that you have detailed error information about the SSIS package failure

Instead of relying on the limited details in the SQL Server Agent Job History, you can use SSIS logging to make sure that you have error information about the SSIS package failure. You can also run the package by using the exec subsystem command instead of the SSIS subsystem command.

About SSIS logging

SSIS logging and log providers let you capture details about the package execution and failures. By default, the package does not log information. You must configure the package to log information. When you configure the package to log information, detailed information is displayed that resembles the following. In this case, you will know that it is a permissions issue:

OnError,DOMAINNAME,DOMAINNAME\USERNAME,FTP Task,{C73DE41C-D0A6-450A-BB94-DF6D913797A1},{2F0AF5AF-2FFD-4928-88EE-1B58EB431D74},4/28/2006 1:51:59 PM,4/28/2006 1:51:59 PM,-1073573489,0x,Unable to connect to FTP server using "FTP Connection Manager".

OnError,DOMAINNAME,DOMAINNAME\USERNAME,Execute SQL Task,{C6C7286D-57D4-4490-B12D-AC9867AE5762},{F5761A49-F2F9-4575-9E2B-B3D381D6E1F3},4/28/2006 4:07:00 PM,4/28/2006 4:07:00 PM,-1073573396,0x,Failed to acquire connection "user01.msdb". Connection may not be configured correctly or you may not have the right permissions on this connection.

About the exec subsystem command and output information

By using the exec subsystem command approach, you add verbose console logging switches to the SSIS command line to call the Dtexec.exe SSIS command-line executable file. Additionally, you use the Advanced job feature of the output file. You can also use the Include Step Output in the history option to redirect the logging information to a file or to the SQL Server Agent Job History.

The following is an example of a command line:

dtexec.exe /FILE 
"C:\_work\SSISPackages\ProtectionLevelTest\ProtectionLevelTest\AgentTesting.dtsx" /MAXCONCURRENT " -1 
" /CHECKPOINTING OFF  /REPORTING V  /CONSOLELOG NCOSGXMT


The /console logging returns details that resemble the following:

Error: 2006-04-27 18:13:34.76
   Code: 0xC0202009
   Source: AgentTesting Connection manager "(local).msdb"
   Description: An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80040E4D  Description: "Login failed for user 'DOMAINNAME\username'.".
End Error


Error: 2006-04-28 13:51:59.19
   Code: 0xC0016016
   Source:  
   Description: Failed to decrypt protected XML node "DTS:Property" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
End Error


Log:
     Name: OnError
     Computer: COMPUTERNAME
     Operator: DOMAINNAME\username
     Source Name: Execute SQL Task
     Source GUID: {C6C7286D-57D4-4490-B12D-AC9867AE5762}
     Execution GUID: {7AFE3D9E-5F73-42F0-86FE-5EFE264119C8}
     Message: Failed to acquire connection "(local).msdb". Connection may not be configured correctly or you may not have the right permissions on this connection.
     Start Time: 2006-04-27 18:13:34
     End Time: 2006-04-27 18:13:34
End Log

References

For more information about a similar problem, click the following article number to view the article in the Microsoft Knowledge Base:
904800 You receive an "Error loading" error message when you try to run an SQL Server 2005 Integration Services package in SQL Server 2005
For more information about how to use the Dtutil.exe utility in batch operations, click the following article number to view the article in the Microsoft Knowledge Base:
906562 How to use the dtutil utility (Dtutil.exe) to set the protection level of a batch of SQL Server Integration Services (SSIS) packages in SQL Server 2005
For more information about how to create package templates, click the following article number to view the article in the Microsoft Knowledge Base:
908018 How to create a package template in SQL Server Business Intelligence Development Studio


For more information about SSIS package security and the ProtectionLevel property, see the "Security Considerations for Integration Services" topic in SQL Server 2005 Books Online.

Unfortunately, users are not aware that default agent job step settings put them in this state. For more information about SQL Server Agent proxies and SSIS, see the following topics in SQL Server 2005 Books Online:
  • Scheduling package execution in SQL Server Agent
  • Creating SQL Server Agent proxies

Properties

Article ID: 918760 - Last Review: July 12, 2013 - Revision: 5.1
Applies to
  • Microsoft SQL Server 2008 Service Pack 1
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2005 Service Pack 3
  • Microsoft SQL Server 2005 Service Pack 2
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems
  • Microsoft SQL Server 2005 Enterprise X64 Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Standard X64 Edition
  • Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems
Keywords: 
kbsqlsetup kbprb kbsql2005ssis kbsql2005setup kbexpertiseinter kbexpertiseadvanced kbtshoot KB918760

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com