How to protect your stored procedures against multiple concurrent executions (SQL Server Series)

Last update: 5 October, 2016: added examples of using sp_getapplock and sp_releaseapplock.

Introduction

I ran into this problem lately. I had a stored procedure that did some global stuff for a database, it would do the exact same thing every time you executed it. It had no parameters.
It would at the best case be useless and use unnecessary server resources when executed multiple times concurrently, and in the worst case would cause deadlocks in that situation.
And this is exactly what this post is about: a stored procedure that is not supposed to be executed multiple times concurrently.
Examples are stored procedures that are purging a database log, updating derived tables or columns, etc.

Problem

You can start a stored procedure, and before it is finished, can start it again from a different connection.

Solutions

There are (at least) two possible solutions for this problem.

  1. Use sp_getapplock and sp_releaseapplock (the preferred solution). My credits go to Andy Novick who has published about this before on MSSQLTips. I have added examples of using ‘Session’ as lock owner, and therefore I hope that my article will still have added value.
  2. My initial solution using dynamic management views is still in this article for reference.

Using sp_getapplock and sp_releaseapplock

How it works

For your convenience the links to MSDN for help on those two system stored procedures:
MSDN page about sp_getapplock
MSDN page about sp_releaseapplock

Important to understand is that as @LockOwner you can either use a transaction or a session.
I have included two examples for both types of lockowner.

Using ‘Transaction’ as lock owner

In this stored procedure example (of which you can download the script) sp_getapplock is used with @LockOwner = ‘Transaction’.
When you use this example, it is really important to use the TRY .. CATCH code so that the lock is always released before the stored procedure execution ends.

sp_StoredProcUsingAppLock.sql

-------------- [sp].[StoredProcUsingAppLock] --------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[sp].[StoredProcUsingAppLock]') AND type in (N'P', N'PC'))
  DROP PROCEDURE [sp].[StoredProcUsingAppLock]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
==========================================================================================
Author: Hans Michiels
Create date: 5-oct-2016
Description: Example of stored procedure that is using sp_getapplock by using a transaction.
==========================================================================================
*/

CREATE PROCEDURE [sp].[StoredProcUsingAppLock]
(
  @RaiseError BIT = 0
)
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @RC INT
  DECLARE @message VARCHAR(500)

  BEGIN TRY

    --\
    ---) Protection Against Concurrent Executions
    --/
    BEGIN TRAN

    SELECT @message = CONVERT(VARCHAR(30), GETDATE(), 121) + ': Try to obtain a lock ..'
    RAISERROR(@message, 0, 1) WITH NOWAIT;

    EXEC @RC = sp_getapplock
        @Resource = 'StoredProcUsingAppLock',
        @LockMode = 'Exclusive',
        @LockOwner = 'Transaction',
        @LockTimeout = 60000 -- 1 minute

    IF @RC < 0
    BEGIN
        IF @@TRANCOUNT > 0 ROLLBACK TRAN;
        SELECT @message = CONVERT(VARCHAR(30), GETDATE(), 121) + ': Sorry, could not obtain a lock within the timeout period, return code was ' + CONVERT(VARCHAR(30), @RC) + '.'
        RAISERROR(@message, 0, 1) WITH NOWAIT;
        RETURN @RC
    END ELSE BEGIN
        SELECT @message = CONVERT(VARCHAR(30), GETDATE(), 121) + ': AppLock obtained ..'
        RAISERROR(@message, 0, 1) WITH NOWAIT;
    END
  
    --\
    ---) Stored procedure body
    --/
    -- Wait so that stored procedure has some considerable execution time.
    SELECT @message = CONVERT(VARCHAR(30), GETDATE(), 121) + ': Hello World!'
    RAISERROR(@message, 0, 1) WITH NOWAIT;

    DECLARE @i INT = 0

    WHILE @i < 6
    BEGIN
        WAITFOR DELAY '00:00:01';
        EXEC [sp].[SubStoredProc];
        SET @i = @i + 1
    END

    IF @RaiseError = 1
    BEGIN
        RAISERROR('An error on demand', 16, 1);
    END

    SELECT @message = CONVERT(VARCHAR(30), GETDATE(), 121) + ': Goodbye ..'
    RAISERROR(@message, 0, 1) WITH NOWAIT;

    COMMIT TRAN
    SELECT @message = CONVERT(VARCHAR(30), GETDATE(), 121) + ': Transaction committed, appLock released ..'
    RAISERROR(@message, 0, 1) WITH NOWAIT;

  END TRY
  BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRAN;
    SELECT @message = CONVERT(VARCHAR(30), GETDATE(), 121) + ': Transaction rolled back, appLock released ..'
    RAISERROR(@message, 0, 1) WITH NOWAIT;
    THROW;
  END CATCH
END
GO

When executed ..

I would not be me if I did not include a test and a proof that it works.

As you can see in the printscreens below, all executions as nicely after each other ..
mssql-e05-354
mssql-e05-356
mssql-e05-355
mssql-e05-357

As you can see in the printscreens below, this even works well, when an error occurs, due to the TRY .. CATCH block ..
mssql-e05-361
mssql-e05-358
mssql-e05-360
mssql-e05-359

Using ‘Session’ as lock owner

In this stored procedure example (of which you can download the script) sp_getapplock is used with @LockOwner = ‘Session’.
An “advantage” of using the session might be that you can also use it, when you do not want to use a transaction within your stored procedure.
As with the previous example, now also it is really important to use the TRY .. CATCH code so that the lock is always released before the stored procedure execution ends.

demo_setup_sp_StoredProcUsingAppLockNoTran.sql
-------------- [sp].[StoredProcUsingAppLockNoTran] --------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[sp].[StoredProcUsingAppLockNoTran]') AND type in (N'P', N'PC'))
  DROP PROCEDURE [sp].[StoredProcUsingAppLockNoTran]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
==========================================================================================
Author: Hans Michiels
Create date: 5-oct-2016
Description: Example of stored procedure that is using sp_getapplock without using a transaction.
==========================================================================================
*/

