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
- Setting up folder and environment
- Deploy your SSIS Packages
- Setup package configuration (or: connect package- and project parameters to environment variables)
- 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.
--\ -------------------------------------------------------------------
---) 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
Example output of this script, when executed using Management Studio.
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.
The demo SSIS project ..
Menu-option to rebuild the SSIS solution ..
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.
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:
--\ -------------------------------------------------------------------
---) 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
Example output windows when this script is run after having deployed the ispac files.
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?
- A sql script for setting up the folder and environment with variables.
- One or more .ispac files that can silently be deployed using ISDeploymentWizard.exe and its command line options.
- 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.
# 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!
Powershell 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.
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.
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
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
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
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
Hello Dee, maybe you can have a look at this blog post: https://www.hansmichiels.com/2016/11/19/using-sensitive-parameters-ssis-series/
It might be of help.
Best regards,
Hans
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
Hi Andrew,
Thanks for letting me know, I will update the scripts when I can.
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?
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.
Thank you! It’s a great script and more time saver!
Hello pds, good to hear that the script helped you.
Best regards, Hans