Tim Mitchell
Follow Tim Mitchell on Twitter  Like Tim Mitchell on Facebook  Subscribe on YouTube  Connect on LinkedIn  Subscribe to the Data Geek Newsletter

A Better Way to Execute SSIS Packages with T-SQL

There are several ways to execute SSIS packages that have been deployed to the SSIS catalog, and my favorite way of performing this task is to execute SSIS packages with T-SQL. There are a couple of challenges that come up when executing catalog-deployed packages using the default settings in T-SQL, but I have workaround for those issues which I’ll cover in this post.

Execute SSIS Packages with T-SQLIn this post I will demonstrate a couple of my favorite tricks for improving the functionality and output of SSIS catalog package executions invoked from T-SQL.

Execute SSIS Packages with T-SQL

To quickly review, there are two stored procedures used to execute catalog-stored SSIS packages:

  • catalog.create_execution: This stored procedure is used to set up the execution in the SSIS catalog, but doesn’t actually execute the package. This initial creation step allows us to set up parameters or add data taps before the execution begins. This stored procedure has a BIGINT output parameter named @execution_id that is used as a handle for other pre-execution steps and the actual execution of the package. Had I been designing this system, I would have configured this to be the default behavior. It’s a rare case that I actually need to make an asynchronous call to [catalog].[start_execution]. From a workflow perspective, I almost always need the script to run until the package is finished.
  • catalog.start_execution: This stored procedure kicks off the execution in the SSIS catalog. The execution ID to be started is the one returned in the @execution_id output parameter from catalog.create_execution above.

Used together, these stored procedures manage the creation and execution of catalog executions, and they work quite well. As shown below, the create_execution stored procedure is executed, followed by start_execution:

However, there are a couple of challenges presented when using this pattern:

The call to [catalog].[start_execution] is asynchronous by default. What this means is that when you execute a package using this stored procedure, the stored procedure runs just long enough to kick off the execution, and will return almost immediately. If the package runs for several minutes or hours, the stored procedure still returns as soon as the execution begins. Although there are cases where this might be the desired behavior, an asynchronous execution is usually the exception.

The call to [catalog].[start_execution] succeeds even if the package fails. As long as there are no syntactical or structural issues – such as an invalid execution ID, a required parameter that is not specified, or insufficient permissions on the package – the execution of catalog.start_execution will succeed. As noted above, there are cases where this might be the intended behavior, but this design is not common.

Fortunately, these issues are not show-stoppers. There are workarounds for each of these to improve the program flow of SSIS execution.

A Better Way

For the majority of the ETL processes I develop, I use the following methods to work around the limitations noted above.

Execute Packages Synchronously

This one is a relatively easy fix. Although the default behavior is to execute [catalog].[start_execution] asynchronously, there is a system parameter that lets you override this behavior on a per-execution basis. The parameter name is SYNCHRONIZED, and it expects a value of either 0 (not synchronized, the default behavior) or 1 (synchronized). When overriding this value and setting it to 1, the call to [catalog].[start_execution] will not return until the package has completed its execution. This means that [catalog].[start_execution] will run as long as the package is still running. The syntax to add this parameter is shown below.

As shown, parameter values (including system, project, and package parameters) are added after [catalog].[create_execution] and before [catalog].[start_execution]. In the above example I set the SYNCHRONIZED system parameter to 1, forcing [catalog].[start_execution] to wait until the package completes before it returns.

This override solves one problem but leaves another: Even though [catalog].[start_execution] now runs synchronously, it does not report the status of the package execution. Most workflows need to know the status of the package execution, not just that the execution has finished. Fortunately, there is a workaround for that as well.

Force a Script Failure if the Package Fails

This workaround requires more than a simple execution parameter, so it has more moving parts. In a nutshell, here is the design pattern for forcing the T-SQL script to fail in the event that the package it has called fails:

  • Create the execution
  • Set the SYNCHRONIZED parameter to 1
  • Execute the package
  • Check the execution log for this execution, and force the T-SQL script to fail if the package failed

