Run SSIS package asynchronously from SQL Agent job step

  • I'd like to run an SSIS package asynchronously from a SQL Agent job step through the the [SSISDB].[catalog].[create_execution] and [SSISDB].[catalog].[start_execution] stored procedures. I need the target package to be run by a specific login, so that login has been set as the owner of the SQL Agent job.

    However, the job step returns the error:

    Executed as user: ***. The current security context is non-revertible. The "Revert" statement failed. [SQLSTATE 42000] (Error 15196).  The step failed.

    If I use the "SQL Server Integration Services Package" job step type, this runs the package synchronously - which doesn't work for my purpose. The only solution I've come up with is to call a parent SSIS package which calls the stored procedures to fire the package that I want executed asynchronously. This seems like overkill.

    Is there a solution? Thanks.

  • Have you looked at creating a credential and an ssis sql agent proxy then running the step under this proxy

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Thanks for the link. However T-SQL steps cannot be run under a proxy and using a proxy on the SSIS step won't make it run asynchronously.

  • Yes you’re running the package aren’t you or have I misread that

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • You've misread. Running the package (under a proxy) runs it synchronously - not what I want.

    Running the stored procedures runs it asynchronously - but this errors. 

  • The error above does suggest that the package itself is attempting to change execution context which doesn’t fit to well with the sp execution type.

    Any idea what the package is doing?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Nope. Nothing in the package is doing that. The package doesn't even start running.

  • Pete Bishop wrote:

    I'd like to run an SSIS package asynchronously from a SQL Agent job step through the the [SSISDB].[catalog].[create_execution] and [SSISDB].[catalog].[start_execution] stored procedures. I need the target package to be run by a specific login, so that login has been set as the owner of the SQL Agent job.

    However, the job step returns the error:

    Executed as user: ***. The current security context is non-revertible. The "Revert" statement failed. [SQLSTATE 42000] (Error 15196).  The step failed.

    If I use the "SQL Server Integration Services Package" job step type, this runs the package synchronously - which doesn't work for my purpose. The only solution I've come up with is to call a parent SSIS package which calls the stored procedures to fire the package that I want executed asynchronously. This seems like overkill.

    Is there a solution? Thanks.

    have you tried to change the job manually? e.g. script the job and change the syncronize setting manually? while the UI does not give that option, using TSQL may work for you.

    the setting is "$ServerOption::SYNCHRONIZED(Boolean)" - default is True, change it to False

    and as an alternative, do create a SQL Agent Proxy to execute command line steps, and use a standard dtexec to execute the package on the SSIS Catalog - on this you can set the flag to false as well as setting all other environment/variable related bits

     

  • Why does it need to run asynchronously?

    If that is the goal - setup an agent job to run the package using the Integration Services subsystem with the appropriate proxy account - then in your 'other' job or process issue sp_start_job when you want the SSIS package to start running.  That will kick off the agent job and allow your 'other' job/process to continue on to the next step without waiting for the package to complete.

     

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • If doing it the way you wish check the user owning the job has permission to the spa in ssis catalog

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Thanks frederico_fonseca. That was exactly what I needed. Annoying that this still isn't in the GUI, even though it adds the parameter (with true) to the command line when scripting the job.

    Perry Whittle - Yes, the job was fully permissioned.

    Jeffrey Williams - I need to be able to run multiple instances of the package at the same time but controlled from a SQL Agent job. Your solution wouldn't work since the sub-job would still be running the first instance of the package when called again by the parent-job. I know I didn't include the parallel running requirement.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply