Moving Forward Blog

SQL Server 2012 Project Deployment Model

  • 25 Feb, 2015
  • C Nelson
  • 0 Comments

By Andy Bolk, Sr. Business Intelligence Consultant, TW

With SQL Server 2012 comes the introduction of the Project Deployment Model.  This uses different methods to deploy and configure SSIS packages than what we are used to in previous versions, now called the Package Deployment Model, but it is much more straightforward.  With the Project Deployment Model, gone are package configurations and file system deployments and they are replaced by parameters and many features in the SSIS Catalog.  However, you can still develop SSIS packages using the Package Deployment Model.

Let’s define what some of the new features are.

Project Deployment Model

With the Project Deployment Model, you deploy an entire Visual Studio project instead of individual packages.  If necessary, you can switch back and forth between deployment models by right clicking on the project in the Solution Explorer.

Parameters

Parameters allow you to configure variables or any package property at run time.  They can be created at the package level or if they apply to multiple packages within your project they can also be created at the project level.

SSIS Catalog

The SSIS Catalog is a database which is created to store all of the objects for the SSIS projects you deploy.  This database will need to be manually created after SQL Server is installed.

To create the catalog right click on the Integration Services Catalogs node, underneath the desired server in SQL Server Management Studio.  You will get the following window once you do so.

Blog-Bolk2

If CLR stored procedures are not already enabled on your server, you will need to enable the check box on this screen.  The database is also encrypted, so provide a password and click OK to create the database.  The database will be named SSISDB, which cannot be changed.

Once the database is created you can navigate the project hierarchy and objects underneath the Integration Services Catalogs node.

Here is a brief description of the different objects you will find in the catalog.

Folders – Used to organize projects, similar to how an operating system folder works.

Projects – The actual SSIS project which is deployed from Visual Studio.

Environments – You can specify the different environments your project will run in (development, test, production, etc.)

Environment Variables – These are located within each environment and are used to map to a package or project parameter.  You can specify different values for a variable depending upon the environment (server name, database name, file path, etc.)

Environment Reference – Once you have your environments created you will need to create a reference between your project and the environments you want to use.

 

Project and Package Description

Before we jump into how to do the deployment, let’s get an understanding of what my Visual Studio project looks like.   The project I am deploying only has one SSIS package and only has a simple Data Flow task to export data from a database to a text file.  Once deployed, I want the package to run in a development and a test environment.  Depending on the environment chosen at run time, the source database will have a different connection and the extract file will be written with a different file name.

Now that we have a general understanding of some of the new features that are involved in a SSIS deployment, we can look at doing the deployment itself.  As part of the SSIS Catalog there are system stored procedures to help us do the deployment.

 

Deployment Script

I find it beneficial to create a template script that can be used as a starting point for a new deployments.  The goal then is to use the template script and only need to change the parameter values and/or make minor modifications.  I will reference sections of my template script below, which can then be combined to create the entire script.

At the beginning of the script I declare and set the values for parameters that are used throughout the script.

DECLARE @ProjectName nvarchar(max),

@PackageName nvarchar(max),

@FileOutputPath sql_variant,

@FolderName nvarchar(128),

@ProjectBinary varbinary(max)

 

SET @ProjectName = N’BlogProject’

SET @PackageName = N’CreateOutputFile.dtsx’

SET @FileOutputPath = N’C:\Blog\OutputFiles\’

SET @FolderName = N’Blog’

SET @ProjectBinary = (SELECT * FROM OPENROWSET(BULK ‘C:\Blog\bin\Development\SQL Saturday.ispac’, SINGLE_BLOB) as BinaryData)

 

@ProjectName – The name of the project to be deployed.

@PackageName – The name of the SSIS package.  If your project has multiple packages there may be a need to have multiple PackageName parameters.

@FileOutputPath – The directory where you want the export file to be created.  This is only necessary for my project, since I am creating an export file.

@FolderName – The name of the folder you want the project to be created in.

@ProjectBinary – The binary contents of the .ispac file.  This is needed to run the stored procedure which deploys the project.

 

First we need to create a folder where the project can be deployed to.  The catalog.create_folder stored procedure requires the folder name as an input parameter.  If the folder already exists the procedure will fail.

EXEC SSISDB.catalog.create_folder

@folder_name=@FolderName

 

Next we need to deploy the Visual Studio project to the SSIS Catalog. When you build the project in Visual Studio it will create an .ispac file, which contains the files necessary for the deployment.  The catalog.deploy_project stored procedure requires the folder name, project name and the project binary.  If the project has already been deployed the stored procedure will update the existing version.

 

EXEC SSISDB.catalog.deploy_project

@folder_name=@FolderName,

@project_name=@ProjectName,

@project_stream=@ProjectBinary

 

Now that the project is deployed, the environments which it will run against need to be created.  The catalog.create_environment stored procedure requires the folder name and the name of the environment.  The example below shows that I am creating an environment for both development and test.

 

EXEC SSISDB.catalog.create_environment

@folder_name=@FolderName,

@environment_name=N’Development’

 

EXEC SSISDB.catalog.create_environment

@folder_name=@FolderName,

@environment_name=N’Test’

 

Once the environments are created we can now add the environment variables.  This will be used to configure the parameters at run time.  The create_environment_variable stored procedure requires the folder name, environment name, variable name, data type, sensitive (whether the value is sensitive or not, i.e. password), and the variable value.  In this example, the same environment variables will need to be created for both the development and test environment, but with different values.

 