CREATE PROCEDURE [sp].[StoredProcUsingAppLockNoTran]
(
  @RaiseError BIT = 0
)
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @RC INT
  DECLARE @message VARCHAR(500)

  BEGIN TRY

    --\
    ---) Protection Against Concurrent Executions
    --/
    SELECT @message = CONVERT(VARCHAR(30), GETDATE(), 121) + ': Try to obtain a lock ..'
    RAISERROR(@message, 0, 1) WITH NOWAIT;

    EXEC @RC = sp_getapplock
        @Resource = 'StoredProcUsingAppLockNoTran',
        @LockMode = 'Exclusive',
        @LockOwner = 'Session',
        @LockTimeout = 60000 -- 1 minute

    IF @RC < 0
    BEGIN
        SELECT @message = CONVERT(VARCHAR(30), GETDATE(), 121) + ': Sorry, could not obtain a lock within the timeout period, return code was ' + CONVERT(VARCHAR(30), @RC) + '.'
        RAISERROR(@message, 0, 1) WITH NOWAIT;
        RETURN @RC
    END ELSE BEGIN
        SELECT @message = CONVERT(VARCHAR(30), GETDATE(), 121) + ': AppLock obtained ..'
        RAISERROR(@message, 0, 1) WITH NOWAIT;
    END
  
    --\
    ---) Stored procedure body
    --/
    -- Wait so that stored procedure has some considerable execution time.
    SELECT @message = CONVERT(VARCHAR(30), GETDATE(), 121) + ': Hello World!'
    RAISERROR(@message, 0, 1) WITH NOWAIT;

    DECLARE @i INT = 0

    WHILE @i < 6
    BEGIN
        WAITFOR DELAY '00:00:01';
        EXEC [sp].[SubStoredProc];
        SET @i = @i + 1
    END

    IF @RaiseError = 1
    BEGIN
        RAISERROR('An error on demand', 16, 1);
    END

    SELECT @message = CONVERT(VARCHAR(30), GETDATE(), 121) + ': Goodbye ..'
    RAISERROR(@message, 0, 1) WITH NOWAIT;

    EXEC @RC = sp_releaseapplock @Resource='StoredProcUsingAppLockNoTran', @LockOwner='Session';
    SELECT @message = CONVERT(VARCHAR(30), GETDATE(), 121) + ': AppLock released ..'
    RAISERROR(@message, 0, 1) WITH NOWAIT;

  END TRY
  BEGIN CATCH
    EXEC @RC = sp_releaseapplock @Resource='StoredProcUsingAppLockNoTran', @LockOwner='Session';
    SELECT @message = CONVERT(VARCHAR(30), GETDATE(), 121) + ': AppLock released after error ..'
    RAISERROR(@message, 0, 1) WITH NOWAIT;
    THROW;
  END CATCH
END
GO

When executed ..

As you can see in the printscreens below, all executions as nicely after each other ..
mssql-e05-363
mssql-e05-362
mssql-e05-365
mssql-e05-364

As you can see in the printscreens below, this even works well, when an error occurs, due to the TRY .. CATCH block ..
mssql-e05-367
mssql-e05-366
mssql-e05-368
mssql-e05-369

Other considerations

As you might have noticed, this only worked because the timeout was 60 seconds, and all the executions could be completed within those 60 seconds. If this would not be the case, the stored procedure execution that was waiting to obtain the applock would time out and would bail out due to a RETURN statement.
So you might play a bit with the timeout time to influence this behavior.
If you do not care that the non-first executions bail out, you could set the time out to a smaller value, e.g. 5000 (milliseconds).
If you absolutely want all executions to continue after waiting, you could set the time out to a higher value, that is above the maximum execution time of the stored procedure.
In this way you can choose to let non-first executions wait or not, as could be achieved by using the @WaitIfExecuted parameter in my initial solution.

My initial solution using dynamic management views

So if you want to create a stored procedure that is Protected Against Concurrent Executions, you have to do it yourself.
Making a PACE stored procedure is not very difficult. There is however one big pitfall. The examples on the internet I found all have nasty bug in it, at least when your stored procedure executes other stored procedures: when this happens, the “main” stored procedure goes off the radar, when the dynamic management objects sys.dm_exec_requests and sys.dm_exec_sql_text() are used, that means, you cannot detect that it is running as long as the “sub” stored procedure is being executed.
To make the challenge even bigger, the object_id of the “main” stored procedure is nowhere, so with the available dynamic management objects there is no way to solve this.
Therefore an extra “user” dynamic management table is needed, I called it [udm_storedproc_executions]. It is automaticly cleaned up in the stored procedure [sp].[GetPace].
This stored procedure is used to implement Protection Against Concurrent Executions.
You execute it at the beginning in stored procedures that you want to protect against concurrent executions.

How it works

  • You create the table [dbo].[udm_storedproc_executions] (or name it differently) in your database.
  • You create the stored procedure [sp].[GetPace] (or name it differently) in your database.
  • You add the code snippet below to stored procedures that are not supposed to run multiple times concurrently.

Create the table

udm_storedproc_executions.sql
/*
(c) Copyright 2016 - hansmichiels.com
 
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.
 
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
 
You should have received a copy of the GNU General Public License
along with this program. If not, see .
*/

IF OBJECT_ID('[dbo].[udm_storedproc_executions]', 'U') IS NOT NULL
   DROP TABLE [dbo].[udm_storedproc_executions];
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO

