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

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9948

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

  • peter.cousins

    SSC Enthusiast

    Points: 174

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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

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

  • Scott Abrants

    SSCommitted

    Points: 1503

    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.

  • Scott Abrants

    SSCommitted

    Points: 1503

    Thanks Peter - handy little script.

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9948

    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!

  • Scott Abrants

    SSCommitted

    Points: 1503

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

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9948

    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?

  • Scott Abrants

    SSCommitted

    Points: 1503

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

  • kenneth.chang

    SSC Rookie

    Points: 41

    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.

  • Scott Abrants

    SSCommitted

    Points: 1503

    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.

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9948

    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.

  • Scott Abrants

    SSCommitted

    Points: 1503

    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.

  • sqlfriend

    SSC Guru

    Points: 52347

    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

  • Phil Parkin

    SSC Guru

    Points: 243779

    Here is a good starter about Environments.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

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

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