How can I invoke an SSIS package from SQL Agent, via T-SQL, using a proxy

Question

Building an ETL framework with many packages and a "controlfile" table that orchestrates running them in the right order and managing errors. In order to control the overall operation of the data load I would like to do this:

1. Make a job(s) in SQL Agent
2. In the job steps invoke a custom stored procedure
3. In the stored procedure, call SSISDB.catalog.create_execution and related procs to execute a package
4. Run this under a dedicated ETL Windows user account that is NOT a sysadmin and is not the SQL Agent service account, for security reasons.

Everything in steps 1, 2, and 3 works, but if I try to configure the agent job step to run as another account or as a proxy it doesn't work.

Background

Running a single package from an Agent job with the job step type "Integration Services" works, because there's an option to set the step to run as a proxy user. (New Job > Steps > New Step > Type = Integration Services > RUN AS). The problem is that we need the "wrapper" logic to control all these individual packages.

Running a package from a stored procedure directly, from T-SQL, works.

Running a package from a stored procedure in a T-SQL job STEP in Agent, if the job is owned by a sysadmin works, but it runs as the SQL Server Agent Service Account which is a security problem.

Running a package from a stored procedure in a T-SQL job step in Agent, if the job is owned by a NON sysadmin user fails because of a problem in the Agent's EXECUTE AS code, where the SSIS procedures throw an error like 'could not revert' - which leads me to believe there's a problem in the Agent's EXECUTE AS logic running the T-SQL under the job owner account, then calling the SSIS procedures. I have found some information online which I hope I can re-locate an link to.

Running a package from a T-SQL Agent job step has no RUN AS capability to set the code to run under a proxy account. This is a connect item :-).

Running a package from a T-SQL Agent job where I manually put in EXECUTE AS logic in the job step code also doesn't work.

Stumper?

Answer 1

I ended up using a PowerShell step in SQL Agent to work around this: job step type "PowerShell" with a short script, which uses Invoke-Sqlcmd to call the stored procedure. Feels awkward, but it works under the alternate credentials.