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.
In 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.
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
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);
What release of SQL Server does this apply to? Thanks.
Hi Dave – this applies packages deployed to the SSIS catalog in SQL Server 2012 or later.
This worked awesome. Thanks Tim.
Great! Glad to hear it was useful to you.
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.
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.
You’re great! That’s exactly what I needed. Greatly written. Awesome 🙂
Glad it helped! Thanks for the feedback.
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
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.
Your article helped solve the issue I’ve been fighting with for days… Thanks a lot!!
-Xindi
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.
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.
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?
I believe the the last check before we raise error should be based on the status 4. When it is 2 (in progress), 7(success), etc, we cannot raise error. so query should be
IF 4 = (SELECT [status] FROM [SSISDB].[catalog].[operations] WHERE operation_id = @execution_id)
RAISERROR(‘The package failed. Check the SSIS catalog logs for more information’, 16, 1)
The operation_id and execution_id remains same.
Please feel free to suggest, If I missed the point.
Thanks,
Abbas
So glad I ran into this old thread, it was very helpful! However, I do have a question; is there any way to use TSQL in a stored procedure to run an SSIS package which is not stored in the catalog, instead just saved to the file system and SQL Server?
Doug, there’s no easy way to use T-SQL to execute a package stored in the file system. You’d need to use xp_cmdshell to call dtexec.exe, which introduces an additional moving part to the process (as well as the risk of having to enable xp_cmdshell).
Thank you, that is what I thought was going to be the answer.
@Pete Cousins: thanks a million, your script works wonderfully!
I am able to run my (test) package (windows login) where there is no file involved succesfully (just T-SQL statement).
When I run a different (test) package with a flat file connection, I get an error.
The package runs succesfully in debug mode from SSIS.
My windows login user (same me) has access to the directory (and file) and I can see the path as below in file explorer
Error code -1071636466 Cannot open the datafile “\\CC5apps\account\jhaKnow\jhaKnow Project\jhaKNow Administration\SSIS\A_testing_files\ToProcess\testAutomation1.csv”.
Hi Tim,
many thanks for your explanations, this was a great help !
However I am wondering about the benefit of executing packages with T-SQL vs dtexec.
I am actually working on packages that are executed via the dtexec utility (sql server 2016). Does package execution in the catalog is better suited or shall improve performance ?
Thanks !
Fred