How a SSIS Lookup works different from a T-SQL JOIN (SSIS Series)

Introduction

With a Lookup in a dataflow in a SSIS Package you can achieve a similar result as with a T-SQL Join clause in a SELECT statement: you can check if a row exists in a different dataset, based on one or more (business / natural / surrogate) key columns. However there are a number of differences. One of these differences took me several hours of investigating some time ago, when I had this lookup in SSIS that did not find any row. I did not understand why, because I had a similar T-SQL statement with a JOIN that found all rows. What could be different (it appeared to be a CHAR column connected to a VARCHAR)?
So today I am going to compare the two.

Subjects of comparison:

  1. Perform a lookup when the datatypes are different
  2. Perform a lookup on a different SQL Server Instance
  3. Perform a lookup when the join is not a equi-join
  4. When the source is not SQL Server
  5. When the joined columns differ in Case (Uppercase / Lowercase / Mixed case)

Simple setup for two lookup transformations using the Adventureworks database

Perform a lookup when the datatypes are different

A Transact-SQL query with a join is more forgiving than a SSIS Lookup when different datatypes are joined.

Connect a nvarchar to a varchar column

In a SSIS Lookup this does not work because an error is shown during design:

In T-SQL, this join can be made and will work:

different_datatypes.sql
SELECT
    *
FROM
    [Production].[STG_Product] stg
JOIN
    [Production].[ProductCategory] pc
    ON pc.name = stg.VARCHAR_ProductCategoryName

Connect a fixed length CHAR column to a variable length VARCHAR column (or NCHAR to NVARCHAR)

In SSIS the lookup can be developed, but will not work in practice, when the CHAR column value is not the maximum length (and thus has trailing spaces).
A “join” of a nvarchar to a nchar column ..
.. will not work !

In T-SQL, this join can be made and will work:

fixed_length_column.sql
SELECT
    *
FROM
    [Production].[STG_Product] stg
JOIN
    [Production].[ProductCategory] pc
    ON pc.name = stg.NCHAR_ProductCategoryName

Perform a lookup on a different SQL Server Instance

In a SSIS Lookup you can use different connection managers for source and Lookup transformation, so this is easy to do.

In T-SQL this would be more complex to implement, for instance you could use linked servers if your security policy allows this.

Perform a lookup when the join is not a equi-join

An equi-join is a join where the column values that are joined simply must be equal to each other.

By default a SSIS Lookup will do an equi-join. In the visual editor you connect one or more columns from the Available Input Columns to the Available Lookup Columns. Designed in this way it will be an equi-join.
There are two ways I know of to implement a different kind of join:

  1. in the Advanced Tab of the Lookup Transformation Editor you can modify the SQL statement.
  2. use a OLE DB Command in the Data Flow, but this can have a negative impact on performance.

In T-SQL you can do more complex joins, for instance with a BETWEEN clause for an EffectiveStartDate and EffectiveEndDate.

When the source is not SQL Server

In a SSIS Lookup you can use different connection managers for source and Lookup transformation, so this is easy to do.

In T-SQL this would be practicly impossible, or at least much more complex to accomplish (e.g. with OPENROWSET).

When the joined columns differ in Case

A SSIS Lookup is case-sensitive when Full cache is used, so differences in casing cause that lookup values are not found.
However when you switch to No Cache or Partial Cache , the lookup can become case-insensitive! Keith Mescha brought this to my attention (see below). Thank you, Keith.
So the safest thing to do is convert the column values for columns to connect from Available Input Columns to Available Lookup Columns to either upper- or lowercase on both sides. In T-SQL you can use the LOWER() and UPPER() functions for this, in SSIS you can add a Derived column to your dataflow, where you also can use the LOWER() and UPPER() functions. Depending on your needs, you can replace the existing column or add the upper or lower value as new column.

Example of a derived column that converts the value of a column of the Available Input Columns to uppercase.

In T-SQL the result of this join will depend on collation settings of the SQL Server Instance or column(s) affected in the join.
By default, SQL Server is installed using a case-insensitive collation (e.g. Latin1_General_CI_AS, where CI stands for Case Insensitive), and when the collation is not overruled on column-level, the join will work case-insensitively.

Performance considerations

From my own experience I know SSIS Lookups can be faster when developed correctly. This means:

  • Use full cache whenever the dataset allows this (so memory usage is acceptable given the amount of RAM on the server)
  • In the query of the lookup transformation, only include the columns that are needed in the Lookup.

Conclusion / Wrap up

In this blog post I have compared the SSIS Lookup transformation with the T-SQL Join.
In general T-SQL is more flexible and more forgiving, but when you need to go outside the SQL Server Instance for source or lookup it is easier to use a SSIS Lookup, but beware of the peculiarities of the SSIS Lookup, as described in this article.
Performance-wise, a SSIS Lookup can perform better than a T-SQL join, but this of course depends on a lot of design factors in both your Lookup as well as SQL database.

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

Free download: Blog Yearbook 2016

I have made a compilation of all my blog posts of the past year, and it was more work than you would think.
Especially because it also contains a non-published new article.

After filling in the form you can download the e-book for free.


I promise you the following:

  • I will not give or sell your email address to any third party.
  • I will not spam you.
  • If I start with a newsletter, you can easily unsubscribe or change the frequency.

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

Making your SQL Agent job scripts generic for all servers (SQL Server Series)

Introduction

If you schedule jobs and you are using SQL Server (not Express Edition) SQL Agent is your best friend. I think it is easier to use than Windows Scheduler for most use cases.
The GUI that SQL Server Management Studio has for creating and editing SQL Agent Jobs is also quite good, I think.

Problem

However, after the agent jobs are created, the SQL Agent Job scripting done by SQL Server Management Studio has a number of issues (Microsoft developers of the SQL Server team, please read!).
Where shall I start ..

  • I like to write repeatable scripts, that raise an error only when something is really wrong, so when the script is being executed as expected, no errors are thrown. The generated scripts however use brute force to delete any existing jobs. If they do not exist yet, an error occurs. This is not what I would call a real error, because it is logical that when a job does not exist yet, it cannot be deleted. A simple IF statement could check if the job exists and delete the job only if it exists.
  • Even worse, the generated scripts use the @job_id, a unique identifier, to delete the job. By definition, this will work only once! After the job is (re)created, the job_id will be different! It would be much better to use the @job_name as parameter for sp_delete_job (yes this is possible), it is just that the SQL Server developer team made the choice to use the @job_id .. 🙁
  • Because scripts are always delete and (re)create, instead of alter/modify, your entire job history is gone, when you change anything in your script and execute it on your server (sorry, no workaround for this).
  • Any (SQL) Server specific values, like SSIS Environment IDs and SQLInstance names, can make your scripts only suitable for one server, the one you created the job on with the SSMS GUI.

Solution

Given all the challenges above, I was looking for a way so:

  • I could create the SQL Agent jobs with the SSMS GUI.
  • Then script the jobs and put them under source control.
  • Then also make sure I can install the SQL Agent jobs on a different server (development/test/production) with the same (nearly) unmodified script.

For this to work, we are again visiting the system objects area of the msdb database.

The plan is roughly as follows:

  1. Create a wrapper stored procedure for sp_create_jobstep.
  2. Create a generic install script that partially is manual labour, that you have to do only once, and partially contains a script generated by SSMS for creating jobs.

Create a wrapper stored procedure for sp_create_jobstep.

This wrapper stored procedure, called [dbo].[usp_add_jobstep_wrapper] consists of the following three parts:

  • PART 1: Check if temp table #jobstep_replacements exists with project-/customer specific replacements: this temporary table can be created in a script that creates jobsteps and contains info about textual replacements to be done in any (N)VARCHAR parameter of the stored procedure sp_create_jobstep. However it would be neater to pass a table variable as parameter to the stored procedure, this would make adjusting scripted jobs more work, because a parameter would have to be added to the EXECUTE [dbo].[usp_add_jobstep_wrapper] command.
  • PART 2: Fix environment references in ssis commands: a feature that is open for improvement in a future SQL Server Service Pack is that when you create a jobstep that executes a SSIS Package, and you use a reference to an environment, in the script a technical ID is used, that is most likely to be different on another server. This is also not very easy to fix manually, or you have to look up all the new id’s with a query, and then change this in the script. Good as occupational therapy, but not so good for your productivity.
    So this part of the stored procedure fixes this /ENVREFERENCE for you.
  • PART 3: After doing replacements in parameter values, and fixing the /ENVREFERENCE, the built-in stored procedure sp_add_jobstep is executed.
    This implies that we simply can execute [dbo].[usp_add_jobstep_wrapper] instead of [dbo].[sp_add_jobstep] , and PART 1 and 2 will be done extra.

And here it is (can also be downloaded).

msdb.dbo.usp_add_jobstep_wrapper.sql