CREATE TABLE [dbo].[udm_storedproc_executions](
 [start_time] [datetime] NOT NULL,
 [session_id] [smallint] NOT NULL,
 [connection_id] [uniqueidentifier] NOT NULL,
 [task_address] [varbinary](8) NOT NULL,
 [object_id] [int] NOT NULL,
  CONSTRAINT [PK_dbo_udm_storedproc_executions] PRIMARY KEY CLUSTERED
( [start_time] ASC,
  [session_id] ASC,
  [connection_id] ASC,
  [task_address] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

Create the stored procedure

sp_GetPace.sql
/*
(c) Copyright 2016 - hansmichiels.com
 
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.
 
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
 
You should have received a copy of the GNU General Public License
along with this program. If not, see .
*/

-------------- [sp].[GetPace] --------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[sp].[GetPace]') AND type in (N'P', N'PC'))
  DROP PROCEDURE [sp].[GetPace]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
==========================================================================================
Author: Hans Michiels
Create date: 17-sep-2016
Description: Generic stored procedure that can give a different stored procedure
             PACE (Protection Against Concurrent Execution).
==========================================================================================
*/

CREATE PROCEDURE [sp].[GetPace]
(
  @StoredProcedureName NVARCHAR(256), -- Quoted name including schema, e.g. [dbo].[SomeStoredProcedure]
  @WaitIfExecuted BIT = 0, -- Indication whether to wait when the stored procedure is already executing (and execute it afterwards).
  @PaceAction VARCHAR(8) OUTPUT, -- Domain values [ CONTINUE | WAIT | RETURN ]
  @InsertDmRow BIT = 0, -- Indication if a row must be inserted to table [dbo].[udm_storedproc_executions]
  @EnableDebugMessages BIT = 0 -- Speaks for itself
)
AS
BEGIN
  SET NOCOUNT ON;

  --\
  ---) Declarations.
  --/
  DECLARE @SpidThatWins INT
  DECLARE @WaitTime CHAR(8) = '00:00:03'
  DECLARE @message NVARCHAR(500)

  --\
  ---) Insert new row into table [dbo].[udm_storedproc_executions].
  --/
  IF @InsertDmRow = 1
  BEGIN
      INSERT INTO [dbo].[udm_storedproc_executions]
        SELECT
            der.[start_time],
            der.[session_id],
            der.[connection_id],
            der.[task_address],
            OBJECT_ID(@StoredProcedureName, 'P') AS [object_id]
        FROM
            sys.dm_exec_requests der
        CROSS APPLY
            sys.dm_exec_sql_text(der.sql_handle) xst
        WHERE
            der.session_id = @@SPID
            AND der.[sql_handle] is not null
            AND xst.objectid = OBJECT_ID('[sp].[GetPace]', 'P');
  END

  -- We need to prevent that this stored procedure would be executed multiple times
  -- simultaneously.
  SELECT @SpidThatWins = -- The one that first started. If started at exact the same time, the lowest SPID wins.
    (
    SELECT TOP 1 der.[session_id]
    FROM
        sys.dm_exec_requests der
    JOIN
        dbo.udm_storedproc_executions spx
        ON spx.[session_id] = der.[session_id]
        AND spx.[start_time] = der.[start_time]
        AND spx.[connection_id] = der.[connection_id]
        AND spx.[task_address] = der.[task_address]
    WHERE
        der.[sql_handle] is not null
        AND spx.[object_id] = OBJECT_ID(@StoredProcedureName, 'P')
    ORDER BY
        der.[start_time] ASC,
        der.[session_id] ASC
    )

  SELECT @PaceAction =
  CASE
    WHEN @@SPID = @SpidThatWins THEN 'CONTINUE'
    WHEN @WaitIfExecuted = 0 THEN 'RETURN'
    WHEN @WaitIfExecuted = 1 THEN 'WAIT'
  END

  IF @PaceAction = 'WAIT'
  BEGIN
      -- Still executing, so wait until I can start.
      IF @EnableDebugMessages = 1
      BEGIN
          -- Still executing, so wait until I can start.
          SET @message = @StoredProcedureName + N' is waiting for other execution to finish .. ';
          RAISERROR(@message, 0, 1) WITH NOWAIT;
      END
      WAITFOR DELAY @WaitTime
  END
  IF @PaceAction = 'RETURN' AND @EnableDebugMessages = 1
  BEGIN
      -- Still executing, so wait until I can start.
      SET @message = N'Cancelling execution of stored proc ' + @StoredProcedureName;
      RAISERROR(@message, 0, 1) WITH NOWAIT;
  END

  --\
  ---) Cleanup old rows from [udm_storedproc_executions].
  --/
  DELETE FROM spx
    FROM
        [dbo].[udm_storedproc_executions] spx
    LEFT JOIN
        sys.dm_exec_requests der
        ON spx.[session_id] = der.[session_id]
        AND spx.[start_time] = der.[start_time]
        AND spx.[connection_id] = der.[connection_id]
        AND spx.[task_address] = der.[task_address]
    WHERE
        der.session_id IS NULL;
END
GO

Add the code snippet

code_snippet_sp_start.sql
CREATE PROCEDURE [sp].[StoredProcGettingPace]
AS
BEGIN
  SET NOCOUNT ON;
  --\
  ---) PACE (Protection Against Concurrent Executions) code
  --/
  DECLARE @PaceAction VARCHAR(8), @InsertDmRow BIT = 1
  WHILE 1 = 1
  BEGIN
      EXECUTE [sp].[GetPace]
        @StoredProcedureName = '[sp].[StoredProcGettingPace]',
        @WaitIfExecuted = 1,
        @PaceAction = @PaceAction OUTPUT,
        @InsertDmRow = @InsertDmRow,
        @EnableDebugMessages = 1;

      IF @PaceAction = 'RETURN' RETURN 0;
      IF @PaceAction = 'CONTINUE' BREAK;
      SET @InsertDmRow = 0;
  END

  --\
  ---) Stored procedure body ..
  --/
END
GO

Proof that it works

For the demo I use the following stored procedures:

  • [sp].[SubStoredProc]
  • [sp].[StoredProcGettingPace]
  • [sp].[StoredProcGettingPaceNoWait]

By the way, all the scripts can be downloaded here.

[sp].[SubStoredProc] is being executed by both other stored procedures.

[sp].[StoredProcGettingPace]

First I will demonstrate the testresults with [sp].[StoredProcGettingPace].
It is executed from 4 query windows at the same time. Debug messages show us what happened.

mssql-e05-341The query that predicts the order in which the session_ids will be processed: the order is 60, 59, 57, 52.

mssql-e05-342As expected session 60 is processed first.

mssql-e05-343As expected session 59 is processed after 60. The “Hello world” time (the beginning of the actual stored procedure body) is after the “Goodbye” time of session 60.

mssql-e05-344Then session 57. Again the “Hello world” time is after the “Goodbye” time of session 59.

mssql-e05-345And finally session 52. It goes without saying that the “Hello world” time is after the “Goodbye” time of the previous session.

[sp].[StoredProcGettingPaceNoWait]

It is important to understand parameter @WaitIfExecuted of stored procedure [sp].[GetPace].
In the previous example we used the value 1 (true), which means that every execution of a stored procedure protected with [sp].[GetPace] is eventually executed, but later.
But if we use the value 0 (false) for @WaitIfExecuted, we actually cancel the execution of a stored procedure if it is already running.

Here are the testresults of executing [sp].[StoredProcGettingPaceNoWait].
Again it is executed from 4 query windows at the same time and debug messages show us what happened.

mssql-e05-346As expected the stored procedure is executed only once. The other executions are cancelled (see below).

mssql-e05-347

