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:
- Make a job(s) in SQL Agent
- In the job steps invoke a custom stored procedure
- In the stored procedure, call SSISDB.catalog.create_execution and related procs to execute a package
- 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.
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.