--\
---) hansmichiels.com [msdb].[dbo].[usp_add_jobstep_wrapper]
---) Author: Hans Michiels
---) Stored procedure that can help to make SQL Server Agent job scripts usable for multiple servers.
--/
/*
(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 [msdb]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_add_jobstep_wrapper]') AND type in (N'P', N'PC'))
  DROP PROCEDURE [dbo].[usp_add_jobstep_wrapper]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[usp_add_jobstep_wrapper]
  @job_id UNIQUEIDENTIFIER = NULL, -- Must provide either this or job_name
  @job_name sysname = NULL, -- Must provide either this or job_id
  @step_id INT = NULL, -- The proc assigns a default
  @step_name sysname,
  @subsystem NVARCHAR(40) = N'TSQL',
  @command NVARCHAR(max) = NULL,
  @additional_parameters NVARCHAR(max) = NULL,
  @cmdexec_success_code INT = 0,
  @on_success_action TINYINT = 1, -- 1 = Quit With Success, 2 = Quit With Failure, 3 = Goto Next Step, 4 = Goto Step
  @on_success_step_id INT = 0,
  @on_fail_action TINYINT = 2, -- 1 = Quit With Success, 2 = Quit With Failure, 3 = Goto Next Step, 4 = Goto Step
  @on_fail_step_id INT = 0,
  @server sysname = NULL,
  @database_name sysname = NULL,
  @database_user_name sysname = NULL,
  @retry_attempts INT = 0, -- No retries
  @retry_interval INT = 0, -- 0 minute interval
  @os_run_priority INT = 0, -- -15 = Idle, -1 = Below Normal, 0 = Normal, 1 = Above Normal, 15 = Time Critical)
  @output_file_name NVARCHAR(200) = NULL,
  @flags INT = 0, -- 0 = Normal,
                                                     -- 1 = Encrypted command (read only),
                                                     -- 2 = Append output files (if any),
                                                     -- 4 = Write TSQL step output to step history,
                                                     -- 8 = Write log to table (overwrite existing history),
                                                     -- 16 = Write log to table (append to existing history)
                                                     -- 32 = Write all output to job history
                                                     -- 64 = Create a Windows event to use as a signal for the Cmd jobstep to abort
  @proxy_id INT = NULL,
  @proxy_name sysname = NULL,
  -- mutual exclusive; must specify only one of above 2 parameters to
  -- identify the proxy.
  @step_uid UNIQUEIDENTIFIER = NULL OUTPUT
AS
BEGIN
  DECLARE @retval INT

  DECLARE @sort_order INT
  DECLARE @max_sort_order INT
  DECLARE @subsystems NVARCHAR(128)
  DECLARE @replace_scope NVARCHAR(128)
  DECLARE @scripted_value NVARCHAR(128)
  DECLARE @replace_value NVARCHAR(128)
  DECLARE @message NVARCHAR(MAX)
  DECLARE @divider_length INT = 120

  DECLARE @folder_name NVARCHAR(128)
  DECLARE @project_name NVARCHAR(128)
  DECLARE @startpos INT
  DECLARE @endpos INT
  DECLARE @endpos_project INT
  DECLARE @reference_id INT = -1
  DECLARE @new_reference NVARCHAR(128)
  DECLARE @new_command NVARCHAR(MAX)
  
  PRINT ' ';
  PRINT REPLICATE('-', @divider_length);
  PRINT 'Stored proc : [usp_add_jobstep_wrapper]'
  PRINT 'Copyright : (c) 2016 - hansmichiels.com'
  PRINT 'License : GNU General Public License, see http://www.gnu.org/licenses/'

  IF @job_name IS NOT NULL
  BEGIN
  -- PRINT 'Stored proc:
    SELECT @message = 'Job : ' + @job_name;
  END ELSE BEGIN
    SELECT @message = 'Job : ' + ISNULL((SELECT TOP 1 j.name FROM dbo.sysjobs j WHERE job_id = @job_id), N'(unknown)');
  END
  PRINT @message;
  SELECT @message = 'Jobstep : ' + @step_name;
  PRINT @message;
  PRINT ' ';

  --\---------------------------------------------------------------------------------------------------------
  ---) PART 1: Check if temp table #jobstep_replacements exists with project-/customer specific replacements.
  --/---------------------------------------------------------------------------------------------------------

  IF OBJECT_ID('tempdb..#jobstep_replacements', 'U') IS NULL
  BEGIN

    PRINT 'No jobstep_replacements were found, installing original script.';

  END ELSE BEGIN

    PRINT 'If any replacements are made, they will be shown below.';

    DECLARE replacements_cursor CURSOR LOCAL STATIC FOR
    SELECT TOP (2147483647) -- Otherwise ORDER BY might not work.
        CASE
          WHEN ISNULL(v.[subsystems], N'') IN (N'*', N'')
          THEN N'*'
          ELSE N',' + v.[subsystems] + N','
          END AS [subsystems],
        CASE
          WHEN ISNULL(v.[replace_scope], N'') IN (N'*', N'')
          THEN N'*'
          ELSE N',' + v.[replace_scope] + N','
          END AS [replace_scope],
        v.[scripted_value],
        v.[replace_value]
    FROM
        #jobstep_replacements v
    ORDER BY
        v.sort_order,
        v.id;
              
    OPEN replacements_cursor;

    FETCH NEXT FROM replacements_cursor
      INTO @subsystems, @replace_scope, @scripted_value, @replace_value;

    WHILE @@FETCH_STATUS = 0
    BEGIN

      IF (@subsystems = N'*' OR CHARINDEX(N',' + @subsystem + N',', @subsystems, 1) > 0)
          AND @replace_value IS NOT NULL
          AND @scripted_value != @replace_value
      BEGIN

        IF (@replace_scope = N'*' OR CHARINDEX(N',@command,', @replace_scope, 1) > 0) AND CHARINDEX(@scripted_value, @command, 1) > 0
        BEGIN
            SET @message = 'Replacement in @command: ' + @scripted_value + ' by ' + @replace_value;
            PRINT ' '
            PRINT @message;
            PRINT 'Old @command: ' + @command;
            SELECT @command = REPLACE(@command, @scripted_value, @replace_value);
            PRINT 'New @command: ' + @command;
        END

        IF (@replace_scope = N'*' OR CHARINDEX(N',@additional_parameters,', @replace_scope, 1) > 0) AND CHARINDEX(@scripted_value, @additional_parameters, 1) > 0
        BEGIN
            SET @message = 'Replacement in @additional_parameters: ' + @scripted_value + ' by ' + @replace_value;
            PRINT ' '
            PRINT @message;
            PRINT 'Old @additional_parameters: ' + @additional_parameters;
            SET @additional_parameters = REPLACE(@additional_parameters, @scripted_value, @replace_value);
            PRINT 'New @additional_parameters: ' + @additional_parameters;
        END

        IF (@replace_scope = N'*' OR CHARINDEX(N',@server,', @replace_scope, 1) > 0) AND CHARINDEX(@scripted_value, @server, 1) > 0
        BEGIN
            SET @message = 'Replacement in @server: ' + @scripted_value + ' by ' + @replace_value;
            PRINT ' '
            PRINT @message;
            PRINT 'Old @server: ' + @server;
            SET @server = REPLACE(@server, @scripted_value, @replace_value);
            PRINT 'New @server: ' + @server;
        END

        IF (@replace_scope = N'*' OR CHARINDEX(N',@database_name,', @replace_scope, 1) > 0) AND CHARINDEX(@scripted_value, @database_name, 1) > 0
        BEGIN
            SET @message = 'Replacement in @database_name: ' + @scripted_value + ' by ' + @replace_value;
            PRINT ' '
            PRINT @message;
            PRINT 'Old @database_name: ' + @database_name;
            SET @database_name = REPLACE(@database_name, @scripted_value, @replace_value);
            PRINT 'New @database_name: ' + @database_name;
        END

        IF (@replace_scope = N'*' OR CHARINDEX(N',@database_user_name,', @replace_scope, 1) > 0) AND CHARINDEX(@scripted_value, @database_user_name, 1) > 0
        BEGIN
            SET @message = 'Replacement in @database_user_name: ' + @scripted_value + ' by ' + @replace_value;
            PRINT REPLICATE('', @divider_length);
            PRINT @message;
            PRINT 'Old @database_user_name: ' + @database_user_name;
            SET @database_user_name = REPLACE(@database_user_name, @scripted_value, @replace_value);
            PRINT 'New @database_user_name: ' + @database_user_name;
        END

        IF (@replace_scope = N'*' OR CHARINDEX(N',@proxy_name,', @replace_scope, 1) > 0) AND CHARINDEX(@scripted_value, @proxy_name, 1) > 0
        BEGIN
            SET @message = 'Replacement in @proxy_name: ' + @scripted_value + ' by ' + @replace_value;
            PRINT ' '
            PRINT @message;
            PRINT 'Old @proxy_name: ' + @proxy_name;
            SET @proxy_name = REPLACE(@proxy_name, @scripted_value, @replace_value);
            PRINT 'New @proxy_name: ' + @proxy_name;
        END

        IF (@replace_scope = N'*' OR CHARINDEX(N',@output_file_name,', @replace_scope, 1) > 0) AND CHARINDEX(@scripted_value, @output_file_name, 1) > 0
        BEGIN
            SET @message = 'Replacement in @output_file_name: ' + @scripted_value + ' by ' + @replace_value;
            PRINT ' '
            PRINT @message;
            PRINT 'Old @output_file_name: ' + @output_file_name;
            SET @output_file_name = REPLACE(@output_file_name, @scripted_value, @replace_value);
            PRINT 'New @output_file_name: ' + @output_file_name;
        END

      END

      FETCH NEXT FROM replacements_cursor
        INTO @subsystems, @replace_scope, @scripted_value, @replace_value;
      
    END

    CLOSE replacements_cursor
    DEALLOCATE replacements_cursor
          
  END

  --\---------------------------------------------------------------------------------------------------------
  ---) PART 2: Fix environment references in ssis commands.
  --/---------------------------------------------------------------------------------------------------------
         
  --\
  ---) First check if there is something to do
  --/
  IF @subsystem = N'SSIS' AND CHARINDEX(N'/ENVREFERENCE', @command, 1) > 0 AND CHARINDEX(N'/ISSERVER “\”\SSISDB\', @command, 1) > 0
  BEGIN
    --\
    ---) Pull out @folder_name and @project_name from the @command variable value
    --/
    SELECT
      @startpos = CHARINDEX(N'/ISSERVER “\”\SSISDB\', @command, 1) + LEN(N'/ISSERVER “\”\SSISDB\'),
      -- @endpos = CHARINDEX(N'dtsx\””', @command, @startpos),
      @endpos = CHARINDEX(N'\', @command, @startpos + 1),
      @endpos_project = CHARINDEX(N'\', @command, @endpos + 1),
      @folder_name = SUBSTRING(@command, @startpos, @endpos - @startpos),
      @project_name = SUBSTRING(@command, @endpos + 1, @endpos_project - @endpos - 1);

      --\
      ---) Armed with the correct @folder_name and @project_name get the environment reference id.
      --/
      SELECT @reference_id = ISNULL((
          SELECT TOP 1
              er.reference_id
          FROM
              SSISDB.catalog.environments AS env
          JOIN
              SSISDB.catalog.folders AS fld
              ON fld.folder_id = env.folder_id
          JOIN
              SSISDB.catalog.projects AS prj
              ON prj.folder_id = fld.folder_id
          JOIN
              SSISDB.catalog.environment_references AS er
              ON er.project_id = prj.project_id
          WHERE
              fld.name = @folder_name
              AND prj.name = @project_name
      ), -1)

  END

  --\
  ---) If a valid environment reference id was found ..
  --/
  IF @reference_id != -1
  BEGIN
    SELECT
      --\
      ---) .. adjust the /ENVREFERENCE part of the @command so that it is followed by the retrieved @reference_id.
      --/
      @startpos = CHARINDEX(N'/ENVREFERENCE ', @command, 1),
      @endpos = CHARINDEX(N' ', @command, @startpos + + LEN(N'/ENVREFERENCE ') + 1);

      SELECT
          @new_reference = '/ENVREFERENCE ' + CONVERT(NVARCHAR, @reference_id),
          @new_command = LEFT(@command, @startpos - 1) + @new_reference + SUBSTRING(@command, @endpos, 8000);
      IF @new_command != @command
      BEGIN
          SET @message = 'Replacement in @command: fixing /ENVREFERENCE';
          PRINT ' '
          PRINT @message;
          PRINT 'Old @command: ' + @command;
          PRINT 'New @command: ' + @new_command;
          SET @command = @new_command;
      END
  END

  --\---------------------------------------------------------------------------------------------------------
  ---) PART 3: Now we have done our trick with the parameter values,
  ---) execute the built-in stored procedure sp_add_jobstep.
  --/---------------------------------------------------------------------------------------------------------

  EXECUTE @retval = dbo.sp_add_jobstep
      @job_id = @job_id,
      @job_name = @job_name,
      @step_id = @step_id,
      @step_name = @step_name,
      @subsystem = @subsystem,
      @command = @command,
      @additional_parameters = @additional_parameters,
      @cmdexec_success_code = @cmdexec_success_code,
      @on_success_action = @on_success_action,
      @on_success_step_id = @on_success_step_id,
      @on_fail_action = @on_fail_action,
      @on_fail_step_id = @on_fail_step_id,
      @server = @server,
      @database_name = @database_name,
      @database_user_name = @database_user_name,
      @retry_attempts = @retry_attempts,
      @retry_interval = @retry_interval,
      @os_run_priority = @os_run_priority,
      @output_file_name = @output_file_name,
      @flags = @flags,
      @proxy_id = @proxy_id,
      @proxy_name = @proxy_name,
      @step_uid = @step_uid OUTPUT

  RETURN(@retval)
END
GO

Create a generic install script

This install script consists of the following parts:

PART 1: Create temporary table #jobstep_replacements with specific replacements.

This is the part where you customize for your own environments.
Two things require further explanation: subsystems and replace-scope.
Each jobstep is defined for a specific subsystem. Valid values are:

Full name Code
Operating system (CmdExec) CmdExec
PowerShell PowerShell
Replication Distributor Distribution
Replication Merge Merge
Replication Queue Reader QueueReader
Replication Snapshot Snapshot
SQL Server Analysis Services Command ANALYSISCOMMAND
SQL Server Analysis Services Query ANALYSISQUERY
SQL Server Integation Services Package SSIS
Transact-SQL script (T-SQL) TSQL

The subsystem codes are used in SQL scripts generated by SSMS to create jobsteps.

Replace scopes is not an official term, I just called it that way.
Valid values are any (n)varchar parameter names of sp_add_jobstep, so:

  • @command
  • @additional_parameters
  • @server
  • @database_name
  • @database_user_name
  • @proxy_name
  • @output_file_name

The temporary table #jobstep_replacements contains the columns [subsystems] and [replace_scope].
Both can be filled with a commaseparated list to control the subsystems and parameters where the replacement should take place. A * wildcard means “all subsystems” or “all parameters”.

An example of part 1:

MSSQL-E08-jobscript-part1

--\------------------------------------------------------------------------------------
---) PART 1: Create temporary table #jobstep_replacements with specific replacements.
--/------------------------------------------------------------------------------------

IF OBJECT_ID('tempdb..#jobstep_replacements', 'U') IS NOT NULL
DROP TABLE #jobstep_replacements;

IF @@SERVERNAME != 'VWS63-SQL161' -- The server where the script was generated.
BEGIN
    CREATE TABLE #jobstep_replacements
    --\------------------------------------------------------------------------------------
    ---) IMPORTANT; do not change the structure of this temporary table because
    ---) stored procedure [usp_add_jobstep_wrapper] depends on it.
    --/------------------------------------------------------------------------------------
    (
    [id] INT IDENTITY(1, 1),
    [subsystems] NVARCHAR(128) NOT NULL, -- Use wildcard * or comma separated list with subsystem codes e.g. SSIS,TSQL (don't use spaces!)
    /*
    SUBSYSTEMS
    -------------------------------------- ---------------------
    Full name Code
    -------------------------------------- ---------------------
    Operating system (CmdExec) CmdExec
    PowerShell PowerShell
    Replication Distributor Distribution
    Replication Merge Merge
    Replication Queue Reader QueueReader
    Replication Snapshot Snapshot
    SQL Server Analysis Services Command ANALYSISCOMMAND
    SQL Server Analysis Services Query ANALYSISQUERY
    SQL Server Integation Services Package SSIS
    Transact-SQL script (T-SQL) TSQL
    */

    [replace_scope] NVARCHAR(128) NOT NULL, -- Use wildcard * or comma separated list with subsystem codes e.g. @command,@proxy_name (don't use spaces!)
    /*
    --------------------------------------
    REPLACE-SCOPES
    --------------------------------------
    @command
    @additional_parameters
    @server
    @database_name
    @database_user_name
    @proxy_name
    @output_file_name
    */

    [scripted_value] NVARCHAR(128) NOT NULL,
    [replace_value] NVARCHAR(128) NULL,
    [sort_order] INT NOT NULL DEFAULT(100)
    );