mssql-e05-348

mssql-e05-349

Download all the scripts here.

Conclusion / Wrap up

In this blog post you could read about two different solutions that can prevent a stored procedure from being executed multiple times simultaneously.
This can be convenient in certain situations, when the stored procedure does some “global” work in your database, and it would be unnecessary and potentially “dangerous” when it is executing multiple times concurrently.

(c) 2016 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.

Using alternate (non-time) triggers to start a SQL Agent job (SQL Server Series)

Updates

After its first publication of this post, I have made a number of modifications on the tool:

Change log

Date Changes
25 September, 2016 Major update:
(1) extended to use an umlimited number of jobtrigger conditions;
(2) added SQLAgentJobResult trigger conditions;
(3) configuration via config file no longer supported.
28 January, 2017 Major update:
(1) added “Running” as a state that can be checked for a job (in the previous version the values where “Succeeded”, “Failed” or “Completed”);
(2) added jobcondition property “reverse_condition”, which enables negative triggers, e.g. a file may not be found, a job may not be running;
(3) a check is done that the jobs used for jobtriggers and jobtrigger conditions actually exist, if not an error is thrown, so this can prevent a silent failure, when a job is renamed or removed after a jobtrigger has been made.

Introduction

Today some SQL Agent stuff. I thought the SQL Agent works just great, until I got the question: can you start this job, when there is a file dropped in folder X, but only if also another process has already done something in the database. Huh?

Problem

With the SQL Agent I can only schedule a job to using time triggers, while I need different triggers like
existence of one or more files, a certain database state and/or other SQL Agent Job(s) having succeeded, failed or just completed.

Solution

So I needed something that checked if a file exists in a folder, and if something was done in the database, and if both is true, a SQL Agent job must be started.
I decided to create a C# Console Application named SQLAgentJobStarter, that could do this job for me. But I wanted it to be a bit more flexible, using one or more trigger conditions of different types, like file trigger or a sql trigger, or both (as in the requirement above).
Later on I extended this with jobtrigger conditions, and I changed the definitions a bit. A trigger can now have one or more conditions of different types:

  • file jobtrigger condition
  • sql jobtrigger condition
  • SQLAgentJobResult jobtrigger condition

The different condition types can be combined in one jobtrigger, e.g. SQLAgentJob “Job_A” must have succeeded and file test.txt must be in folder C:\temp.

In this article I want to concentrate on instructing you how to use this tool, instead of going into deep from a coding point of view.
I have made this tool an open source project on GitHub, so you still can look in the code (SQLAgentJobStarter_VisualStudio2015_SourceCode.zip) if you want, but that’s up to you.

DISCLAIMER: please use this tool at your own risk. It is in a bèta state, you should not use it in production environments, until you have convinced yourself that it does what it should do.

Installation and demonstration of the tool

One important comment first. This tool is not a Windows Service. It is “just” a Console Application that needs to be scheduled using a SQL Agent job or Windows Task Scheduler.
Having said that, what are we going to do?

  • We are going to create two tables in the msdb database: [dbo].[sysjobtriggers] and [dbo].[sysjobtrigger_conditions]. All other SQL Agent job data is also in this database, therewith this seems a fair place. Still I want to emphasize the importance of backing up this database on a regular base, preferably daily. But if you have a DBA and a maintenance plan set up, this might be needless to say.
  • We are going to create a few stored procedures in the msdb database to maintain those tables.
  • We are going to install the executable HansMichiels.com.Tools.SQLAgentJobStarter.exe and corresponding config file in the C:\Program Files\SQLAgentJobStarter\MSSQL13.MSSQLSERVER folder. MSSQL13.MSSQLSERVER represents the SQL Version number and Instance name, in the example a default instance of SQL Server 2016. Change it to the name of your SQL Server version and instance. In this way you can install the tool side by side for different SQL Instances on the same machine.
  • Before start using the tool, check the SQLConnectionString in the HansMichiels.com.Tools.SQLAgentJobStarter.exe.config file. By default, this is configured as follows: key=”SQLConnectionString” value=”Data Source=(local);Initial Catalog=msdb;Integrated Security=SSPI;”. This is okay for a local default SQL Server Instance, but not usable when you are using named instances.
  • We are going to schedule the tool using a SQL Agent job.
  • Now you are ready to rock! All that needs to be done is adding jobtriggers and jobtrigger_conditions to the msdb database. But to help you with this, I will show you a demo now.

Installation – technical steps

  • Download all the needed files here.
  • Unzip the files in SQLAgentJobStarter_ProgramFiles.zip, and copy them to a folder on your server, I used C:\Program Files\SQLAgentJobStarter\MSSQL13.MSSQLSERVER.
  • Review the script msdb_install.sql and if needed adjust the foldername here: @command=N'”C:\Program Files\SQLAgentJobStarter\MSSQL13.MSSQLSERVER\HansMichiels.com.Tools.SQLAgentJobStarter.exe”‘,
  • After reviewing, execute the script msdb_install.sql
  • Review the SQL Agent job ##SQLAgentJobStarter##, especially the schedule. For the demo the polling interval of the schedule is 1 minute, but I advice to set it to 3 or 5 minutes for production environments, because this interval usually is good enough.

    Also enable the schedule, it is installed in a disabled state.

So far, so good. The tool is installed.

Installation – preparation for the demo.

If you want to join the demo, also do the following:

  • execute script demo_setup_sqlagentjobs.sql: this script will set up dummy SQL Agent jobs that are used for the demo.
  • execute script demo_setup_storedprocedures.sql: this script will create a database [MSSQL_S01E04] with some stored procedures that are used for sql triggers.
  • create a folder C:\Temp, if it does not exist yet, and create a file with a .txt extension in it.
  • execute script demo_setup_sysjobtriggers.sql: this script will add demo rows to the tables [dbo].[sysjobtriggers] and [dbo].[sysjobtrigger_conditions]
  • enable all schedules. To do this you can right-click on the Jobs node of SQL Server Agent and click on Manage Schedules, then select all the schedules for the demojobs (their names start with Schedule for MSSQL_S01E04) and the schedule for ##SQLAgentJobStarter##, if not enabled yet.


The Manage Schedules menu-item ..


Check the Enabled checkbox for the applicable schedules ..

The jobs that are created so far

mssql-s01e04-342
The tables in msdb that hold the trigger information ..

mssql-s01e04-355The stored procedures in msdb used to maintain the jobtriggers ..