Development Environment

 

EXEC SSISDB.catalog.create_environment_variable

@folder_name=@FolderName,

@environment_name=N’Development’,

@variable_name=N’FilePath’,

@data_type=N’String’,

@sensitive=0,

@value=@FileOutputPath

 

The name of the output file will be different depending on which environment we run the package against.

 

EXEC SSISDB.catalog.create_environment_variable

@folder_name=@FolderName,

@environment_name=N’Development’,

@variable_name=N’FileName’,

@data_type=N’String’,

@sensitive=0,

@value=N’Person_Development.txt’

 

The name of the database server will be different depending on which environment we run the package against.

 

EXEC SSISDB.catalog.create_environment_variable

@folder_name=@FolderName,

@environment_name=N’Development’,

@variable_name=N’DatabaseServerName’,

@data_type=N’String’,

@sensitive=0,

@value=N’DevelopmentServer’

 

EXEC SSISDB.catalog.create_environment_variable

@folder_name=@FolderName,

@environment_name=N’Development’,

@variable_name=N’DatabaseName’,

@data_type=N’String’,

@sensitive=0,

@value=N’AdventureWorks2014′

 

Test Environment

 

EXEC SSISDB.catalog.create_environment_variable

@folder_name=@FolderName,

@environment_name=N’Test’,

@variable_name=N’FilePath’,

@data_type=N’String’,

@sensitive=0,

@value=@FileOutputPath

 

The name of the output file will be different depending on which environment we run the package against.

 

EXEC SSISDB.catalog.create_environment_variable

@folder_name=@FolderName,

@environment_name=N’Test’,

@variable_name=N’FileName’,

@data_type=N’String’,

@sensitive=0,

@value=N’Person_Test.txt’

 

The name of the database server will be different depending on which environment we run the package against.

 

EXEC SSISDB.catalog.create_environment_variable

@folder_name=@FolderName,

@environment_name=N’Test’,

@variable_name=N’DatabaseServerName’,

@data_type=N’String’,

@sensitive=0,

@value=N’TestServer’

 

EXEC SSISDB.catalog.create_environment_variable

@folder_name=@FolderName,

@environment_name=N’Test’,

@variable_name=N’DatabaseName’,

@data_type=N’String’,

@sensitive=0,

@value=N’AdventureWorks2014′

 

With the environments all set to go, we now need to create environment references so our project can use the environments that were created.  The create_environment_reference stored procedure requires the environment name, project name, folder name and the reference type.  The reference type accepts values of R and A.  Pass in R if the environment you are referencing is in the same folder as the project.  Pass in A when you need to reference an environment in a different folder.

 

EXEC SSISDB.catalog.create_environment_reference

@environment_name=N’Development’,

@project_name=@ProjectName,

@folder_name=@FolderName,

@reference_type=R   — R = reference A = absolute

 

EXEC SSISDB.catalog.create_environment_reference

@environment_name=N’Test’,

@project_name=@ProjectName,

@folder_name=@FolderName,

@reference_type=R

 

After the references have been made, now we need to map the environment variables to the project and package parameters.  The catalog.set_object_parameter_value stored procedure requires the parameter name, object name, folder name, project name, value type, object type and the parameter value.  The stored procedure parameters are explained below:

 

Object_type – 20 if the parameter to configure is a project parameter and 30 if it is at the package level.

Parameter_name – The name of the parameter within the project.

Object_name – The name of the project if the parameter is at the project level or the name of the package if the parameter is at the package level.

Folder_name – The name of the folder where the project/package is located.

Project_name – The name of the project.

Value_type – Accepts R and V.  Use R if referencing an environment variable and use V if using a hard coded value.

Parameter_value – The value to pass in to the parameter.  This will be the name of the environment variable or the hard coded value.

 

–Configure project parameter

EXEC SSISDB.catalog.set_object_parameter_value

@object_type=20, –project level parameter

@parameter_name=N’FilePath’,

@object_name=@ProjectName,

@folder_name=@FolderName,

@project_name=@ProjectName,

@value_type=R,   –R = variable reference, V = hard coded value

@parameter_value=N’FilePath’

 

–Configure package parameter

EXEC SSISDB.catalog.set_object_parameter_value

@object_type=30,

@parameter_name=N’FileName’,

@object_name=@PackageName,

@folder_name=@FolderName,

@project_name=@ProjectName,

@value_type=R,

@parameter_value=N’FileName’

 

When configuring a connection manager, the parameter name works a little different than if we are just configuring a parameter.  First we reference “CM” which says we are configuring a connection manager.  Next we specify the name of the connection manager, followed by the property, as shown in the examples below.

 

–Configure connection manager properties

 

EXEC SSISDB.catalog.set_object_parameter_value

@object_type=20,

@parameter_name=N’CM.AdventureWorks.ServerName’,

@object_name=@ProjectName,

@folder_name=@FolderName,

@project_name=@ProjectName,

@value_type=R,

@parameter_value=N’DatabaseServerName’

 

EXEC SSISDB.catalog.set_object_parameter_value

@object_type=20,

@parameter_name=N’CM.AdventureWorks.InitialCatalog’,

@object_name=@ProjectName,

@folder_name=@FolderName,

@project_name=@ProjectName,

@value_type=R,

@parameter_value=N’DatabaseName’

 

Our project is now deployed and configured.  The package can now be executed in both the development and test environments.

Share This Story

Categories

Comments