END

--\
---) Fill the temporary table, based on the current SQL Server Instance name.
---) Include the server on which the scripts were made in one IF statement, so the records can be updated later.
--/
IF @@SERVERNAME = 'VWS63-SQL161\TABULAR'
BEGIN
    INSERT INTO #jobstep_replacements
    ( [sort_order], [subsystems], [replace_scope], [scripted_value], [replace_value])
    VALUES
      (1, N'SSIS',
          N'@command',
          N'VWS63-SQL161\”',
          N'VWS63-SQL161\TABULAR\”'),

      (2, N'SSIS',
          N'@command',
          N'DEV',
          N'TST'),

     (3, N'TSQL',
          N'@command,@database_name',
          N'Demo_DEV',
          N'Demo_TST'),

     (4, N'PowerShell',
          N'@command',
          N'F:\Files\Development',
          N'F:\Files\Test')
END
ELSE IF @@SERVERNAME = 'PRODSERVER'
BEGIN
    INSERT INTO #jobstep_replacements
    ( [sort_order], [subsystems], [replace_scope], [scripted_value], [replace_value])
    VALUES
      (1, N'SSIS',
          N'@command',
          N'VWS63-SQL161\”',
          N'VWS63-SQL163\PRD\”'),

      (2, N'SSIS',
          N'@command',
          N'DEV',
          N'PRD'),

     (3, N'TSQL',
          N'@command,@database_name',
          N'Demo_DEV',
          N'Demo_PRD'),

     (4, N'PowerShell',
          N'@command',
          N'F:\Files\Development',
          N'F:\Files\Production')
END

PART 2: Smart sp_delete_job statements, using @job_name and IF EXISTS.

Those statements can be generated using a SQL query. This query is available as comment in the MSSQL-E08-jobscript.sql script (download the scripts here). Just select it and execute. Then you copy and paste from the query output window.

An example of part 2:

MSSQL-E08-jobscript-part2

--\------------------------------------------------------------------------------------
---) PART 2: Smart sp_delete_job statements, using @job_name and IF EXISTS
--/------------------------------------------------------------------------------------

USE [msdb]
GO
/* -- Query to generate sp_delete_job statements:
SELECT
    CASE sql.line_no
      WHEN 1 THEN 'IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE [name] = N'''+ name + ''')'
      WHEN 2 THEN ' EXEC msdb.dbo.sp_delete_job @job_name=N'''+ name + ''', @delete_unused_schedule=1;'
      WHEN 3 THEN 'GO'
    END AS [drop_sql_for_copy_and_paste]
FROM
    msdb.dbo.sysjobs j
CROSS JOIN -- To force new lines in the output, a cross join with 3 line_no rows is used.
    (SELECT 1 AS [line_no] UNION SELECT 2 UNION SELECT 3) AS [sql]
WHERE
    j.name like 'MSSQL-E08%' -- IMPORTANT: here you filter on the jobs to create delete statements for.
ORDER BY
    j.name,
    sql.line_no
*/

IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE [name] = N'MSSQL-E08-Job1-SSIS')
  EXEC msdb.dbo.sp_delete_job @job_name=N'MSSQL-E08-Job1-SSIS', @delete_unused_schedule=1;
GO
IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE [name] = N'MSSQL-E08-Job2-TSQL')
  EXEC msdb.dbo.sp_delete_job @job_name=N'MSSQL-E08-Job2-TSQL', @delete_unused_schedule=1;
GO
IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE [name] = N'MSSQL-E08-Job3-Powershell')
  EXEC msdb.dbo.sp_delete_job @job_name=N'MSSQL-E08-Job3-Powershell', @delete_unused_schedule=1;
GO

PART 3: Create jobs, script generated by SSMS.

Here you paste the script to create jobs that was generated by SQL Server Management Studio. After pasting, replace sp_add_jobstep by usp_add_jobstep_wrapper.

An example of part 3:

MSSQL-E08-jobscript-part3

--\------------------------------------------------------------------------------------
---) PART 3: Create jobs, script generated by SSMS
--/------------------------------------------------------------------------------------
--\
---) IMPORTANT NOTE: You can generate the part below as follows:
---) In SQL Server Management Studio select a SQL Agent job, then press F7
---) In the Object Explorer Details Pane, select the jobs you want to create a script for.
---) Then rightclick and in the context menu select:
---) > Script Job As > CREATE To > New Query Editor Window
---) In the script generated with SQL Server Management Studio, replace
---) sp_add_jobstep
---) by
---) usp_add_jobstep_wrapper
--/
USE [msdb]
GO

/****** Object: Job [MSSQL-E08-Job1-SSIS] Script Date: 19-12-2016 18:59:58 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 19-12-2016 18:59:58 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'MSSQL-E08-Job1-SSIS',
  @enabled=1,
  @notify_level_eventlog=0,
  @notify_level_email=0,
  @notify_level_netsend=0,
  @notify_level_page=0,
  @delete_level=0,
  @description=N'No description available.',
  @category_name=N'[Uncategorized (Local)]',
  @owner_login_name=N'VWS63-SQL161\Hans', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [AnyPackage.dtsx] Script Date: 19-12-2016 18:59:58 ******/