mssql-s01e04-334The [MSSQL_S01E04] database with stored procedures, used for the demo.

mssql-s01e04-335The C:\Temp folder, that should contain one or more .txt files ..

Viewing the demo results.

Now just wait for about 10 minutes.
In the meantime I can tell you some expected testresults:

  • MSSQL_S01E04_Job01_SqlConditionTrue, MSSQL_S01E04_Job02_FileConditionTrue and MSSQL_S01E04_Job03_FileAndSqlConditionTrue will be executed every time after the job ##SQLAgentJobStarter## has run, because their triggers (C:\Temp\*.txt and [MSSQL_S01E04].[sp].[SqlTriggerExample], both return True.
  • MSSQL_S01E04_Job04_TwoJobSuccessConditions will only be executed after the triggering jobs have succeeded both.
  • MSSQL_S01E04_Job05_JobFailureCondition will only be executed after the triggering job has failed.
  • MSSQL_S01E04_Job06_JobConditionThatNeedsToComplete will only be executed after the triggering job has either succeeded or failed.
  • MSSQL_S01E04_Job07_RunningJob will only be executed when the triggering job is running.
  • MSSQL_S01E04_Job08_NonRunningJob will only be executed when the triggering job is not running. Because job 7 and 8 both have the same triggering job, either MSSQL_S01E04_Job07_RunningJob or MSSQL_S01E04_Job08_NonRunningJob will be started after the job ##SQLAgentJobStarter## has run.
  • MSSQL_S01E04_Job09_SqlConditionFalse will never be executed because the jobtrigger uses a sql trigger using stored procedure [MSSQL_S01E04].[sp].[SqlTriggerFalse], that always returns 0 (false).
  • MSSQL_S01E04_Job10_ReversedFileCondition_RunsWhenFileDoesNotExist will only be executed when the file “C:\Temp\NowJob10DoesNotRun.txt” does not exist.

Let’s see if it worked out!


Viewing the job history of job 1 to 3..
In the job history I can see that:

  • MSSQL_S01E04_Job01_SqlConditionTrue, MSSQL_S01E04_Job02_FileConditionTrue and MSSQL_S01E04_Job03_FileAndSqlConditionTrue were executed every time after the job ##SQLAgentJobStarter## had run.
  • ##SQLAgentJobStarter##, the job that caused this, was also executed every minute, as scheduled.


Viewing the job history of job 4..

  • MSSQL_S01E04_Job4 was only being executed after the triggering jobs had succeeded both.


Viewing the job history of job 5..

  • MSSQL_S01E04_Job5 was only being executed after the triggering job had failed.


Viewing the job history of job 6..

  • MSSQL_S01E04_Job6 was being executed after the triggering job had either succeeded or failed.


Viewing the job history of job 7 and 8..

  • MSSQL_S01E04_Job07_RunningJob was being executed when the triggering job was still running (job MSSQL_S01E04_Trigger_for_Job07_and_Job08 runs every other minute for 30 seconds).


Viewing the job history of job 9..

  • MSSQL_S01E04_Job09_SqlConditionFalse was never executed: it is checked on the left, but does not occur on the right.


Viewing the job history of job 10 when the file “C:\Temp\NowJob10DoesNotRun.txt” does not exist.

Viewing the job history of job 10 after the file “C:\Temp\NowJob10DoesNotRun.txt” is created.

  • MSSQL_S01E04_Job10_ReversedFileCondition_RunsWhenFileDoesNotExist was only executed when the file “C:\Temp\NowJob10DoesNotRun.txt” did not exist.

But there is more. The tool will write to the Windows Eventlog when an error occurs.
mssql-s01e04-337To view the windows Eventlog, press <Windows Key>+R, then type eventvwr


Example of errors that are logged.

Make sure you clean up your server! Use the script demo_cleanup.sql for that. Otherwise the tool will keep on executing the demo jobs. Also change the interval of the ##SQLAgentJobStarter## from 1 to 3 or 5 minutes.

Understanding the stored procedures of the solution

Stored procedure [dbo].[usp_upsert_jobtrigger]

With this stored procedure you can insert or update a jobtrigger.

You can set the following parameters:

  • @trigger_id: If provided as input, it will be used for checking if an update or insert needs to be done. If left null or smaller than or equal to 0, it will be filled in the stored procedure.
  • @enabled: Only enabled jobtriggers are processed. Values 0 or 1 (required).
  • @name: Name for the jobtrigger (unique + required). Is used for checking if an update or insert needs to be done, when @trigger_id is null or smaller than or equal to 0
  • @sqlagent_jobname: The name of the SQL Agent job that must be started when all jobtrigger conditions are true (required).
  • @processing_order: Indicates the order in which jobtriggers must be processed (required). Duplicate processing order values are allowed.
  • @description: An optional description.

Stored procedure [dbo].[usp_upsert_jobtrigger_condition]

With this stored procedure you can insert or update a jobtrigger condition of any type.

