How to execute a SSIS package using some other account instead of SQL Server Agent’s service account?

If you need to execute a SSIS which is scheduled as a SQL job using credential of some other account other than SQL Server Agent’s service account, then please follow below steps:
1] Create a credential using below code:

USE [master]

GO

CREATE CREDENTIAL [Cre_SSIS] WITH IDENTITY = N'domain\account', 

SECRET = N'*****'

GO

Note: Please replace Credential Name with the name of credential you want to us. Also please replace 'domain\account’ with the account name which will execute the SSIS package and replace '*****' with the password of that account.

2] Create proxy with the newly created credential using below code:

USE [msdb]

GO

EXEC msdb.dbo.sp_add_proxy @proxy_name=N'Prox_Name',@credential_name=N'Cre_SSIS', 

@enabled=1, 

@description=N'To run SSIS with Cre_SSIS Credential'

GO

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'Prox_SSIS', @subsystem_id=11

GO

Note: Please replace 'Proxy_Name' with the name you want to use.

3] Use this proxy account to be used with the job which is using SSIS package from SQL Server Management Studio as below:

a) Open Job Properties and Select Step Tab per below screen shot:


b) Double click on the job name (which is actually defining the SSIS to be executed and passing all the required details). Then in the Run As section, select appropriate proxy.
(In this case I am using Proxy_SSIS as the proxy account)


Example code used on a SQL 2008 R2 SP2 Instance:

USE [master]

GO

CREATE CREDENTIAL [Cre_SSIS] WITH IDENTITY = N'REDQUEEN\sqluser1', 

SECRET = N'*****'

GO

Command(s) completed successfully.

USE [msdb]

GO

EXEC msdb.dbo.sp_add_proxy @proxy_name=N'Prox_SSIS',@credential_name=N'Cre_SSIS', 

@enabled=1, 

@description=N'To run SSIS with Cre_SSIS Credential'

GO

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'Prox_SSIS', @subsystem_id=11

GO

Command(s) completed successfully.