EXEC @ReturnCode = msdb.dbo.usp_add_jobstep_wrapper @job_id=@jobId, @step_name=N'AnyPackage.dtsx',
  @step_id=1,
  @cmdexec_success_code=0,
  @on_success_action=1,
  @on_success_step_id=0,
  @on_fail_action=2,
  @on_fail_step_id=0,
  @retry_attempts=0,
  @retry_interval=0,
  @os_run_priority=0, @subsystem=N'SSIS',
  @command=N'/ISSERVER “\”\SSISDB\DEV\SSIS-E10-P2\AnyPackage.dtsx\”” /SERVER “\”VWS63-SQL161\”” /ENVREFERENCE 4 /Par “\”$ServerOption::LOGGING_LEVEL(Int16)\””;1 /Par “\”$ServerOption::SYNCHRONIZED(Boolean)\””;True /CALLERINFO SQLAGENT /REPORTING E',
  @database_name=N'master',
  @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Schedule for MSSQL-E08-Job1-SSIS',
  @enabled=0,
  @freq_type=4,
  @freq_interval=1,
  @freq_subday_type=1,
  @freq_subday_interval=0,
  @freq_relative_interval=0,
  @freq_recurrence_factor=0,
  @active_start_date=20161216,
  @active_end_date=99991231,
  @active_start_time=60000,
  @active_end_time=235959,
  @schedule_uid=N'6d24a58d-f800-4341-ab5b-41fbe4923da8'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

/****** Object: Job [MSSQL-E08-Job2-TSQL] Script Date: 19-12-2016 18:59:58 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 19-12-2016 18:59:58 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'MSSQL-E08-Job2-TSQL',
  @enabled=1,
  @notify_level_eventlog=0,
  @notify_level_email=0,
  @notify_level_netsend=0,
  @notify_level_page=0,
  @delete_level=0,
  @description=N'No description available.',
  @category_name=N'[Uncategorized (Local)]',
  @owner_login_name=N'VWS63-SQL161\Hans', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Demo_DEV] Script Date: 19-12-2016 18:59:58 ******/
EXEC @ReturnCode = msdb.dbo.usp_add_jobstep_wrapper @job_id=@jobId, @step_name=N'Demo_DEV',
  @step_id=1,
  @cmdexec_success_code=0,
  @on_success_action=1,
  @on_success_step_id=0,
  @on_fail_action=2,
  @on_fail_step_id=0,
  @retry_attempts=0,
  @retry_interval=0,
  @os_run_priority=0, @subsystem=N'TSQL',
  @command=N'SELECT * from sys.objects',
  @database_name=N'Demo_DEV',
  @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Schedule for MSSQL-E08-Job2-TSQL',
  @enabled=0,
  @freq_type=4,
  @freq_interval=1,
  @freq_subday_type=1,
  @freq_subday_interval=0,
  @freq_relative_interval=0,
  @freq_recurrence_factor=0,
  @active_start_date=20161216,
  @active_end_date=99991231,
  @active_start_time=70000,
  @active_end_time=235959,
  @schedule_uid=N'a25c43d5-8543-4723-903b-beeb6d9a07a3'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

/****** Object: Job [MSSQL-E08-Job3-Powershell] Script Date: 19-12-2016 18:59:58 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 19-12-2016 18:59:58 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'MSSQL-E08-Job3-Powershell',
  @enabled=1,
  @notify_level_eventlog=0,
  @notify_level_email=0,
  @notify_level_netsend=0,
  @notify_level_page=0,
  @delete_level=0,
  @description=N'No description available.',
  @category_name=N'[Uncategorized (Local)]',
  @owner_login_name=N'VWS63-SQL161\Hans', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Powershell step] Script Date: 19-12-2016 18:59:59 ******/
EXEC @ReturnCode = msdb.dbo.usp_add_jobstep_wrapper @job_id=@jobId, @step_name=N'Powershell step',
  @step_id=1,
  @cmdexec_success_code=0,
  @on_success_action=1,
  @on_success_step_id=0,
  @on_fail_action=2,
  @on_fail_step_id=0,
  @retry_attempts=0,
  @retry_interval=0,
  @os_run_priority=0, @subsystem=N'PowerShell',
  @command=N'dir “F:\Files\Development\*.csv”',
  @database_name=N'master',
  @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Schedule for MSSQL-E08-Job3-Powershell',
  @enabled=0,
  @freq_type=4,
  @freq_interval=1,
  @freq_subday_type=1,
  @freq_subday_interval=0,
  @freq_relative_interval=0,
  @freq_recurrence_factor=0,
  @active_start_date=20161216,
  @active_end_date=99991231,
  @active_start_time=110000,
  @active_end_time=235959,
  @schedule_uid=N'a6f0e0ca-d27a-4d3f-a349-4a53c0392541'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

It’s demo time ..

It is also time to set up a demo, otherwise you have to believe me that this works, and I always like to prove it.
For this I have deployed two SSIS projects from an earlier blog post to two SQL Instances on my virtual server. One instance is the default instance MSSQLSERVER and the other one is called TABULAR (it has SSAS installed in TABULAR mode).
For the demo I will pretend however that the default instance is my development environment and the TABULAR instance is my test.
On the default instance I create three SQL Agent jobs for SSIS-, TSQL and PowerShell jobsteps, respectively.

Jobs created for the demo ..

Then I create a script from the jobs, paste it into script MSSQL-E08-jobscript.sql and replace sp_add_jobstep by usp_add_jobstep_wrapper.

Generating a CREATE script for the jobs ..

After doing the necessary preparation to install usp_add_jobstep_wrapper on both instances, I can create the SQL Agent jobs on the TABULAR instance.
The output window shows which replacements were done.

As you can see in the output window, multiple replacements will be applied one after the other ..

Replacement in the TSQL jobstep ..

Replacement in the PowerShell jobstep..

I can still use the script on the default instance, on that instance simply no replacements will be done.

No replacements done on the default instance where the CREATE script was generated ..

Download the scripts here.

Conclusion / Wrap up

In this blog post you could read how to cope with some peculiarities of SQL Server Management Studio when it creates SQL Agent job scripts.
I have explained how you can make your script generic for all environments, which makes it more suitable for putting it under source control.
For this a special stored procedure, [dbo].[usp_add_jobstep_wrapper] is installed in the [msdb] database and a generic install script must be made that is partly developed and partly pasted from the SSMS Script.

And, of course, have a merry Christmas and a happy New Year!

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

Quiet install of SQL Server + SP1 + all tools (SQL Server Series)

Introduction

Due to some serious trouble with my SQL Server 2016 Virtual Machine I had to reinstall SQL Server + SP1 + SSMS + Visual Studio 2015 + SQL Server Data Tools again on an older backup of the Virtual Machine.
So the article I had planned next is delayed.
But my bad luck was still inspiration for this unplanned article.

Problem

How can I do quiet installations of SQL Server 2016 including all service packs and tools?

Solution

So what I want to do is install the following products with minimum effort and user interaction after each other, in the Netherlands we would say “gewoon, omdat het kan” (this means something like “just because it is possible”):

  1. SQL Server 2016 Developer Edition RTM
  2. SQL Server 2016 SP1
  3. SQL Server Management Studio 2016
  4. Visual Studio Community 2015
  5. SQL Server Data Tools

The structure of this article will be practical and straight forward.
For each program to install I will give comments and details about the command line options, one or more external references for more info when required, and a location to download the software.

A few common remarks that are applicable to all programs:

  • I have chosen for “unattended but visible” installations when possible. This means there is no user interaction required, but you can see what is happening.
    These switches are either called /qs or /passive.
  • Before doing an unattended install you have to download all media. I mounted one ISO (SQL Server) as DVD drive in my Hyper V Virtual machine, and copied the other setup files into a Virtual harddisk X. (e.g. mounted the downloaded ISO file of SSDT and copied the files to X:\Install\SSDT).
  • Also /norestart is used whenever possible. Not because I do not want to reboot, but because the reboot is taken care of in the Powershell script (using shutdown -r -t 0), so I can first create an empty dummy file. When this file exists, the script knows that the setup has already run.
  • And last but not least: I think it is appropriate to thank Microsoft Corporation. All those software above is FOR FREE now (but please do respect the sometimes limiting license terms)! This is heaven for any developer just to play around without any license cost. So: Microsoft, a sincere Thank You.

Finally a PowerShell script (download the script here) is used that calls all installers, one after the other. You can choose to reboot inbetween, and then run the same script again.

SQL Server 2016 Developer Edition RTM

Comments and command line options

Two things are important here, you must add /IACCEPTSQLSERVERLICENSETERMS and you must have a ConfigurationFile that specifies all the details for the installation.
The simplest way to obtain a ConfigurationFile is to start the setup manually and walk through the wizard until it is ready to install. Then a path to the ConfigurationFile.ini is shown.
Save this file and you can use it for future installations.

To make the configuration file suitable for a “progress only” installation, set QUIETSIMPLE=”True”.
Also put a semicolon in front of UIMODE to make it comment, because it is not possible to use QUIET or QUIETSIMPLE together with UIMODE.
mssql-e07-472Changes needed in the SQL Server Configuration File ..

External references

Install SQL Server 2016 Using a Configuration File.

Where to download

SQL Server 2016 Developer Edition Download (Microsoft Account required).

SQL Server 2016 SP1

Comments and command line options

While writing this article I noticed that on the page above the installation media of SQL Server 2016 is also available including SP1, so in that case you do not need to download SP1 seperately.
If you need SP1, e.g. because you have existing installation media (maybe other than Developer Edition) the following can be said about the command line options:
I have used /action=Patch /allinstances /qs /IAcceptSQLServerLicenseTerms.
If you not want to do all instances on a server, check the external references below for more info.

External references

Silent install info on Technet.

Where to download

Microsoft® SQL Server® 2016 Service Pack 1 (SP1) download.

SQL Server Management Studio 2016

Comments and command line options

The command line options used are /install /passive /norestart.

External references

Performing a Silent Install of SQL Server Management Studio (2016) by Sven Aelterman.

Where to download

Download SQL Server Management Studio (SSMS).

Visual Studio Community 2015

Comments and command line options

Microsoft recommends to install Visual Studio 2015 before SQL Server Data Tools, as follows:
“We recommend installing Visual Studio 2015 prior to applying this update. Installing this update will replace SSDT RTM in Visual Studio 2015 with the latest version.
If you do not have Visual Studio 2015, SSDT will install the Visual Studio 2015 Integrated shell and Visual Studio 2015 Isolated shell with limited feature support for SQL Server Database and BI Projects.”

I like to install Visual Studio, so I can do other stuff like creating console applications. If you are sure you will only use the Business Intelligence “Suite” you could skip installing Visual Studio.
The command line options used are /Passive /NoRestart /Log “X:\Install\VSTUD_LOG\VSTUD.log”.
These options lead to an installation of about 7.2 GB.
It is best to provide a logfile name in a seperate “dedicated” folder, because not one, but hundreds of logfiles are created. I created the folder before manually. I have not tested (sorry, forgot) if the setup would create the folder if it would not exist.