You can set the following parameters:

  • @condition_id: If provided as input, it will be used for checking if an update or insert needs to be done. If left null or smaller than or equal to 0, it will be filled in the stored procedure.
  • @trigger_id: The trigger_id of the jobtrigger to which the jobtrigger condition belongs.
  • @enabled: Only enabled jobtrigger conditions are processed. Values 0 or 1 (required).
  • @processing_order: Indicates the order in which jobtrigger conditions must be processed (required). Duplicate processing order values are allowed.
  • @description: An optional description.
  • Note regarding the parameters underneath: You must provide values for the one @sql* parameter OR the three @file* parameters OR the three @job* parameters.
  • @sql_stored_procedure: The fully qualified name (including database name) of a stored procedure that returns a single row with a single column named [SqlTriggerResult] of the BIT datatype.
  • @file_watch_folder: The folder where is searched for files.
  • @file_recurse_subfolders: Indicates if also subfolders of the watchFolder must be checked.
  • @file_name_pattern: A filename or filename pattern with wildcards of the file that must exist for the triggercondition to be true.
  • @job_name: The name of a SQLAgentjob that must be completed with the desired value for @job_result for the triggercondition to be true.
  • @job_result: Domainvalues [S | F | C] meaning Succeeded, Failed or Completed (=Succeeded or Failed).
  • @job_result_maxage: An age in seconds for the maximum time ago the job was completed. Use -1 for unlimited. For instance, you want a sql agent job to start when 4 other jobs, that are scheduled daily and partially run simultaneously, have all succeeded.
    The time difference between the first and the last estimated completion time is 60 minutes.
    The time difference between the first scheduled starttime and the last estimated completion time is 120 minutes.
    Any value (recalculated as seconds) between 60 minutes (plus a safety margin) and 22 hours (24 hours minus 120 minutes) would be fine.
  • @reverse_condition: If the value 1 (true) is used, the condition must have the opposite result to trigger the job to start. For example a file may not be found or a job may not be running.
    A few things are important to understand:

    1. Job results “Succeeded”, “Failed” or “Completed” check for a result; job state “Running” (or not) checks for a current state. Do not get confused about this.
    2. @reverse_condition does not work for job results “Succeeded” and “Failed” and there is no need to: the opposite of “Succeeded” is “Failed”, you should implement it that way. The opposite of “Not Succeeded” implemented with @reverse_condition = 0, would be any result except “Succeeded”, so “Failed”, “Completed” or “No execution found”.
    3. @reverse_condition does not work for “Completed” for the same reason: The opposite of “Completed” implemented with @reverse_condition = 0, would be any result except “Completed”, so “Failed”, “Succeeded” or “No execution found”. Instead of “Not Completed” you could check for “Running”.
    4. If @job_result = ‘R’ (Running) and @reverse_condition = 0 then @job_result_maxage is not used: when job is running (and the condition is met) @sqlagent_jobname is only started, if it was not started yet after the current execution of the running job (so a running job will not cause the other job to run more than once).
    5. If @job_result = ‘R’ (Running) and @reverse_condition = 1 then @job_result_maxage is used differently: when job is not running (and the condition is met) @sqlagent_jobname is only started, if it was not started the last @job_result_maxage seconds. Therefore it is best, to use “Not Running” only in conjunction with some other condition.

Stored procedure [dbo].[usp_upsert_sql_jobtrigger_condition]

With this stored procedure you can insert or update a jobtrigger condition of type ‘sql’ (using a stored procedure).

You can set the following parameters:
@condition_id, @trigger_id, @enabled, @processing_order, @description, @sql_stored_procedure, @reverse_condition: as described above.

Stored procedure [dbo].[usp_upsert_file_jobtrigger_condition]

With this stored procedure you can insert or update a jobtrigger condition of type ‘file’.

You can set the following parameters:
@condition_id, @trigger_id, @enabled, @processing_order, @description, @file_watch_folder, @file_recurse_subfolders, @file_name_pattern, @reverse_condition: as described above.

Stored procedure [dbo].[usp_upsert_jobresult_jobtrigger_condition]

With this stored procedure you can insert or update a jobtrigger condition of the SQLAgentJobResult type.

You can set the following parameters:
@condition_id, @trigger_id, @enabled, @processing_order, @description, @job_name, @job_result, @job_result_maxage, @reverse_condition: as described above.

Stored procedure [dbo].[usp_delete_jobtrigger]

With this stored procedure you can delete a jobtrigger by trigger_id or name.

Stored procedure [dbo].[usp_delete_jobtrigger_condition]

With this stored procedure you can delete a jobtrigger condition by using the condition_id.

Stored procedure [dbo].[usp_delete_jobtrigger_conditions_by_trigger_id]

With this stored procedure you can delete all jobtrigger conditions for a given trigger_id.

Examples of usage of the stored procedures

Among the files you can download there is the file demo_setup_sysjobtriggers.sql, that uses the upsert stored procedures for different use cases.

Conclusion / Wrap up

I have tried to explain how the SQLAgentJobStarter tool can be used for starting SQL Agent jobs triggered by the existence of one or more files, by the result of a stored procedure execution or by the execution result of one or more other SQL Agent Jobs.
A few important sidemarks I still have to make:

  1. In real life scenarios, make sure the files you are polling for are moved after processing, so that the tool does not try to start the job again.
  2. To create stored procedures that are used as a sql trigger you can use the storedprocedure_template.sql file as a template.
  3. To create your own jobtriggers and triggerconditions please check demo_setup_sysjobtriggers.sql in which some of the stored procedures are used.
  4. I am aware that the sourcecode of the tool might need some refactoring. It does the job, however.
  5. Anyone that can help me with creating a graphical user interface to manage the jobtriggers and triggerconditions using C# and WinForms is more than welcome. If you are interested, please contact me. I do not have enough time to do this myself.

(c) 2016/2017 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.

Using the project deployment model with environments (SSIS Series)

Introduction

Hi, as announced in my previous post of July, 14, I took a summer break. I was really useful and I enjoyed it.
From now on I will try to write some interesting posts weekly again.
Today I want to explain the project deployment model of SSIS, introduced already in SSIS 2012.
Also you are gonna find a (hopefully useful) script below that creates folders, environments and environment variables, and creates connections from package parameters to environment variables.
But that’s for later, first a little introduction.

Folders and environments

What I found very confusing when I started using the project deployment model, already some years ago, is that an environment in this context is not what I expected it to be.
From a software development perspective an environment is an installed and working version of your software, optionally using one or more databases and configuration setttings exclusively for that environment. For instance a Development or a Test environment.
But the project deployment model of SSIS does not agree with that definition.
For the project deployment model an environment is only a set of values for all environment variables, and nothing more.
Can you think of what that means? Suppose you have a server, both used for Development and Test, and you have databases and cubes with a “_DEV” or a “_TST” suffix to their names.
But as far as SSIS is concerned, you can only install one version of an SSIS package! Yeah, you can execute it with a different set of parameter values stored in SSIS environment variables, but that’s it.
You cannot have two different versions of the same package installed on the same instance of SQL Server – at least not using only SSIS Environments.
I found it not so intuitive that you need to use folders inside the Integration Services Catalog to achieve this.
So far the theory, let us try this stuff out together.

Let’s try it

I’ll just click an example together, you do not need to follow me, my goal is that you understand the concepts so that you can apply it to your own projects.
By the way, I am using SQL Server 2016, but it should also work in SQL Server 2012 and 2014.

My test setup is very basic:

  • I have made two databases, [SSIS_S01E08_DEV] AND [SSIS_S01E08_TST], as development- and testenvironment, respectively.
  • Both databases have a table [tbl].[Fruit], but the table in the DEV database has one extra column, [FruitDescription], to simulate a change that is under development but not deployed to test yet.
  • I have made one SSIS Package AddFruit.dtsx, that inserts a row into the table [tbl].[Fruit] passing the value for [FruitName] only. During the demo I will change the package so that it also provides the [FruitDescription] column value.

