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
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?