I must warn you also that the command line option /Full leads to a massive install of about 56 GB and takes quite a long time. Because the default installation installs all I possibly need I did not use /Full.

mssql-e07-464When you run vs_community.exe /? you get an overview of the command line parameters.

External references

Using Command-Line Parameters to Install Visual Studio.
How to: Create and Run an Unattended Installation of Visual Studio.
Install Visual Studio 2015.

Where to download

VS 2015 Community Edition download.

SQL Server Data Tools

Comments and command line options

Hoorah, Microsoft ended the confusion and separate setups for SSDT (Visual Studio project type for Database projects) and SSDT-BI (formerly known as BIDS with project types for SSIS-, SSRS- and SSAS-development).
The current installer contains both!
One thing of the command line options really caught me and therefore a
WARNING!
Do not use a / before the command line options INSTALLAS, INSTALLIS, INSTALLRS and INSTALLALL!
However a slash is not shown in the help screen below, it is so easy to assume that a / is required (or overlook this on the help screen). Intuitively you would expect that all command line parameters start with /, but believe me I did this and both the installed software as the install log file proved that the options where not installed while I provided /INSTALLALL=1 as command line parameter.
A line of the logfile: Condition ‘INSTALLIS = 1 OR INSTALLALL = 1’ evaluates to false.
When I used INSTALLALL=1 as command line parameter, all Business Intelligence project types where installed!

mssql-e07-463When you run SSDTSETUP.EXE /? you get an overview of the command line parameters.

External references

Blog article by Dandy Weyn.

Where to download

Download SQL Server Data Tools (SSDT).
SQL Server Data Tools in Visual Studio 2015.

Using Powershell to install all

Please note that the installation is not “fully” automated. After each reboot, you have to restart the Powershell script, and it will continue with the first program that is not installed yet.
If you like, you could create a Windows Scheduled Task and start the script “with the highest privileges” after a reboot. I tried this but it seemed not to work very well, maybe I did something wrong.
So I just start the script a few times manually after reboot, not a big deal IMHO (but of course this depends on how you intend to use the script).

From a technical point of view, the comment in the script should help you further:
mssql-e07-470“Help” section of the PowerShell script. Please read!

mssql-e07-471The MAIN SCRIPT contains multiple of this kind of code blocks, for each installer one.

mssql-e07-465Running the PowerShell script ..

mssql-e07-466My Install folder with some “done” files ..

mssql-e07-468Command-based dialog after an installation is completed ..

mssql-e07-467Printscreen of installation in progress ..

mssql-e07-469After installation both “SQL Server Data Tools 2015” and “Visual Studio 2015” are available as Apps on Windows Server 2012 R2. In my experience you can use both for all project types, however I tend to use Visual Studio ..

Where to download

Download the Powershell script here.

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

Using sensitive parameters (SSIS Series)

Introduction

This time I want to tell something about the use of sensitive parameters. Passwords, connection strings, anything that is .. uhm .. sensitive.

Problem

SSIS Package Parameters and Environment variables can hold sensitive values, like passwords, connection strings and so on. You do not want these to be seen by people who are not authorized to have access to this information.

Solution

First I want to give a *short* intro on how (not) to save sensitive parameters in SSIS.
There are several options which are explained enough on other internet sites:

  • DontSaveSensitive
  • EncryptSensitiveWithUserKey
  • EncryptSensitiveWithPassword
  • EncryptAllWithPassword
  • EncryptAllWithUserKey

My opinion on those options:

  • When you use a *WithUserKey value, you might get into trouble when someone else from the development team wants to work on a SSIS Package.
  • When you use a *WithPassword value, you have to type (or paste) the password every time you open the package in Visual Studio. I personally really disklike that.
  • But more important, why would you care about saving the sensitive data at all? Thinking about professional SSIS package development, you would not want sensitive data like passwords and connection strings hard coded in the package anyway, while you can simply configure your packages from environment variables as explained in two of my earlier blog posts. So, as you might guess, DontSaveSensitive is my favorite option.

So the rest of this article is based on using the DontSaveSensitive property.
You have to set it in the SSIS package, but also in the project properties:
ssis-e11-436Setting the package property “ProtectionLevel”
ssis-e11-437Setting the Project Protection Level

I have set up a very simple demo. It consists of the following steps:

Set up a folder and environment in SSISDB if not done already

See the script for this in my previous blog post.

Create a sensitive environment variable “MyDBConnectionString”.

In the demo I use my existing DEV folder with the DEV environment inside that folder.
In my case I use Windows Integrated Security, therefore the connection string on itself is not sensitive. I’ll just use this example, because a connection string could also contain a username and password.

ssis-e11-458The environment variable “MyDBConnectionString”. Check the checkbox to make it sensitive

Create a SSIS Package with a sensitive parameter “MyDBConnectionString”

ssis-e11-454
Do I need to say more?

In the SSIS Package create a (non-sensitive) variable “MyDBConnectionString”

Not so exiting either, except that you should set Raise Change Event to True for the variable, so that any other properties set by an expression using the variable will certainly change when the variable changes.
Before you can do that, you must make sure that the column is visible. The printscreens below explain how.
ssis-e11-439Clicking on this icon shows the Variable Grid Options
ssis-e11-440On the Variable Grid Options check “Raise event when variable value changes”
ssis-e11-441For the variable set “Raise Change Event” to True

ssis-e11-455Here you can see that the Expression @[User::MyDBConnectionString] is used to set the ConnectionString property of the OLEDB Connection Manager OLE_SRC_MyDatabase.

Copy the parameter value to the variable value using a Script Task

The “problem” is that you cannot use the sensitive parameter in an expression that uses the value for a non-sensitive property, in my case the ConnectionString property of an OLEDB Connection Manager.
This error is shown:
ssis-e11-435

This is why you have to copy the value of the sensitive parameter to a non-sensitive variable first using a Script Task.
I personally think it is a pity that Microsoft did not create a GETSENSITIVEVALUE(« SensitiveParameterOrVariable ») expression, so you could use an Expression Task, but they didn’t, so a script task is the way to go.
Add a Script Task to the control flow of your SSIS Package and adjust public void Main() as below:

ScriptMain.cs
public void Main()
{
  try
  {
    // Copy sensitive value to normal (non-sensitive) variable, so that it can be used in an expression.
    string value = Dts.Variables["$Package::MyDBConnectionString"].GetSensitiveValue().ToString();
    if (!string.IsNullOrEmpty(value))
    {
      // Only copy, if not empty. Else preserve design time value, for instance for debugging.
      Dts.Variables["User::MyDBConnectionString"].Value = value;
    }
    Dts.TaskResult = (int)ScriptResults.Success;
  }
  catch (Exception e)
  {
    Dts.Log(e.Message, 0, null);
    Dts.TaskResult = (int)ScriptResults.Failure;
    throw (e);
  }
  Dts.TaskResult = (int)ScriptResults.Success;
}

The control flow of the demo package now looks as follows:
ssis-e11-460
The final control flow of the demo package

To demonstrate how it works, the “design time” connection string, that is stored in the variable MyDBConnectionString, is different from the sensitive environment variable MyDBConnectionString.
I have added an Execute SQL Task SQL_FooBar that uses the OLEDB Connection Manager OLE_SRC_MyDatabase.
The connection string in the variable connects to a database named FF (which is Dutch SMS talk for “Just a moment”/temporary).
The connection string in the sensitive environment variable connects to tempdb.
To proof that the connection string from the sensitive environment variable (which passes it to the package parameter) is used during package execution, I take the FF database offline, after the OLEDB Connection Manager is configured.
ssis-e11-443Taking database FF offline ..

So when the connection manager would not be configured from the sensitive parameter during run time, the Execute SQL Task would certainly fail during execution.

Now it’s time to deploy my project with the testpackage to my DEV folder in SSISDB. I do not have screenshots of that, because I assume that you will know how to do this. If not, check my previous blog posts.

ssis-e11-456
ssis-e11-450This time I do the configuration manually: in the project I add a reference to the DEV environment (not in the picture), then I connect the environment variable to the package parameter.

After it is configured, I execute the package.

ssis-e11-451
ssis-e11-452

Shortly after it has succeeded, proof for me that it was using the configured connectionstring with database tempdb, and not the design time value with database FF.

Considerations for debugging

If you want to debug the SSIS package in Visual Studio you have a problem: due to DontSaveSensitive the package parameter will have no value, and you cannot debug the package.
My workaround for this is to put the debug value for the sensitive value in the SSIS variable. In the Script Task the variable will only get the value from the parameter when it is not empty. So during debug time, the variable value will be preserved.
If your value is too sensitive for this, either use a less sensitive value referring to a test database/server etc., or remove the value from the variable after debugging the package. In the latter case, also delete copies of the package in the bin folder.

Will my sensitive value be safe in an environment variable?

Short answer: yes, I think pretty safe.
Why? If a DBA unticks the “Sensitive” checkbox, the value is wiped out and cannot be read.
Of course a DBA or hacker with sysadmin access to the SQL Instance and knowledge could deploy a SSIS Package, just to pull out the sensitive value. But there are little things a DBA cannot do..

Conclusion / Wrap up

In this article you could read some practical insights on using the ProtectionLevel DontSaveSensitive for a SSIS package containing a sensitive parameter configured from a sensitive environment variable during execution.
Steps to take are:

  1. Create a folder in SSISDB. In that folder, create an environment. In my example both the folder and environment were called DEV.
  2. Create a sensitive environment variable.
  3. Create a SSIS package with a sensitive parameter and a (non-sensitive) variable with the same name.
  4. In the SSIS package create a script task to set the value of the variable to the value of the parameter.
  5. In the SSIS package use an expression to use the variable value where needed, for example in a OLEDB Connection Manager or a FTP Task.

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

How to automate your SSIS package deployment and configuration (SSIS Series)

Introduction

Hi there, today I want to share my scripts on package deployment. As manual deployment can be cumbersome and prone to errors, I have automated this boring repetitive task.
This article is based on SQL Server 2016, but I have included scripts to be used for SQL Server 2012 and 2014.

Problem

How to automate SSIS Package deployment.

Solution

  1. Setting up folder and environment
  2. Deploy your SSIS Packages
  3. Setup package configuration (or: connect package- and project parameters to environment variables)
  4. Glueing everything together using Powershell