Playing around with the example above, I can demonstrate the use of folders and environments when the package is deployed using the project deployment model.

The initial SSIS Package

The initial SSIS Package contains just one Execute SQL Task, one parameter named FruitName and one OLEDB Connection manager named MyDatabase.connectionmanager.
The connection manager is configured by an expression so that the property ConnectionString gets the value of a project parameter also named ConnectionString as demonstrated in the images below.
SSIS-S01E08-280The Execute SQL Task

SSIS-S01E08-281The package parameter

SSIS-S01E08-282The project parameter

SSIS-S01E08-283The OLEDB connection manager configured with an expression
SSIS-S01E08-284Then rebuild the Solution

Deploying the SSIS Package

Once the solution has been rebuilt, you can deploy the package with the Integration Services Deployment Wizard. You can also call this utility from the command line, but explaining that is out of scope for this blog post.
To execute this wizard, you have to locate the .ispac file, it is in a subfolder of your projects bin folder.
If you have created additional solution configurations in your Visual Studio solution, this subfolder might be different from the image below (where the subfolder is Development).
SSIS-S01E08-285Locating the .ispac file, that was created when the solution was rebuilt.

SSIS-S01E08-286First step of the wizard

SSIS-S01E08-287The default deployment model is Project Deployment, the one you need. The other option is Package Deployment.

SSIS-S01E08-288Select a server name and a path [1]

SSIS-S01E08-289Select a server name and a path [2]: create a new folder

SSIS-S01E08-290Select a server name and a path [3]: name the folder after your environment

SSIS-S01E08-292Select a server name and a path [4]: then click Next

SSIS-S01E08-293Review and deploy.

SSIS-S01E08-295Deployment succeeded.

SSIS-S01E08-296In the Integration Services Catalog you can find your project and package.

SSIS-S01E08-297You could configure it now, but there are no SSIS environments yet (note: these are a set of environment variable values).

SSIS-S01E08-298Don’t configure just yet. We will do this later.

SSIS-S01E08-299Here you could create an environment .. don’t do it now !

SSIS-S01E08-TST-environment_setup.sql

--\
---) A script to create a folder and environment in an SSIS Catalog.
---) Author: Hans Michiels
--/
/*
(c) Copyright 2016 - hansmichiels.com
 
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.
 
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
 
You should have received a copy of the GNU General Public License
along with this program. If not, see http://www.gnu.org/licenses/.
*/

USE [SSISDB]
GO

DECLARE @environment_variables_table TABLE(
  [id] int identity(1, 1),
  [variable_name] nvarchar(128),
  [data_type] nvarchar(128),
  [sensitive] bit,
  [value] nvarchar(4000),
  [description] nvarchar(1024)
  )

DECLARE @object_parameter_value_table TABLE(
  [id] int identity(1, 1),
  [object_type] smallint,
  [object_name] nvarchar(260),
  [parameter_name] nvarchar(128)
  )

DECLARE @id INT
DECLARE @max_id INT
DECLARE @folder_name sysname
DECLARE @environment_name nvarchar(128)
DECLARE @environment_description nvarchar(128)
DECLARE @project_name nvarchar(128)
DECLARE @variable_name nvarchar(128)
DECLARE @data_type nvarchar(128)
DECLARE @sensitive bit
DECLARE @value sql_variant
DECLARE @value_tinyint tinyint
DECLARE @description nvarchar(1024)
DECLARE @nsql nvarchar(max)

DECLARE @object_type smallint
DECLARE @object_name nvarchar(260)
DECLARE @value_type CHAR(1)
DECLARE @parameter_name nvarchar(128)
DECLARE @parameter_value sql_variant

--\
---) Environment settings
--/
SELECT
  @folder_name = N'TST',
  @environment_name = N'TST',
  @environment_description = N'My demo Test environment',
  @project_name=N'SSIS_S01E08'

--\
---) Script to create environment and settings.
--/
IF NOT EXISTS (
    SELECT 1
    FROM [SSISDB].[internal].[environments]
    WHERE [environment_name] = @environment_name
    )
BEGIN
    EXEC [SSISDB].[catalog].[create_environment] @folder_name, @environment_name, @environment_description;
END

INSERT INTO @environment_variables_table
  ( [variable_name], [data_type], [sensitive], [value], [description] )
  VALUES
      ( N'ConnectionString', N'String', 0, N'Data Source=.;Initial Catalog=SSIS_S01E08_TST;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;', N'Connection string for the database.' )
    , ( N'FruitName', N'String', 0, N'Tamarillo', N'' )
    --, ( N'Int32Example', N'Int32', 0, N'1', N'' )
    --, ( N'BooleanExample', N'Boolean', 0, N'1', N'' )
    --, ( N'ByteExample', N'Byte', 0, N'5', N'' )

