How to deploy and execute an SSIS package from the SSISDB catalog

  • Comments posted to this topic are about the item How to deploy and execute an SSIS package from the SSISDB catalog

  • 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);

  • Thanks for the elaborate article, great graphical walk-through.

  • 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 Peter - handy little script.

  • 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!

  • 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).

  • 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?

  • The need access to the SSIS database and ssis_admin to be able to execute a package.

  • 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.

  • 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.

  • 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.

  • 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.

  • 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

  • Here is a good starter about Environments.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

Viewing 15 posts - 1 through 14 (of 14 total)

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