Setting up folder and environment

As you could read in one of my previous posts Using the project deployment model with environments I usually create a one-on-one relation between folders and environments.
So a folder = an environment, and within the folder several SSIS projects can exist.
What I have seen others do is creating a “Generic”environment. That is also a possibility, this is a matter of personal choice. I prefer having the environment having an explicit name identical to the foldername (e.g. DEV), just to decrease the risk that any changes are made in the wrong Generic environment variable values.

Our main goal for this step is to have a way to automate setting up a folder and environment, including all (scripted) environment variables. This script should be rerunable to be able to add or update environment variables when needed.
One thing you should know is that the database SSISDB has a number of stored procecures in the catalog schema, that I use to achieve this.
Well, here’s the script. Don’t bother copy and paste while you can download all scripts here.

pre_deployment.sql

--\ -------------------------------------------------------------------
---) Script to create folder, environment and settings.
--/ -------------------------------------------------------------------
/*
(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),
  --)> Just an autonumber to be able to go through the table without cursor.
  [variable_name] nvarchar(128),
  --) Name of the variable
  [data_type] nvarchar(128),
  --) Variable datatype e.g. [ Boolean | Byte | DateTime | Decimal | Double | Int16 | Int32 | Int64 | SByte | Single | String | UInt32 | UInt64 ]
  --) Check all possible values using this query: SELECT DISTINCT [ssis_data_type] FROM [SSISDB].[internal].[data_type_mapping]
  [sensitive] bit,
  --) Indication of the environment variable is sensitive (e.g. a password).
  [value] nvarchar(4000),
  --) The variable value.
  [description] nvarchar(1024),
  --) Extra description for the variable.
  [create_only] bit
  --) Indication that a variable should only be created when it does not exist yet, but should not be replaced when it already exists.
  --) (so the value in the script does not overwrite the existing value).
  --) In this way you can prevent later configuration changes in an environment being undone by the values in the script.
  )

DECLARE @id int
DECLARE @max_id int
DECLARE @folder_id int
DECLARE @folder_name sysname = N'unknown'
DECLARE @environment_name nvarchar(128) = N'unknown'
DECLARE @environment_description nvarchar(128) = N'unknown'
DECLARE @variable_name nvarchar(128)
DECLARE @data_type nvarchar(128)
DECLARE @sensitive bit
DECLARE @value sql_variant
DECLARE @description nvarchar(1024)
DECLARE @create_only bit --
DECLARE @exists bit
DECLARE @nsql nvarchar(max)
DECLARE @message nvarchar(255)

DECLARE @false BIT = 0, @true BIT = 1

--\
---) Environment settings.
---) This is a generic script for all environments, using the @@SERVERNAME (SQL Server Instance name)
---) to set values right for each environment.
--/

IF @@SERVERNAME LIKE 'VWS63-SQL1%1'
BEGIN
    SELECT
      @folder_name = N'DEV',
      @environment_name = N'DEV',
      @environment_description = N'My development environment'
END
IF @@SERVERNAME = '<SQL INSTANCE NAME>'
BEGIN
    SELECT
      @folder_name = N'TST',
      @environment_name = N'TST',
      @environment_description = N'My test environment'
END
IF @@SERVERNAME = '<SQL INSTANCE NAME>'
BEGIN
    SELECT
      @folder_name = N'PRD',
      @environment_name = N'PRD',
      @environment_description = N'My production environment'
END

IF @environment_name = N'unknown'
BEGIN
    RAISERROR('Unknown environment', 16, 1)
    -- Make sure that the script does not continue when the environment settings could not be set due to an unknown @@SERVERNAME.
    GOTO Finally
END

----\
-----) Create folder, when needed.
----/
IF NOT EXISTS (
    SELECT 1
    FROM [SSISDB].[internal].[folders] f
    WHERE f.name = @folder_name
    )
BEGIN
    SET @message = 'Folder “' + @folder_name + '” is being created.'
    RAISERROR(@message , 0, 1) WITH NOWAIT;
    EXEC [SSISDB].[catalog].[create_folder] @folder_name, @folder_id OUTPUT;
END

--\
---) Create environment, when needed.
--/
IF NOT EXISTS (
    SELECT 1
    FROM [SSISDB].[internal].[environments] e
    JOIN [SSISDB].[internal].[folders] f
        ON e.folder_id = f.folder_id
    WHERE e.[environment_name] = @environment_name
        AND f.name = @folder_name
    )
BEGIN
    SET @message = 'Environment “' + @environment_name + '” is being created.'
    RAISERROR(@message , 0, 1) WITH NOWAIT;
    EXEC [SSISDB].[catalog].[create_environment] @folder_name, @environment_name, @environment_description;
END

IF @environment_name = N'DEV'
BEGIN
  INSERT INTO @environment_variables_table
      ( [create_only], [variable_name], [data_type], [sensitive], [value], [description] )
  VALUES
  -- Just a few examples of environment variables, add your own here or generate this part from existing environments or deployed packages, see scripts below.
      ( @true, N'Parameter1', N'Int32', @false, N'1', N'Example parameter 1 (DEV).' )
    , ( @true, N'Parameter2', N'String', @false, N'Hello world from script (DEV).', N'Example parameter 2 (DEV).' )
    , ( @true, N'Parameter3', N'Decimal', @false, N'9.95', N'Example parameter 3 (DEV).' )
    , ( @true, N'ProjectParameter1', N'String', @false, N'Hello world from script (DEV).', N'Example projectparameter 1 (DEV).' )
    , ( @true, N'ProjectParameter2', N'Int32', @false, N'4', N'Example projectparameter 2 (DEV).' )
    
END

IF @environment_name = N'TST'
BEGIN
  INSERT INTO @environment_variables_table
      ( [create_only], [variable_name], [data_type], [sensitive], [value], [description] )
  VALUES
  -- Just a few examples of environment variables, add your own here or generate this part from existing environments or deployed packages, see scripts below.
      ( @true, N'Parameter1', N'Int32', @false, N'1', N'Example parameter 1 (TST).' )
    , ( @true, N'Parameter2', N'String', @false, N'Hello world from script (TST).', N'Example parameter 2 (TST).' )
    , ( @true, N'Parameter3', N'Decimal', @false, N'9.95', N'Example parameter 3 (TST).' )
    , ( @true, N'ProjectParameter1', N'String', @false, N'Hello world from script (TST).', N'Example projectparameter 1 (TST).' )
    , ( @true, N'ProjectParameter2', N'Int32', @false, N'4', N'Example projectparameter 2 (TST).' )
    
END

IF @environment_name = N'PRD'
BEGIN
  INSERT INTO @environment_variables_table
      ( [create_only], [variable_name], [data_type], [sensitive], [value], [description] )
  VALUES
  -- Just a few examples of environment variables, add your own here or generate this part from existing environments or deployed packages, see scripts below.
      ( @true, N'Parameter1', N'Int32', @false, N'1', N'Example parameter 1 (PRD).' )
    , ( @true, N'Parameter2', N'String', @false, N'Hello world from script (PRD).', N'Example parameter 2 (PRD).' )
    , ( @true, N'Parameter3', N'Decimal', @false, N'19.95', N'Example parameter 3 (PRD).' )
    , ( @true, N'ProjectParameter1', N'String', @false, N'Hello world from script (PRD).', N'Example projectparameter 1 (PRD).' )
    , ( @true, N'ProjectParameter2', N'Int32', @false, N'4', N'Example projectparameter 2 (PRD).' )
    
END

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],
      @create_only = v.[create_only],
      @exists = 0
    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
        JOIN
            [SSISDB].[internal].[folders] f
            ON e.folder_id = f.folder_id
        WHERE
            v.[name] = @variable_name
            AND e.environment_name = @environment_name
            AND f.name = @folder_name
        )
    BEGIN
        IF @create_only = 1
        BEGIN
            SET @message = @variable_name + ' already exists and is not replaced.'
            RAISERROR(@message , 0, 1) WITH NOWAIT;
            SET @exists = 1;
        END ELSE 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
    END

    IF @exists = 0
    BEGIN
        SET @message = 'Creating variable “' + @variable_name + '”.';
        RAISERROR(@message , 0, 1) WITH NOWAIT;

        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;
    END
    SET @id = @id + 1
END

RAISERROR('Creating folder, environment and settings has completed ..', 0, 1) WITH NOWAIT;

Finally:

GO
--\
---) ON a server where the VARIABLES already exist, you can use this query to generate the 'VALUES' part of the INSERT command used above.
--/
/*
DECLARE @environment_name nvarchar(128) = N'DEV'

SELECT
    m.*,
    v.name,
    N', ( N''' + v.name + ''', N''' + m.[ssis_data_type] + ''', '
      + CONVERT(NCHAR(1), v.sensitive) + ', N'''
      + CASE v.sensitive
        WHEN 0 THEN CONVERT(NVARCHAR(4000), v.value)
        ELSE '< type sensitive value here TODO >' END
      + ''', N''' + v.description + ''' )' AS [sql_values_part]
FROM
    [SSISDB].[internal].[environment_variables] v
JOIN
    [SSISDB].[internal].[environments] e
   ON e.environment_id = v.environment_id
JOIN
    [SSISDB].[internal].[folders] f
    ON e.folder_id = f.folder_id
JOIN
    [SSISDB].[internal].[data_type_mapping] m
    ON m.sql_data_type = v.base_data_type
    AND ( m.sql_data_type != 'tinyint' OR m.ssis_data_type = 'Byte')
WHERE
    e.environment_name = @environment_name
*/

--\
---) ON a server where the PACKAGES are ALREADY DEPLOYED, you can use this query to generate the 'VALUES' part of the INSERT command used above.
---) Manually check the output before running the sql script!
--/
/*
SELECT
  N', ( N''' + q.[variable_name] + ''', N''' + q.[data_type] + ''', '
    + CONVERT(NCHAR(1), q.[sensitive]) + ', N'''
    + CASE q.sensitive
        WHEN 0 THEN CONVERT(NVARCHAR(4000), q.value)
        ELSE '< type sensitive value here TODO >' END
    + ''', N''' + q.[description] + ''' )' AS [sql_values_part]
FROM
  (
  SELECT TOP 2147483647
      prm.[parameter_name] AS [variable_name],
      prm.[parameter_data_type] AS [data_type],r
      prm.[sensitive],
      COALESCE(prm.[default_value], prm.[design_default_value]) AS [value],
      prm.[description],
      ROW_NUMBER() OVER (PARTITION BY prm.[parameter_name] ORDER BY prm.[parameter_name]) AS [Occurrence]
  FROM
      [SSISDB].[internal].[object_parameters] prm
  -- [variable_name], [data_type], [sensitive], [value], [description]
  WHERE
      prm.[value_type] = 'V'
      AND prm.[parameter_name] NOT LIKE 'CM.%'
      AND LEFT(prm.[parameter_name], 1) != '_'
      AND NOT ( prm.[object_type] = 30 AND LEFT(prm.[object_name], 1) = '_') -- Naming convention for internally used SSIS Packages: start with _
  ORDER BY
      prm.[parameter_name]
  ) q
WHERE
  q.Occurrence = 1
*/