SELECT @id = 1, @max_id = MAX([id]) FROM @environment_variables_table
WHILE @id <= @max_id
BEGIN
    SELECT
      @variable_name = v.variable_name,
      @data_type = v.data_type,
      @sensitive = v.sensitive,
      @value = v.value,
      @description = v.[description]
    FROM @environment_variables_table v
    WHERE [id] = @id;

    IF EXISTS (
        SELECT 1
        FROM [SSISDB].[internal].[environment_variables] v
        JOIN [SSISDB].[internal].[environments] e ON e.environment_id = v.environment_id
        WHERE v.[name] = @variable_name
        AND e.environment_name = @environment_name
        )
    BEGIN
        SET @nsql = N'EXECUTE [catalog].[delete_environment_variable] '
          + N'@folder_name = N'''+ @folder_name + ''', @environment_name = N''' + @environment_name + ''', '
          + N'@variable_name = N''' + @variable_name + ''''
        PRINT @nsql;
        EXEC sp_executesql @nsql;
    END

    PRINT '/*'
    PRINT @variable_name
    PRINT CONVERT(nvarchar(128), SQL_VARIANT_PROPERTY(@value, 'BaseType'));
    PRINT '*/'

    SET @nsql = N'EXECUTE [catalog].[create_environment_variable] '
      + N'@folder_name = N'''+ @folder_name + ''', @environment_name = N''' + @environment_name + ''', '
      + N'@variable_name = N'''+ @variable_name + ''', @data_type = N''' + @data_type + ''', '
      + N'@sensitive = ' + CONVERT(NVARCHAR, @sensitive) + ', @description = N''' + @description + ''', '
      + CHAR(13) + CHAR(10) + N'@value = ' +
      CASE UPPER(@data_type)
      WHEN 'String' THEN 'N''' + CONVERT(NVARCHAR(1000), @value) + ''' '
      ELSE CONVERT(NVARCHAR(1000), @value)
      END + '; '
    PRINT @nsql;
    EXEC sp_executesql @nsql;

    SET @id = @id + 1
END

--\
---) Add environment reference to project.
--/
Declare @reference_id bigint
IF NOT EXISTS(SELECT 1
  FROM [SSISDB].[internal].[environment_references] r
  JOIN [SSISDB].[internal].[projects] p
    ON p.project_id = r.project_id
  WHERE p.name = @project_name
    AND r.environment_name = @environment_name
  )
BEGIN
    EXEC [SSISDB].[catalog].[create_environment_reference]
        @environment_name=@environment_name, @reference_id=@reference_id OUTPUT,
        @project_name=@project_name, @folder_name=@folder_name, @reference_type='R';
    --Select @reference_id
END

--\
---) Connect environment variables to package parameters, based on the name
--/
INSERT INTO @object_parameter_value_table (
    [object_type],
    [object_name],
    [parameter_name]
    )
SELECT
    prm.[object_type],
    prm.[object_name],
    prm.[parameter_name]
FROM
    [SSISDB].[internal].[object_parameters] prm
JOIN
    [SSISDB].[internal].[environment_variables] ev
    ON ev.name = prm.parameter_name
JOIN
    [SSISDB].[internal].[projects] prj
    ON prj.project_id = prm.project_id
WHERE
    prm.[value_type] != 'R'
AND
    prm.value_set = 0
AND
    prj.name = @project_name
ORDER BY
    prm.object_name, prm.parameter_name

SELECT @id = 1, @max_id = MAX([id]) FROM @object_parameter_value_table
WHILE @id <= @max_id
BEGIN
    SELECT
      @object_type = v.[object_type],
      @object_name = v.[object_name],
      @parameter_name = v.[parameter_name]
    FROM @object_parameter_value_table v
    WHERE [id] = @id;

    SELECT @value_type = 'R', @parameter_value = @parameter_name;
    
    EXEC [SSISDB].[catalog].[set_object_parameter_value]
      @object_type, @folder_name, @project_name, @parameter_name,
      @parameter_value, @object_name, @value_type

    SET @id = @id + 1
END

GO

My script that facilitates to set up multiple environments quickly (download all the used scripts here).

In the demo it would not be a problem to do all the configuration by hand, but if you have a lot of SSIS Packages with a lot of parameters, you really do not want to do this, especially not if you have different environments, like Development, Test and Production.
This is why I made a script that automates this work for me:

  • It creates an environment, if it doesn’t exist yet.
  • It (re)creates environment variables, defined in the script.
  • It adds an environment reference to the project, if it doesn’t exist yet.
  • It connects environment variables to package parameters, based on the name.
    For this to work, it is important that package parameters with the same name used in different packages mean the same thing!
    For instance if you have a parameter SourceFolder used in different packages, it should be the same folder! If not, rename the parameters so that they have a unique name and – when mapped to environment variables – can both be assigned a different value, e.g. CrmSourceFolder and ErpSourceFolder.

We run the script for the TST environment.

SSIS-S01E08-300The script was executed successfully.

SSIS-S01E08-301After the script has run, a TST folder with an TST environment inside exists. You can view or edit its properties.

SSIS-S01E08-302When the Variables pane is selected on the left, the environment variables, created by the script, are shown.

SSIS-S01E08-303View package configuration.

SSIS-S01E08-304The package parameter FruitName is now connected to environment variable FruitName.

SSIS-S01E08-305When I choose Execute ..

SSIS-S01E08-306… all I have to do is check Environment, it will automaticly show .\TST

SSIS-S01E08-307After execution of the package, a row exists in the table (and one from a previous test)

SSIS-S01E08-308We can also execute the package with a SQL Agent Job ..

SSIS-S01E08-310Execute the package with a SQL Agent Job: all I have to do is check Environment, it will automaticly show .\TST ..

SSIS-S01E08-311.. and all values are connected properly ..

SSIS-S01E08-312And yes, it works!

Extending the package with a FruitDescription

I must admit, I cheated a little. I skipped the part of deploying the first version of the SSIS Package to the DEV environment.
So now I am going to deploy to a new DEV Folder and Environment for the first time, while it is will be the second version of the package. This is not a recommended practice in real projects.
Anyway, the package gets an extra parameter FruitDescription.
SSIS-S01E08-313Added FruitDescription

SSIS-S01E08-314Modify the Execute SQL Task to insert also the value for @FruitDescription [1]

SSIS-S01E08-315Modify the Execute SQL Task to insert also the value for @FruitDescription [2]

SSIS-S01E08-316Then Rebuild

SSIS-S01E08-317Then run deployment wizard again, but now create a DEV folder

SSIS-S01E08-318Script modifications [1]: use a DEV folder and a DEV environment name.

SSIS-S01E08-319Add FruitDescription to the list of environment variables and change the values: connection string must use the DEV database and fruitname and -description something different than in the TST environment.

Then run the script.
SSIS-S01E08-320The package was deployed using the deployment wizard.

SSIS-S01E08-321SSIS Environment was created using the script

SSIS-S01E08-322Environment variables where added using the script

SSIS-S01E08-323Environment variables were connected to package parameters using the script

SSIS-S01E08-324Package execution test: note the new parameter FruitDescription

SSIS-S01E08-325Test result

SSIS-S01E08-326Final result: different versions of the same SSIS Package installed side by side on the same machine. But in addition to SSIS Environments with variable values, folders inside the Integration Services Catalog are used for that!

Download the scripts here.

Conclusion / Wrap up

With the project deployment model of SSIS, introduced in SQL Server 2012, deployment and configuration of SSIS Packages has become easier.
However it is important to understand how folders and environments in the Integration Services Catalog work. I have tried to explain this, and provided a script to set up multiple environments quickly.

Why did you use those weird fruit examples?

D=Development
T=Test
😉
Read more here:
Durian
Tamarillo

(c) 2016 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.