Run SSIS 2012 package in Integration Services Catalog from Sql Agent job with proxy account.

Problem

An Integration Services 2012 package that reads/writes files in a shared folder has to be executed under a windows account with sufficient access rights. The package is deployed with VS2012 in the Integration Services Catalogs. The package has a production and a test environment. It will be scheduled in a Sql Server Agent job. The windows account will need sufficient rights to run the package from the Integration Services Catalog. In this blog article the steps are presented necessary to grant rights to run the package by a Sql Server Agent proxy.

Solution

Prerequisites

The creation of proxy user was already necessary for SSIS 2008 packages with similar functionality, so I do not go into too much detail

• On the Sql server that runs the job
• In Security / Logins the windows account is added.
• In Security / Credentials, a new credential is added with this account and password.
  
• In SQL server Agent / Proxies a new proxy is added, with the Credential and active to the subsystem ‘Sql Server Integration Services Package’.

Grant the proxy rights to execute the package

On the SQL server where the package is deployed, open the Integration Services Catalogs, SSISDB and right click on the projectfolder where the package is stored. Select properties

In the folder properties dialog window, in the select a page pane, select Permissions. Add the windows user account and check the Grant, Execute Objects and Read Objects permissions.

The Read Objects permission is necessary to read the Environment variables in the Environments folder.

Create the SQL server Agent job

In the job Steps add a new step that will execute the SSIS package

• Step name: Import files
• Type: SQL Server Integration Service Package
• Run as: prxSSIS (the name of the proxy
• Package source: SSIS Catalog
• Server: the SSIS catalog server where the Project was deployed
• Package: pathname inside the SSIS catalog, e.g. \SSISDB\FileImportExport\SSIS_A..\FileImport.dtsx
• In the configuration Tab: check the Environment and select the necessary environment e.g. Production (the name given in the environments folder of the SSIS catalog)