GO

ssis-e10-428
Example output of this script, when executed using Management Studio.

ssis-e10-429
And the second time, when a parameter is added and “create_only” is true for the existing parameters.

Deploy your SSIS Packages

This is about how to deploy an .ispac file in an automated way.
An .ispac file is a zipfile that contains all packages in a SSIS project that can be deployed all at once. You create it when you chose Build or Rebuild in Visual Studio when you have the SSIS project open.
By the way, if you rename it and give it a .zip extension, you can simply look what is inside.

Amongst the files you can download here there are demo SSIS projects that I used for this on SQL Server 2012, 2014 and 2016.

ssis-e10-423
The demo SSIS project ..
ssis-e10-424
Menu-option to rebuild the SSIS solution ..
ssis-e10-425
ssis-e10-426
After rebuilding the solution from the menu you will find the ispac files in the bin\<configuration name> folder of each project. In the screenprints above you can see how the bin subfolder is related to the chosen configuration in Visual Studio.

To install an ispac file, I use the command line options of the Integration Services Deployment Wizard.

For a default installation you can find the Integration Services deployment wizard (ISDeploymentWizard.exe) here:

Version Path
SQL Server 2012 C:\Program Files\SQL Server\110\DTS\Binn
SQL Server 2014 C:\Program Files\SQL Server\120\DTS\Binn
SQL Server 2016 C:\Program Files\SQL Server\130\DTS\Binn

You will find the exact command line in one of the following paragraphs, where all steps are glued together.
On 64-bit machines there is also a 32-bit version of this tool, but to be honest I do not see a reason why you would want to use the 32-bit version instead of the 64-bit, therefore I did not list the paths here.

ssis-e10-427
After installation you can find the folder, project and environment here ..

Setup package configuration

After the packages/SSIS projects are deployed, the next thing to do is adding an environment reference to the project(s) and connecting environment variables to package parameters and project parameters, based on the name.
I use this script for that:

post_deployment.sql

