How Do I Execute SSIS Catalog Packages As A Different User With TSQL?

Question

We've been moving application packages from the older methods of storage and execution into the SSIS Catalog. We're required to use an enterprise scheduling system instead of the SQL Server Agent to execute batch jobs and SSIS packages. Basically, the scheduler opens a command line on the server and executes Windows commands. In our case, we'd be using SQLCMD to execute a stored procedure like the one below.

Here's what I need to be able to do:

CREATE PROCEDURE usp_Execute_Packages @Package_name NVARCHAR(260) WITH EXECUTE AS 'Domain\User' AS DECLARE @execution_id bigint

EXEC [SSISDB].[catalog].[create_execution] @package_name=@Package_name, @execution_id=@execution_id OUTPUT, @folder_name=N'A Folder Name', @project_name=N'Test Deployment'

EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

But, this doesn't seem to work; I get an error telling me that I can't use a SQL Server account to start an execution. I assume it's having an issue with the EXECUTE AS statement. Any ideas how to start the package execution as a different user without using SQL Server Agent and proxy accounts?

Answer 1

If you create the stored procedure in SSISDB, then add the EXECUTE AS statement within the stored procedure and you can execute the package as the necessary login, which is what it looks like you're aiming to do. As @sepupic noted, adding EXECUTE AS in the header of the SP only applies to database users.

USE SSISDB;
GO

CREATE PROCEDURE usp_Execute_Packages 

AS BEGIN 

EXECUTE AS LOGIN = 'Domain\YourLogin'

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'YourPackage.dtsx',
@execution_id=@execution_id OUTPUT, @folder_name=N'Your Folder', @project_name=N'YourProject', 
@use32bitruntime=False, @reference_id=Null

Select @execution_id

DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50,
@parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0
EXEC [SSISDB].[catalog].[start_execution] @execution_id


END