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.
The Execute SQL Task
The package parameter
The project parameter
The OLEDB connection manager configured with an expression
Then 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).
Locating the .ispac file, that was created when the solution was rebuilt.
First step of the wizard
The default deployment model is Project Deployment, the one you need. The other option is Package Deployment.
Select a server name and a path [1]
Select a server name and a path [2]: create a new folder
Select a server name and a path [3]: name the folder after your environment
Select a server name and a path [4]: then click Next
Review and deploy.
Deployment succeeded.
In the Integration Services Catalog you can find your project and package.
You could configure it now, but there are no SSIS environments yet (note: these are a set of environment variable values).
Don’t configure just yet. We will do this later.
Here you could create an environment .. don’t do it now !
--\
---) 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.
The script was executed successfully.
After the script has run, a TST folder with an TST environment inside exists. You can view or edit its properties.
When the Variables pane is selected on the left, the environment variables, created by the script, are shown.
View package configuration.
The package parameter FruitName is now connected to environment variable FruitName.
When I choose Execute ..
… all I have to do is check Environment, it will automaticly show .\TST
After execution of the package, a row exists in the table (and one from a previous test)
We can also execute the package with a SQL Agent Job ..
Execute the package with a SQL Agent Job: all I have to do is check Environment, it will automaticly show .\TST ..
.. and all values are connected properly ..
And 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.
Added FruitDescription
Modify the Execute SQL Task to insert also the value for @FruitDescription [1]
Modify the Execute SQL Task to insert also the value for @FruitDescription [2]
Then Rebuild
Then run deployment wizard again, but now create a DEV folder
Script modifications [1]: use a DEV folder and a DEV environment name.
Add 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.
The package was deployed using the deployment wizard.
SSIS Environment was created using the script
Environment variables where added using the script
Environment variables were connected to package parameters using the script
Package execution test: note the new parameter FruitDescription
Test result
Final 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.
Hans, you and your readers may also find this SSISDB architectural poster useful, http://bit.ly/2cbxxEx. Let me know what you think.
George, yes indeed, this poster is useful ,thank you for bringing this to my attention.
Thank you for your post! This might come in handy one day, although I hope to have the luxery never to use this type of construction. I’d rather have all the environments as much the same as possible including table names, given the choice.
Hi Veronique, I understand your concern. However, when you do development on a system, it is inavoidable that you have different environments with different versions of your system/software. For instance you develop a change in the development environment (e.g. new tables and SSIS packages) and when it is ready to test, you upgrade your test environment with the new tables and packages.
This post is merely about how to cope with different versions of the same SSIS Packages when two or more system environments are installed on the same server.
This helped me get my POC to a pilot. Thanks for the guidance!
Hello Dan, thank you for taking the time to let me know. I like to help people with my blog articles, and a confirmation that this has worked out is always welcome 🙂
Typically I’ve found that most sites have separate instances of SQL Server for DEV, TEST, Prod etc. Therefore, when I deploy, I deploy one environment to each server and reset the connection string values in the new environment by right-clicking the environment name and selecting the Variables page. This way I don’t have to manage multiple copies of the same catalog and I don’t need multiple copies of my database either, i.e. no MyDatabaseDev and MyDatabaseTST. It’s simply MyDatabase and greatly simplifies deployment.
I deploy my SSIS projects using .ispac files and Powershell and I use database projects in Visual Studio to create .dacpac files and deploy changes to the databases with Powershell as well. I version both types of projects in TFS (or Git).
Obviously, if you only have one server with Integration Services installed that has to service DEV, TEST, and Prod environments then your method will work much better.
Hi Bob, thanks for your comment.
I agree that database names on different instances don’t need an environment name in the database name, and understand that it has the advantages that you mention.
Reasons I use it most of the time is:
– I have worked for customers where the production environment was on a separate server but development and one or more testenvironments were all on one Server and SQL Server Instance.
– In small teams, when there is not a strict division of responsibilities a developer might be responsible for deployment as well. In those cases different database names (amongst other precautions) can help to prevent making mistakes, say think you are connected to the test environment but it is actually production. If the database names are equal, there is a slightly higher chance that you notice this too late on a late Friday afternoon or early Monday morning.
– It “discourages” to hardcode cross database dependencies using database name prefixes before schema and objectname. This is a bad practice in my opinion, in one of my older blog posts I have explained the usage of synonyms as an alternative for this, so cross database dependencies are isolated in the synonyms, which can be dropped and recreated to point to a different database with a script.
Thanks again for sharing your way of working and insights, I appreciate it.
At our work place we have a situation that – we have ssis package for lot of reports and the packages are executed using scheduled sql server agent job. Can we deploy sql server agent job also as part of ssis project deployment ?
Hello B, yes I suppose it would be possible, but you will have to write some custom scripts.
For a start you can use these scripts to deploy the SSIS packages:
https://www.hansmichiels.com/2016/11/04/how-to-automate-your-ssis-package-deployment-and-configuration-ssis-series/
Then you could create a generic SQL Agent job script, as described here:
https://www.hansmichiels.com/2016/12/24/making-your-sql-agent-job-scripts-generic-for-all-servers-sql-server-series/
Finally you will have to glue the two together.
It is some work, but it can be done.
I hope my information is useful for you.
Best regards, Hans