--\ -------------------------------------------------------------------
---) Script to add environment reference to project(s) and to connect
---) environment variables to package parameters, based on the name
--/ -------------------------------------------------------------------
/*
(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 @object_parameter_value_table TABLE(
  [id] int identity(1, 1),
  [object_type] smallint,
  [object_name] nvarchar(260),
  [parameter_name] nvarchar(128),
  [project_name] NVARCHAR(128)
  )

DECLARE @project_names_table TABLE(
  [id] int identity(1, 1),
  [project_name] NVARCHAR(128)
  )

DECLARE @message nvarchar(255)

DECLARE @id int
DECLARE @max_id int
DECLARE @folder_name sysname = N'unknown'
DECLARE @environment_name nvarchar(128) = N'unknown'
DECLARE @environment_description nvarchar(128) = N'unknown'
DECLARE @project_name NVARCHAR(128)

DECLARE @object_type SMALLINT
DECLARE @object_name NVARCHAR(260)
DECLARE @value_type CHAR(1)
DECLARE @parameter_name NVARCHAR(128)
DECLARE @parameter_value SQL_VARIANT

DECLARE @reference_id BIGINT

--\
---) Environment settings
--/

IF @@SERVERNAME LIKE 'VWS63-SQL1%1'
BEGIN
    SELECT
      @folder_name = N'DEV',
      @environment_name = N'DEV',
      @environment_description = N'My development environment'
END
IF @@SERVERNAME = '<SQL INSTANCE NAME>'
BEGIN
    SELECT
      @folder_name = N'TST',
      @environment_name = N'TST',
      @environment_description = N'My test environment'
END
IF @@SERVERNAME = '<SQL INSTANCE NAME>'
BEGIN
    SELECT
      @folder_name = N'PRD',
      @environment_name = N'PRD',
      @environment_description = N'My production environment'
END

IF @folder_name = N'unknown'
BEGIN
    RAISERROR('Unknown environment', 16, 1)
    -- Make sure that the script does not continue when the environment settings could not be set due to an unknown @@SERVERNAME.
    GOTO Finally
END

INSERT INTO @project_names_table([project_name])
  SELECT
      p.name
  FROM
      [SSISDB].[internal].[folders] f
  JOIN
      [SSISDB].[internal].[environments] e
      ON e.folder_id = f.folder_id
  JOIN
      [SSISDB].[internal].[projects] p
      ON p.folder_id = f.folder_id
  WHERE
      f.name = @folder_name
      AND e.environment_name = @environment_name
      -- Optional project_name filter here:
      -- AND p.name IN ('Project_1', 'Project_2')

--\
---) Add environment reference to project(s).
--/
SELECT @id = 1, @max_id = MAX([id]) FROM @project_names_table
WHILE @id <= @max_id
BEGIN
    SELECT
      @project_name = v.[project_name]
    FROM @project_names_table v
    WHERE [id] = @id;

    IF NOT EXISTS( SELECT 1
        FROM
            [SSISDB].[internal].[folders] f
        JOIN
            [SSISDB].[internal].[environments] e
            ON e.folder_id = f.folder_id
        JOIN
            [SSISDB].[internal].[projects] p
            ON p.folder_id = f.folder_id
        JOIN
            [SSISDB].[internal].[environment_references] r
            ON r.environment_name = e.environment_name
            AND p.project_id = r.project_id
        WHERE
            f.name = @folder_name
            AND e.environment_name = @environment_name
            AND p.name = @project_name
        )
    BEGIN
        SET @message = 'An environment reference for project “' + @project_name + '” is being created.'
        RAISERROR(@message , 0, 1) WITH NOWAIT;

        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

    SET @id = @id + 1
END

--\
---) Connect environment variables to package parameters, based on the name
--/
INSERT INTO @object_parameter_value_table (
    [object_type],
    [object_name],
    [parameter_name],
    [project_name]
    )
SELECT
    prm.[object_type],
    prm.[object_name],
    prm.[parameter_name],
    prj.name
FROM
    [SSISDB].[internal].[folders] f
JOIN
    [SSISDB].[internal].[environments] e
    ON e.folder_id = f.folder_id
JOIN
    [SSISDB].[internal].[environment_variables] ev
    ON e.environment_id = ev.environment_id
JOIN
    [SSISDB].[internal].[projects] prj
    ON prj.folder_id = f.folder_id
JOIN
    @project_names_table prjsel
    ON prjsel.project_name = prj.name
JOIN
    [SSISDB].[internal].[object_parameters] prm
    ON prj.project_id = prm.project_id
    AND prm.parameter_name = ev.name
WHERE
    prm.[value_type] != 'R'
    AND prm.value_set = 0
    AND prm.[parameter_name] NOT LIKE 'CM.%'
    AND LEFT(prm.[parameter_name], 1) != '_' -- Naming convention for internally used parameters: start with _
    AND NOT ( prm.[object_type] = 30 AND LEFT(prm.[object_name], 1) = '_') -- Naming convention for internally used SSIS Packages: start with _
    AND f.name = @folder_name
    AND e.environment_name = @environment_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],
        @project_name = v.[project_name]
    FROM
        @object_parameter_value_table v
    WHERE
        [id] = @id;

    SELECT @value_type = 'R', @parameter_value = @parameter_name;
    
    SET @message = 'Parameter “' + @parameter_name + '” (of object “' + @object_name + '”) is mapped to environment variable.'
    RAISERROR(@message , 0, 1) WITH NOWAIT;

    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

RAISERROR('Adding environment reference to project(s) and mapping environment variables to package parameters has completed ..', 0, 1) WITH NOWAIT;

Finally:

GO

Also important is that I use a naming convention to decide whether or not to create a link between a package parameter and an environment variable.
For this I borrowed a naming convention from c# coding, where a variable starting with an underscore is a private variable within a class.
So what I have done is the following:

  • To exclude one package parameter from being mapped to an environment variable, start the name with _ (underscore). I know you could just use parameter names that do not exist as environment variables, but this naming convention makes your design more clear. The naming convention also makes it possible to generate the script to create environment variables after you have initially deployed your packages, you can then just skip the package parameter names that start with _.
  • To exclude all package parameters for a SSIS package, start the package name with _ (e.g. when this package is always executed from other packages and does not need environment mappings).

For the demo I use a solution with two projects, one of them with three packages, and the second one with just one package. This is done to demonstrate how connecting package- and project parameters to environment variables (step 3) works.

Project SSIS-E10-P1

  • ExampleMaster.dtsx: contains Parameter1 and Parameter2. Both parameters should be mapped to environment variables with the same name.
  • Export_Persons.dtsx: contains Parameter1, Parameter2 and _Parameter3. Only the parameters not starting with the underscore should be mapped to the environment variables with the same name.
  • _LoadDimPerson.dtsx: contains Parameter1, Parameter2 and Parameter4. Not a single parameter should be mapped to an environment variable, because the package name starts with _
  • The project contains ProjectParameter1 which should be mapped to an environment variable with the same name.

Project SSIS-E10-P2

  • AnyPackage.dtsx: contains Parameter1 and Parameter5. Only Parameter1 will be mapped, because no environment variable with name Parameter5 exists.
  • The project contains ProjectParameter1 and ProjectParameter2. Both parameters should be mapped to environment variables with the same name.
  • ssis-e10-431
    Example output windows when this script is run after having deployed the ispac files.

    ssis-e10-430
    And the result: the environment is “selectable” and the parameters show that they are mapped to an environment variable by having an underlined environment variable name in the “Value” column.

    Glueing everything together using Powershell

    Alright, what do we have so far?

    1. A sql script for setting up the folder and environment with variables.
    2. One or more .ispac files that can silently be deployed using ISDeploymentWizard.exe and its command line options.
    3. A sql script for connecting package- and project parameters to environment variables.

    So our PowerShell script simply needs to execute a sql script, then deploy the packages, and then execute another sql script. That shouldn’t be too difficult.
    This is the script that does exact those steps. If I have some time left I want to split the configuration and the actual script over different files/scripts.

    deployment_x64.ps1
    ######################################################################
    # Script:
    # ssis_deployment_script.ps1
    # Author:
    # Hans Michiels
    # Description:
    # This scripts consists of three parts:
    # 1. Executes pre_deployment.sql, which sets up an environment
    # with its variables.
    # 2. Installs one or more SSIS Deployment packages (ispac files)
    # that are in the same folder as this script.
    # 3. Executes post_deployment.sql, which configures the installed
    # packages to use the environment variables.
    #
    # (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/.
    #
    ######################################################################

    cls
    # Get the folder where this script is located
    $ScriptPath = split-path -parent $MyInvocation.MyCommand.Definition
    # Write-Output $ScriptPath;

    ######################################################################
    # CONFIGURATION
    ######################################################################
    # Configure SQL instance name and (SSIS) foldername here.
    $SQLInstance = “VWS63-SQL161”
    $FolderName = “DEV”
    # SQL Server 2016 default location:
    $ISDeploymentWizard = “C:\Program Files\Microsoft SQL Server\130\DTS\Binn\ISDeploymentWizard.exe”
    # My location:
    # $ISDeploymentWizard = “C:\MSSQL\Shared features\130\DTS\Binn\ISDeploymentWizard.exe”

    ######################################################################
    # MAIN SCRIPT
    ######################################################################
    # \
    # 1. Calls pre_deployment sql script
    # /
    $File = $ScriptPath + “\pre_deployment.sql”
    sqlcmd -S $SQLInstance -i $File

    # \
    # 2. Installs each ispac file found in the folder where the powershell script is located.
    # /
    Get-ChildItem $ScriptPath -Filter *.ispac |
    Foreach-Object {
        $ProjectName = $_.BaseName
        Write-Host “Deploying project $ProjectName ..”;
        & $ISDeploymentWizard /Silent /ModelType:Project /SourcePath:”$ScriptPath\$ProjectName.ispac” /DestinationServer:”$SQLInstance” /DestinationPath:”/SSISDB/$FolderName/$ProjectName” | Out-Null
        Write-Host “Deployment of project $ProjectName completed ..”;
    }
    # \
    # 3. Calls post_deployment sql script
    # /
    $File = $ScriptPath + “\post_deployment.sql”
    sqlcmd -S $SQLInstance -i $File

    And this is the result of the execution of the Powershell script. From now on no more manual deployments!
    ssis-e10-435Powershell output window ..

    Download all scripts and SSIS Solutions for SQL Server 2012, 2014 and 2016 here.

    Conclusion / Wrap up

    The goal of this blog post was to explain to you how to automate the deployment of SSIS packages including the configuration and environment setup.
    I have explained how:

    • to create a folder, an environment and environment variables in SSISDB using a sql script;
    • to deploy your SSIS Packages /ispac files using Powershell and the command line options of Integration Services Deployment Wizard;
    • to setup your package configuration after deployment (or: connect package- and project parameters to environment variables);
    • to execute all of the above using Powershell.

    You could also read that I use a naming convention to be able to exclude individual package parameters or all parameters of a package from being connected to environment variables.

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

    UPDATED: How to upgrade your SSIS Packages from SQL Server 2012 to 2016 (SSIS Series)

    Errata

    Important, please read!
    Hi, I noticed something weird when upgrading my packages as described below, and thought it would be fair to share this with you.

    Script Tasks might not get upgraded well

    There seems to be an issue with upgrading packages with Script Tasks in it. The packages I upgraded with just plain SSIS stuff did not have that problem.
    The following warnings are given:
    Warning 0x40016044: DFT_Export_Person: Found SQL Server Integration Services 2012 Script Component SCR_Add_RowNumber that requires migration!
    Warning 0x40016044: SCR_Foo_Bar: Found SQL Server Integration Services 2012 Script Task “ST_a1fbd3cd5d924758a2c98397289876d0” that requires migration!
    However, the wizard still says that the upgrade is successful:
    The message that the upgrade is successful

    I think these warnings should be big fat errors and I will show you why.

    Before the upgrade: the script task has a ReadOnlyVariable and a ReadWriteVariable

    Before the upgrade: the script task has code in public void Main()

    After the upgrade: the script task does not have the ReadOnlyVariable nor the ReadWriteVariable

    After the upgrade: the code in public void Main() has disappeared

    As you know I am not anti-Microsoft, in the past I have thanked them for being so cool towards developers.
    But as far as this upgrade process is concerned I wonder: what must the developer at Microsoft have thought when making this a warning instead of an error? Was it monday morning, was he or she drunk or on drugs? I don’t know, the package is now cripple and will not work. That is not a warning.

    So I “warn” you, pay attention to your script tasks, if you use them.

    Use “Upgrade All Packages” option

    After upgrading, my builds started to take a lot of time (up to 4 minutes, I suspect an on-the-fly upgrade was being done). While investigating this, I found that in the project file all packages are also listed with a PackageFormatVersion per package in it, which was still 6!
    I am not sure if this is a Visual Studio bug, if the PackageFormatVersion of the dtsx file says 8, while the dtproj file says 6 for the same package.
    Anyway, after I modified the procedure slightly, my builds were fast again (20 seconds instead of 4 minutes).
    To achieve this, I opened the SSIS 2012 project using Visual Studio 2015, then chose Upgrade All Packages from the contextmenu.
    ssis-e09-422
    After that I changed the TargetServerVersion as described below.
    Sorry for any inconveniece caused by my initial article, and I hope this update will help.

    (The original post starts below)

    Introduction

    If you are intending upgrading your SQL Server installations to 2016, you’ll probably want to do the same with your SSIS packages.
    And I can tell you, coming from SQL Server 2012 or 2014 this is fairly easy to do.
    But with my experience I want to help you doing this not the wrong way.

    Problem

    I made the mistake by using the SSIS Package Upgrade Wizard while the projectversion still was SQL Server 2012.
    Funny thing is that the packages seemed to upgrade, however I got all kinds of errors and they just did not work.

    Solution

    While the solution seemed so easy.
    For this article I prepared a SSIS project made with SQL Server 2012.
    In the picture below you can how it looks like when opened with SQL Server Data Tools 2015 on a Server with SQL Server 2016 installed.

    ssis-e09-413After the project name, between brackets you can read that this is a SSIS 2012 project.

    ssis-e09-414Also, if you open a package in Notepad (be careful not to change it) you can see that the PackageFormatVersion is 6. This means SQL Server 2012.

    The simplest way to upgrade all packages in the project is to change the version of the SSIS project! Please be sure to backup your entire project and all SSIS packages first.
    To upgrade all packages, right click on the project node (in the picture on SSIS-E09-Packages (SQL Server 2012) and select Properties.
    Then select Configuration Properties and change the TargetServerVersion to SQL Server 2016.

    ssis-e09-415Changing the TargetServerVersion ..

    Then read the message, follow the instructions and click Yes.

    ssis-e09-416Upgrade instructions and warning

    The good news is, that’s basicly it! When you open a package with Notepad again, you can see that it is upgraded to PackageFormatVersion 8. This means SQL Server 2016.

    ssis-e09-418Package which is upgraded to PackageFormatVersion 8 (SQL Server 2016).

    In my experience with more complex packages than used for this demo, they all upgraded with no problems. I used all the standard SSIS components. Script Tasks and Script Components did not have to be recompiled.

    Still a warning about the Upgrade All Packages menu item, available from the SSIS Packages node. You don’t need it if you simply change the TargetServerVersion of the project as just described. If you use Upgrade All Packages with the wrong TargetServerVersion it seems to work, but the output packages do not work.

    ssis-e09-419The Upgrade All Packages menu-item

    Conclusion / Wrap up

    When you do it the right way, upgrading SSIS Packages from 2012 to 2016 is easy to do. However I did not test this with 2014, I am quite sure it will work in the exact same way.

    As you might have noticed I have changed the frequency of my blog posts, due to several circumstances. I still try to do 2 or 3 a month.

    Last updated: 27 November, 2016.

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

    It’s patch & update time! (Updates on previous posts)

    Introduction

    As you might have noticed I have had a lack of time over the past weeks, this is why my posts are a bit irregular. Sorry for that.
    Today a listing of some updates that I did lately.

    The updates

    How to copy a database table structure (T-SQL Scripting Series)

    This script was last updated on 14 July, 2016. If you use it, make sure it is the most recent version.

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

    This article and open source code on Github was updated a few days ago. Please check it if you are interested.

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

    This article is just updated. Thanks to very useful comments some of you gave me on the initial article (thank you for that) I have added examples of using sp_getapplock and sp_releaseapplock.

    Conclusion / Wrap up

    Nobody is perfect, this why updates exist.

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

    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)

    Last update: 29 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.

    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. If you agree with a polling interval of 3 minutes, enable the job, otherwise change the interval first.
      mssql-s01e04-330Enabling the job ..

    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]

    mssql-s01e04-341The 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-343These tables have 7 jobtriggers and 9 jobtrigger conditions used for the demo ..

    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.

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

    • MSSQL_S01E04_Job0 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_Job3 will only be executed every 6 minutes, because this job runs for a little more than 3 minutes, and if the job already runs it will not be started again by the tool.
    • MSSQL_S01E04_Job1 and MSSQL_S01E04_Job2 will be executed every 3 minutes, because their triggers (C:\Temp\*.txt and [MSSQL_S01E04].[sp].[SqlTriggerExample], both return True.
    • MSSQL_S01E04_Job4 will only be executed after the triggering jobs have succeeded both.
    • MSSQL_S01E04_Job5 will only be executed after the triggering job has failed.
    • MSSQL_S01E04_Job6 will only be executed after the triggering job has either succeeded or failed.

    Let’s see if it worked out!

    mssql-s01e04-346Viewing the job history of job 0 to 3..
    In the job history I can see that:

    • MSSQL_S01E04_Job0 was never executed: it is checked on the left, but does not occur on the right.
    • MSSQL_S01E04_Job3 was only executed every 6 minutes, see the red arrows on the right side.
    • MSSQL_S01E04_Job1 and MSSQL_S01E04_Job2 were executed every 3 minutes.
    • ##SQLAgentJobStarter##, the job that caused all this, was also executed every 3 minutes, as scheduled.

    mssql-s01e04-351Viewing the job history of job 4..

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

    mssql-s01e04-352Viewing the job history of job 5..

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

    mssql-s01e04-353Viewing the job history of job 6..

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

    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

    mssql-s01e04-338Example 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.

    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.

    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: 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: 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: 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 who 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 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.