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

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.

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.

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.