Troubleshooting: SSIS Package Execution Using SQL Server Agent (SQL Server Video)

Applies to: Microsoft SQL Server Integration Services

Authors: Carla Sabotta, Microsoft Corporation

Length: 00:12:12 Size: 9.50 MB Type: WMV

Watch this Video on TechNet, to watch, share, and download the video in multiple formats.

Watch this video

Related help topics: An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step dtexec Utility How to: Add a Package Configuration Setting the Protection Level of Packages Using Integration Services Roles

Additional videos: How to: Automate SSIS Package Execution by Using the SQL Server Agent (SQL Server Video)

Video Summary

This video demonstrates how to troubleshoot an SQL Server Integration Services package that doesn't run when you call the package from a SQL Server Agent job step. The package does run successfully outside SQL Server Agent.

Video Transcript

Video Time-Stamp	Audio
00:00	Hello, my name is Carla Sabotta. I write documentation for the Microsoft SQL Server Integration Services product. In this video, I’m going to show you how to troubleshoot a SQL Server Integration Services package that doesn’t run when you call the package from a SQL Server Agent job step. The package does run successfully outside SQL Server Agent. You’ll learn the recommended methods for resolving this issue, including creating a proxy account, modifying the package ProtectionLevel property setting, saving sensitive data in a package configuration file, and storing a package in the SQL Server msdb database. As you can see, this job failed to execute the Integration Services package. When you call a package from a SQL Server Agent job step and the package doesn’t run, one of the following conditions is true: The user account that runs the package as a job step differs from the original package author. —or— The user account does not have the required permissions to make connections or to access resources outside the package When the user account that calls the package from the job step differs from the original package author, the package protection level may prevent the package from running. This is because the user account either cannot decrypt the package or the package-sensitive data, or the user account cannot provide the sensitive data that is missing from the package. Examples of sensitive data are the password part of a connection string, a variable that is marked sensitive, etc. There are a couple of recommended methods for resolving issues with encryption and sensitive data.
01:53	The first method is to store the package in the SQL Server msdb database, and set the protection level to Rely on server storage and roles for access control. You use the Integration Services Service in SQL Server Management Studio to do this. Database roles now control read and write access to the package. You need to assign one of the Integration Services fixed database-level roles or assign a user-defined database-level role, to the Reader role of the package. The fixed database-level roles are db_ssisadmin, db_ssisoperator, and db_ssisltduser. In this demonstration, we’ll assign the db_ssisadmin to the package. If you assign a fixed database-level role to the package, the user account that calls the package from the job step must be a member of that role. If you assign a user-defined role to the package, the user account must be a member of one of the fixed database-level roles and a member of the user-defined role.
03:59	The second method is to change the package ProtectionLevel property setting to EncryptSensitiveWithPassword, in Business Intelligence Development Studio. You access the package ProtectionLevel property by clicking anywhere in the package control flow, and then selecting ProtectionLevel in the Properties window. Next, you modify the SQL Server Agent job step command line to include the password that decrypts the sensitive data. You add the password using the /Decrypt parameter of the dtexec command prompt utility. The SQL Server Agent job steps use the dtexec utility to run packages.
05:22	The third method for resolving issues with encryption and sensitive data, is to change the package ProtectionLevel property setting to DontSaveSensitive, again using Business Intelligence Development Studio. With this property setting, the package is not encrypted and sensitive data is not saved with the package. Therefore, you use a package configuration file to save the data. In this demonstration, we’ll save the password part of a connection string for the DestinationConnectionOLEDB connection manager. When the SQL Server Agent job step runs the package, the sensitive data is loaded from the configuration file that’s been created. Be sure to store the file in a secured folder. So far, we’ve looked at methods for resolving issues with encryption and sensitive data. The other condition that results in an agent job step failing to run a package, concerns user account permissions. The user account does not have the required permissions to make connections or to access resources outside the package.
08:08	To test the prmissions of the user account, you open a command prompt window and execute the RunAs command. Replace mydomain\myuser with the authentication information stored in the account credential. You type the password for the account when prompted. The recommended method for resolving the permissions issue is to create a SQL Server Agent proxy account that has the required permissions. The proxy account also decrypts sensitive data in the package. Keep in mind that this method may fail if you move the package to another computer and the package ProtectionLevel property is set to EncryptSensitiveWithUserKey or EncryptAllWithUserKey.
09:12	To create a proxy account, you must be a member of the sysadmin fixed server role. Or, you must be a member of SQLAgentOperatorRole, SQLAgentReaderRole, or SQLAgentUserRole, in the msdb database. You create a proxy account by running a Transact-SQL query or by using the New Proxy Account dialog box in SQL Server Management Studio. We’ll use the New Proxy Account dialog box. On the General page, you specify the name and credential for the new proxy account. We’ll name the account, Package proxy, and select an existing credential called, User1, that contains the authentication information. Keep in mind that the selected credential must enable SQL Server Agent to run the job as the account that created the package or as an account that has the required permissions. You also need to specify the subsystem for which the proxy is enabled. Because the job is running a package, we’ll select the SQL Server Integration Services Package subsystem. The proxy description is optional. On the Principals page, you can add or remove roles to grant access to the proxy account. Members of the sysadmin fixed server role have automatic access. The User1 credential we specified for the proxy account is listed under the Credentials node in Object Explorer. You can create a new credential by running a Transact-SQL query or by using the New Credentials dialog box. This video demonstrated how to resolve issues with a package that doesn’t run when called from an SQL Server Agent Job Step. The video covered creating a proxy account, modifying the package ProtectionLevel property setting, saving sensitive data in a package configuration file, and storing a package in the SQL Server msdb database. Thank you for watching this video. We hope that you have found this of value, and will return to the Web site to view other Microsoft SQL Server videos.