Tag Archives: Deployment

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.

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




    Last update: 20 May, 2017: made newer scripts available for download after giving a presentation for PASS Slowakia.

    Enhancements in the newer scripts (may 2017)

    • can handle multiple folders/environments on the same SQL Server Instance.
    • can install ispac files that are located in subfolders.
    • can install individual SSIS packages (dtsx files) from Powershell (SQL Server 2016 only feature)

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


    Using the project deployment model with environments (SSIS Series)

    Introduction

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

    Folders and environments

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

    Let’s try it

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

    My test setup is very basic:

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

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

    The initial SSIS Package

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

    SSIS-S01E08-281The package parameter

    SSIS-S01E08-282The project parameter

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

    Deploying the SSIS Package

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

    SSIS-S01E08-286First step of the wizard

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

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

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

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

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

    SSIS-S01E08-293Review and deploy.

    SSIS-S01E08-295Deployment succeeded.

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

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

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

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

    SSIS-S01E08-TST-environment_setup.sql

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

    USE [SSISDB]
    GO

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

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

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

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

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

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

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

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

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

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

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

        SET @id = @id + 1
    END

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

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

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

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

        SET @id = @id + 1
    END

    GO

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

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

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

    We run the script for the TST environment.

    SSIS-S01E08-300The script was executed successfully.

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

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

    SSIS-S01E08-303View package configuration.

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

    SSIS-S01E08-305When I choose Execute ..

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

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

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

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

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

    SSIS-S01E08-312And yes, it works!

    Extending the package with a FruitDescription

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

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

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

    SSIS-S01E08-316Then Rebuild

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

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

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

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

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

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

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

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

    SSIS-S01E08-325Test result

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

    Download the scripts here.

    Conclusion / Wrap up

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

    Why did you use those weird fruit examples?

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

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