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.


    Hans Michiels

    Hans is an Independent Business Intelligence and Datawarehouse Consultant & Microsoft SQL Server Consultant, working in the Netherlands. He has been working in the software industry since 1996, with SQL Server since the year 2001, and since 2008 he has a primary focus on datawarehouse- and business intelligence projects using Microsoft technology, using a Datavault and Kimball architecture. He has a special interest in Datawarehouse Automation and Metadata driven solutions. * Certified in Data Vault Modeling: Certified Data Vault 2.0 Practitioner, CDVDM (aka Data Vault 1.0) * Certified in MS SQL Server: * MCSA (Microsoft Certified Solutions Associate) SQL Server 2012 - MCITP Business Intelligence Developer 2005/2008 - MCITP Database Developer 2005/2008 - MCITP Database Administrator 2005/2008

    More Posts

    13 thoughts on “How to automate your SSIS package deployment and configuration (SSIS Series)”

    1. Hi there.

      I’m using the IsDeploymentWizard command line tool to automate deployment of packages..which is working very well.
      The only issue I’m running into is when I’m deploying to servers over a slow network…and I get a Timeout Error.
      I can’t find any way to extent the Timeout value (through a param or global setting)

      Any idea what I can do to overcome this?

      M.

      1. Hello Marius,
        The IsDeploymentWizard is the exact same thing I am using in my scripts. If you run this over the network, you are actually using the IsDeploymentWizard of a different SQL Server Instance .. this could be a different version or service pack .. I am just thinking loud (not tried it), but maybe you could create a SQL Agent Job without a schedule, that kicks of the IsDeploymentWizard.exe on the target server. You can then start the job remotely (from a different server) using SQL Server Management Studio by connecting to the remote SQL Instance. But the difference is, then IsDeploymentWizard.exe will run locally on that server, and you might get rid of the TimeOut Error. If you try this I would appreciate it, if you keep me updated on the fact if this solves your problem. I hope it will.
        Best regards, Hans

    2. Hi Michiel,

      Great post ! I want to split my SSIS project and want to copy the Environment variables. There is a move option (and not copy) and the Script option gives an empty window. Great! So I will study your blogpost and grab some of your stuff 😉 I hope you don’t mind!

      Best regards,

      Hennie

      1. Hi Hennie,
        Of course not, go ahead. I have just added a newer version of my scripts, the changes are listed on top of the article. I hope it will be useful.
        Best regards,
        Hans

    3. Hi Hans,

      I’ve project deployment model, Project parameters. I would like to change the Password to sensitive and try to execute the packages. I encountered with the error ”Error: The expression will not be evaluated because it contains sensitive parameter variable “$Project::EV_Oracle_GW_Password”. Verify that the expression is used properly and that it protects the sensitive information.”. Could you please help me on this.

      Thanks
      Dee K

    4. Hi Hans,

      Another great article, I have found a bug in your post-deployment sql code, when joining from project to object_parameters, you need this extra join predicate.
      AND prj.object_version_lsn = prm.project_version_lsn

    5. Hi Hans,
      It’s a great articles and really very time saving .
      How can i use this if i have already existing SSIS package and variable/ parameter setup but needs to modifying so as a example first check the it’s exist then delete and run it or how i can change it. I think our script is to create new ones like in any other environments but for existing ones how i can use it?

      1. Hi pds,
        If you have downloaded the scripts, check pre_deployment.sql, it contains on line 261 (of the v2016 script) this comment: “ON a server where the VARIABLES already exist, you can use this query to generate the ‘VALUES’ part of the INSERT command used above.” and on line 292 “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.” If you have added your existing variables/parameters using those scripts, you can then set [create_only] to @false if you always want to replace the value of the environment variable by the value in the script.

    Leave a Reply

    Your email address will not be published. Required fields are marked *