The previous script already took care of steps 1-3, so we just need to add the final step. This can be as simple or as complicated as needed, but in my example I just check [catalog].[executions] and retrieve the [status] value for that execution; if the value does not equal 7 (the status ID for Success), the script will generate a failure using RAISERROR.

Conclusion

Using T-SQL stored procedures is the most flexible way to execute catalog-deployed SSIS packages, but it does have some limitations. In this post I have demonstrated how to work around the issue of synchronicity and status reporting when executing catalog packages from T-SQL.

About the Author

Tim Mitchell
Tim Mitchell is a business intelligence and SSIS consultant who specializes in getting rid of data pain points. Need help with data warehousing, ETL, reporting, or SSIS training? Contact Tim here: TimMitchell.net/contact

20 Comments on "A Better Way to Execute SSIS Packages with T-SQL"

  1. Hi Tim,
    This method requires us to use Windows Authentication. I have a workflow automation tool that currently doesn’t support windows auth yet, do we have any workaround here.
    I created a sql agent job and invoking the job/step using msdb procedures.

    TIA, Kiran

  2. I’ve written a stored procedure that enables execution of SSIS packages. We’ve had running without issue in production for over a year. In case it’s of use to anyone else:

    CREATE PROC [dbo].[rsp_ExecuteSSISPackage]
    (
    @SSISFolder sysname ,
    @SSISProjectName NVARCHAR(128) ,
    @PackageName NVARCHAR(255) ,
    @EnvironmentName sysname = ” ,
    @32Bit BIT = False ,
    @Logging_Level TINYINT = 1
    )
    AS
    DECLARE @packageexecution_id BIGINT;
    DECLARE @status INT= 1;
    DECLARE @Event_Message_id BIGINT= 0;
    DECLARE @Last_Event_Message_id BIGINT= 0;
    DECLARE @message_time DATETIME2(7);
    DECLARE @message NVARCHAR(MAX);
    DECLARE @ReferenceID BIGINT = NULL;
    DECLARE @PackageFileName NVARCHAR(260);
    DECLARE @ReturnCode BIGINT;
    — Written by Pete Cousins

    RAISERROR(‘Executing on server %s’,0,0,@@ServerName) WITH NOWAIT;
    –Look up Environment ID if relevant
    IF @EnvironmentName ”
    BEGIN
    RAISERROR(‘Looking up %s environment in folder %s’,0,0,@EnvironmentName,@SSISFolder) WITH NOWAIT;
    SELECT @ReferenceID = reference_id
    FROM SSISDB.[catalog].environment_references er
    JOIN SSISDB.[catalog].projects p ON p.project_id = er.project_id
    WHERE er.environment_name = @EnvironmentName
    AND p.name = @SSISProjectName;

    IF @@rowcount = 0 –We could not find it. Abort and tell the operator
    BEGIN
    RAISERROR(‘Environment %s not found in Project %s’,16,1,@EnvironmentName,@SSISProjectName);
    RETURN;
    END;
    ELSE
    RAISERROR(‘Found Reference_id %I64d for environment’,0,0,@ReferenceID) WITH NOWAIT;
    END;

    SET @PackageFileName = @PackageName + ‘.dtsx’;

    SET @message = ‘Getting an Application lock to work round MS SQL bug at ‘
    + CONVERT(VARCHAR(30), GETDATE(), 113);
    RAISERROR(@message,0,0 ) WITH NOWAIT;
    BEGIN TRAN;
    EXEC @ReturnCode= sp_getapplock @Resource = ‘SSISCreateExecution’,
    @LockMode = ‘Exclusive’;
    IF @ReturnCode = 1
    RAISERROR(‘I had to wait to get the lock!!’,0,0) WITH NOWAIT;
    SET @message = ‘Got the lock at ‘ + CONVERT(VARCHAR(30), GETDATE(), 113);
    RAISERROR(@message,0,0 ) WITH NOWAIT;

    RAISERROR(‘Creating package execution for package %s’,0,0,@PackageFileName) WITH NOWAIT;
    –Create a SSIS execution for the required SSIS package and return the execution_id
    EXEC [SSISDB].[catalog].[create_execution] @package_name = @PackageFileName,
    @execution_id = @packageexecution_id OUTPUT,
    @folder_name = @SSISFolder, @project_name = @SSISProjectName,
    @use32bitruntime = @32bit, @reference_id = @ReferenceID;
    –Set the logging level 0-none, 1-basic (recommended), 2-performance, 3-verbose
    RAISERROR(‘Setting Logging level to %i’,0,0,@Logging_Level) WITH NOWAIT;
    EXEC [SSISDB].[catalog].[set_execution_parameter_value] @packageexecution_id,
    @object_type = 50, @parameter_name = N’LOGGING_LEVEL’,
    @parameter_value = @Logging_Level;

    RAISERROR(‘Releasing Application lock’,0,0) WITH NOWAIT;
    EXEC sp_releaseapplock @Resource = ‘SSISCreateExecution’;
    COMMIT TRAN;
    RAISERROR(‘Starting SSIS package %s with execution_id %I64d on server %s’,0,0,@PackageFileName,@packageexecution_id,@@SERVERNAME) WITH NOWAIT;

    –Start the package executing
    EXEC [SSISDB].[catalog].[start_execution] @packageexecution_id;

    WHILE @status IN ( 1, 2, 5, 8 ) –created (1), running (2), canceled (3), failed (4), pending (5), ended unexpectedly (6), succeeded (7), stopping (8), and completed (9).
    BEGIN

    WAITFOR DELAY ’00:00:05′;
    –Get the status to see later if we’ve finished
    SELECT @status = status
    FROM SSISDB.catalog.executions
    WHERE execution_id = @packageexecution_id;
    –Are there any event messages since we last reported any?
    DECLARE curEventMessages CURSOR FAST_FORWARD
    FOR
    SELECT event_message_id ,
    message_time ,
    message
    FROM SSISDB.catalog.event_messages
    WHERE operation_id = @packageexecution_id
    AND event_message_id > @Last_Event_Message_id;
    OPEN curEventMessages;
    FETCH NEXT FROM curEventMessages INTO @Event_Message_id,
    @message_time, @message;
    WHILE @@FETCH_STATUS = 0
    BEGIN
    –We have found a message, so display it – watch out for % signs in the message, they will cause an error if we don’t replace them
    SET @message = CONVERT(NVARCHAR(MAX), @message_time, 113)
    + ‘ ‘ + REPLACE(@message, ‘%’, ‘ percent’);
    RAISERROR(@message,0,0) WITH NOWAIT;
    SET @Last_Event_Message_id = @Event_Message_id; –Make a note that we’ve reported this message
    FETCH NEXT FROM curEventMessages INTO @Event_Message_id,
    @message_time, @message;
    END;
    CLOSE curEventMessages;
    DEALLOCATE curEventMessages;
    END;
    –@Status indicates that package execution has finished, so let us look at the outcome, and error if there is a problem
    IF @status = 7
    RAISERROR(‘Package Succeeded’,0,0);
    ELSE
    IF @status = 9
    RAISERROR(‘Package completed’,0,0);
    ELSE
    IF @status = 3
    RAISERROR(‘Package Cancelled’,16,1);
    ELSE
    IF @status = 4
    RAISERROR(‘Package failed (see error message above)’,16,1);
    ELSE
    IF @status = 6
    RAISERROR(‘Package ended unexpectedly’,16,1);
    ELSE
    RAISERROR(‘Package ended with unknown status %i’,16,1,@status);

  3. What release of SQL Server does this apply to? Thanks.

  4. This worked awesome. Thanks Tim.

  5. Hi Tim,
    We try to execute stored procedure with execute as ‘domain\username’, We are unable to execute create_execution, It returns error message as ‘The Server Principal ‘domain\username’ is not able to access the database ‘SSISDB’ under the current security context’. We have provided ‘domain\username’ with sysadmin access, then also it doesn’t work.

    • Vinod, this could be related to one of several issues: invalid proxy, user account not having access to the catalog (which is granted separately from SSISDB database access) or possibly even a Kerberos issue.

  6. Whenever I try to execute the package via TSQL, I get “Cannot access the package or the package does not exist. Verify that the package exists and that the user has permissions to it.” I can see the package and can execute it from the GUI. Any ideas? I’ve been struggling with this for a while. Any help would be appreciated. Thank you.

    • Nancy, it’s most likely an error in defining where the package exists. I recommend creating your execution script from the UI by opening the Execute Package dialog in SSMS and using the Script button to generate T-SQL.

  7. Reto Wietlisbach | October 13, 2017 at 2:56 am | Reply

    You’re great! That’s exactly what I needed. Greatly written. Awesome 🙂

  8. Thank you for this article. I was able to create the stored procedure I needed to execute my SSIS package and process the data retrieved. It runs perfectly when I’m logged in to the database and execute the stored procedure. My account is a Windows Administrator account, and I have sa rights on the Server. I made sure the account is set as a member of SSIS_Admin and as owner of SSISDB. Both the Database associated with the stored procedure and SSISDB are on the same server. I don’t know if I have to do anything to make this work across two databases.

    Although this may be out of the scope for this article, I’m hoping you can help, or at least point me to the best place to get help. Google hasn’t provided a good answer so far.

    I created an ASP.Net web page where clicking on a button should execute the stored procedure that runs this SSIS package. However, I get the following error right after clicking the button.

    “The server principal \”WORKGROUP\\MYSERVERNAME$\” is not able to access the database \”SSISDB\” under the current security context.”

    I hope you can point me to the right place to get help for this or find an answer.

    Thanks1

  9. I have an issue that extends beyond this just a bit . . . . My stored procedure and SSIS package run well together when I run them from SQL thanks to the guidance above. However, when I try to execute the stored procedure from an ASP.Net web page when a user clicks a button, I get an error that the user (even me, and I am sys admin on the server and sa on the SQL instance and SSISDB_Admin) ‘The Server Principal ‘domain\username’ is not able to access the database ‘SSISDB’ under the current security context’. What’s happening, I believe, is that ASP.Net is running under a different account – LocalSystem – within the Default App Pool of IIS8. It would appear that SQL is assuming the LocalSystem is the current user and is generating this error because LocalSystem isn’t a user on the SQL Server instance. When I use my personal account as the principal login to the Default APP Pool, the ASP.net page runs successfully. I just can’t leave it like that.

    I totally understand this isn’t quite the subject of this article. i’m grateful that the information above got me most of the way where I need to be, but it occurs to me that this is an important next step – to get this process to run from a Web page. Perhaps this next step can be the subject of a new article.

    • Hi Karen, it looks like you’ve identified the root of the failure, that the account used by ASP.NET does not have permissions to execute the SSIS catalog stored procedures. To execute this from a web page (or any application, for that matter), the stored procedure would need to be invoked from an account that has permissions on SSISDB as well as the databases accessed by the SSIS packages. This is one of the potential topics on my list for future blog posts.

  10. Your article helped solve the issue I’ve been fighting with for days… Thanks a lot!!
    -Xindi

  11. Worked perfectly, thanks. Interestingly , there is not on either https://docs.microsoft.com/en-us/sql/integration-services/system-stored-procedures/catalog-set-execution-parameter-value-ssisdb-database?view=sql-server-2017 or the create execution URL. This is very important feature to be able to run syncronously.
    Great Post.

  12. Hi Tim,
    Did you dig the save scenario, but with Service Broker as caller?
    Thank you.

    • Peter, I’ve not used this with Service Broker, but I don’t know of any reason it wouldn’t work. It’s just a T-SQL call, so any service that has permissions to run those SSISDB stored procedures could kick off a package execution.

  13. When executing [SSISDB].[catalog].[set_execution_parameter_value] I am receiving the following error.

    Msg 27176, Level 16, State 1, Procedure set_execution_parameter_value, Line 149 [Batch Start Line 2]
    The parameter ‘pPeriodEndDate’ does not exist or you do not have sufficient permissions.

    I’m running this as as sysadmin. How do I give permissions to set the value? Has anyone else experienced this error?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: