May 23, 2016 at 9:10 pm
Comments posted to this topic are about the item How to deploy and execute an SSIS package from the SSISDB catalog
May 24, 2016 at 2:57 am
Thought I'd post this stored procedure in case it's of any use to others. I wrote it to execute a SSIS package synchronously from transact-sql, and use it widely in our enterprise scheduler http://www.jamsscheduler.com/. Note there's a bug in the MS code for creating the package execution which can cause deadlocks if many are being created simultaneously - hence the addition of an application lock in my stored procedure.
If anyone can think of a way of getting rid of the cursor.....
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;
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);
May 24, 2016 at 6:32 am
Thanks for the elaborate article, great graphical walk-through.
May 24, 2016 at 7:07 am
Remember in-order to execute the script that executes the SSIS package you need to be logged into that SQL Server as a Window's Account - SQL Server accounts do no have permissions to do so.
May 24, 2016 at 7:08 am
Thanks Peter - handy little script.
May 24, 2016 at 9:28 am
Scott Abrants (5/24/2016)
Remember in-order to execute the script that executes the SSIS package you need to be logged into that SQL Server as a Window's Account - SQL Server accounts do no have permissions to do so.
Thanks for that tidbit. This means that any Windows user can execute them. Just what I needed to know!
May 24, 2016 at 9:31 am
Well not exactly the windows user must have proper permissions defined on that server, it simply means that SQL Server users cannot execute packages (simply).
May 24, 2016 at 9:45 am
Scott Abrants (5/24/2016)
Well not exactly the windows user must have proper permissions defined on that server, it simply means that SQL Server users cannot execute packages (simply).
What permissions do the windows users require?
May 24, 2016 at 10:35 am
The need access to the SSIS database and ssis_admin to be able to execute a package.
May 24, 2016 at 2:09 pm
can you do a second part and show us how to use the "Environments" folder?
If, as in your example, everything is approved in the TestServer, you want to move to the Production server, do you change the connection manager to point to Production server, or you just move the package unchanged, and let Environments handle it.
May 24, 2016 at 2:19 pm
Kenneth,
It really depends on what you want to do. If you know that you have lets say 2 environments DEV and PROD then you can configure all of your settings for each. For example have a connection string setting in both and have them pointed to the correct values before deployment of the package (hopefully this is stored in some kind of source control repo). Then when you execute the package you can specify the environment to use dynamically and not have to worry about changing the values for each connection string during deployment. Still I agree with you a great idea for a follow on post.
May 24, 2016 at 5:32 pm
To tell you the truth, I write articles about things I am trying to learn. Thanks for the suggestion. If I can figure out how to use the environment, I will write an article about it.
May 25, 2016 at 7:08 am
I am the same way Stan and from what I can tell there is an audience for the environment discussion.
Who knows I might dust off my writing skills and see how I can do with it.
June 21, 2016 at 12:17 pm
I have an issue to execute the package in SSMS SSISDB catalog, it failed to open UNC path file share.
I searched and some said the server has to be setup as kerboros authentication.
NCLM authentication will not work.
Do you all do that?
And what SPN you set up for the server and user and services?
Thanks
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply