Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

SSIS Package Deployment Model in SQL Server 2012 (Part 1 of 2)

MSSQLTips author Arshad Ali By:   |   Read Comments (20)   |   Related Tips: 1 | 2 | 3 | 4 | More > Integration Services Configuration Options
Problem

Deployment has always been a challenge for SSIS developers to deploy packages. SSIS developers are envious of SSRS/SSAS developers as they have an easy way to create a single unit of deployment (deployment package) that contains everything needed for the deployment. The good news is the inclusion of the SSIS Package Deployment Model in SQL Server 2012. In this tip I cover what it is and how to get started to simplify your SSIS package deployments.

Solution

SSIS enhancements in SQL Server 2012 brings a brand new deployment model for SSIS project deployment. This new deployment model is called Project Deployment Model and unlike the Legacy Deployment Model where each package was a single unit of deployment, this new model creates a deployment packet containing everything (packages and parameters) needed for deployment in a single file with an ispac extension and hence streamlines the deployment process.

Apart from handling the deployment issues, managing the configuration file for each environment for each package was also a pain in the Legacy Deployment model. The new Project Deployment Model includes Project/Package Parameters, Environments, Environment variables and Environment references.

So before I jump into an example, let me first explain some of the key terms:

Project Deployment Model
As I said before, this is the new deployment model for SSIS projects. By default all SSIS projects you create are created using this model only (you can also migrate your existing projects to this model). You can also revert back to the Legacy Deployment model if needed in Solution Explorer. To learn more about the differences between Legacy Deployment model and Project Deployment model click here.

When a project in this model is built, a deployment packet is created with an ispac extensions which includes all your packages and parameters in one packet. The deployment packet does not contain any additional files like text files, image files if you added them in the project. To learn more about Project Deployment Model click here.

Integration Service Catalog
You can create one Integration Services catalog per SQL Server instance. It stores application data (deployed projects including packages, parameters and environments) in a SQL Server database and uses SQL Server encryption to encrypt sensitive data. When you create a catalog you need to provide a password which will be used to create a database master key for encryption and therefore it's recommended that you back up this database master key after creating the catalog.

The catalog uses SQLCLR (the .NET Common Language Runtime(CLR) hosted within SQL Server), so you need to enable CLR on the SQL Server instance before creating a catalog (I have provided the step below for this). This catalog also stores multiple versions of the deployed SSIS projects and if required you can revert to any of the deployed versions. The catalog also stores details about the operations performed on the catalog like project deployment with versions, package execution, etc.... which you can monitor on the server. There is one default job provided for cleanup of operation data and can be controlled by setting catalog properties. To learn more about this click here.

Project Parameters and Package Parameters
If you are using the project deployment model, you can create project parameters or package parameters. These parameters allow you to set the properties of package components at package execution time and change the execution behavior.

The basic difference between project parameters and package parameters is the scope. A project parameter can be used in any package of the project whereas the package level parameter is specific to the package where it has been defined. The best part of these parameters is that you can mark any of them as sensitive and it will be stored in an encrypted form in the catalog.

There can be three default values for these parameters:

  • Design Default value is assigned and used in BIDS (Business Intelligence Development Studio),
  • Server Default value is assigned when project comes in the catalog and overwrites the Design Default value and
  • Execution value is assigned in reference to a specific environment variable during execution. To learn more click here.

Environments and Environment variables
An environment (development, test or production) is a container for environment variables which are used to apply different groups of values to the properties of package components by means of environment reference during runtime.

An environment reference is the mapping between an environment variable to pass a value to a property of a package component. A project can have multiple environment references, but a single instance of package execution can only use a single environment reference. This means that when you are executing your project/package you need to specify a single environment to use for that execution instance.

When defining a variable you can mark it sensitive and hence it will be stored in an encrypted form and NULL will be returned if you query it using T-SQL. To learn more click here.


Example - Development Need

What I want to do in this example, is create a project with multiple packages and build a deployment packet for the deployment. Then I will deploy the project to the Integration Services catalog and create different environments (TEST and PROD) and finally I will update the deployed project properties to use an environment reference. For execution, depending on the environment selected the data should be moved to the respective environment. The design of each individual package is very simple, it first truncates the destination table, moves data from the source (always the same for this example) to the destination (which varies depending on the environment reference chosen at execution time) and finally sends a confirmation email. For example, if I am choose the TEST environment the data should move to test database or if I choose the PROD environment the data should move to the production database.

These are the steps we will cover in this tip series:

  1. Create an Integration Services Catalog
  2. Create a SSIS project with Project Deployment Model
  3. Deploy the project to Integration Services Catalog
  4. Create Environments, Environment variables (Covered in the Part 2 tip of this series)
  5. Set up environment reference in the deployed project (Covered in the Part 2 tip of this series)
  6. Execute deployed project/package using the environment for example either for TEST or PROD (Covered in the Part 2 tip of this series)
  7. Analyze the operations performed on the Integration Services Catalog (Covered in the Part 2 tip of this series)
  8. Validate the deployed project or package (Covered in the Part 2 tip of this series)
  9. Redeploy the project to Integration Services Catalog (Covered in the Part 2 tip of this series)
  10. Analyze deployed project versions and restored to desired one (Covered in the Part 2 tip of this series)

1 - Creating Integration Services Catalog...

First of all we need to create an Integration Services catalog on the SQL Server instance (note: we can create only one Integration Service catalog on the SQL Server instance, though a catalog may contain many folders and inside each folder many projects). To create an Integration Services catalog connect to a SQL Server instance in SSMS (SQL Server Management Studio) and right click on the Integration Services node under the connected server in the Object Explorer and click on Create Catalog as shown below:

create an integration srvices catalog on the sql server

By default the catalog name appears as SSISDB and cannot be changed. You need to provide a strong password for the SSISDB Integration Services catalog you are creating which is used for the database master key. Let me tell you why you need to provide this; actually the Integration Services catalog stores application data in a SQL Server database and uses SQL Server encryption to encrypt sensitive data. For encryption, it needs to have a database master key and for that only you need to provide a password here. It's recommended to backup the database master key after the Integration Services catalog creation.

catalog name appears as ssisdb

The Integration Services catalog uses CLR based stored procedures and by default CLR is not enabled for a SQL Server instance, so you need to enable it before creating an Integration Services catalog.

enable clr for a sql server instance

To enable CLR on a SQL Server instance you can execute the script below. Once you have enabled CLR you can go ahead and create the Integration Service catalog as discussed above.

--Script #1 - Enabling CLR on the SQL Server Instance
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO 

Once an Integration Services Catalog gets created you can verify it in SSMS as shown below. As I mentioned before, an Integration Services Catalog stores application data in a SQL Server database and hence a database has been created with the same name as the Integration Services Catalog. If you browse through the database you will notice there are several tables which store the data, several views built on top of these tables and several stored procedure to access and manage this data.

verify it in ssms

Even though you can have a single Integration Services Catalog on each instance, each Integration Services Catalog can have several projects deployed to it. Let me first create a folder (AdventureWorks) for my project which I will be deploying next.

under integration services node in ssms click create folder

To create a folder, right click on the Integration Services Catalog name under Integration Services node in SSMS as shown above and click on the Create Folder menu item. This will launch the Create Folder dialog box as shown below; specify a name for the folder to create and a folder description:

create a folder for you ssis project

Each folder that you create inside an Integration Services catalog will have two subfolders inside it by default as shown below. The Projects folder is a place where you will be deploying your SSIS project and the Environments folder is a place where you will be creating multiple environments like development, test, PPE (Pre Production environment), Production, etc... I will discuss these folders more in a later tip in this series.

the projects folder is where you will deploy your ssis project


2 - Creating a SSIS project with Project Deployment Model...

The way we develop SSIS project/package in SQL Server 2012 remains the same as what we have been doing, so I am not going to talk in detail about SSIS project/package creation (to learn basics of SSIS you can refer to this tutorial) but rather directly jump into the development for the example. One noticeable difference in the SSIS project created in SQL Server 2012 is that by default the SSIS project will be created in Project Deployment mode, but if you need to you can change it by right clicking on the project in Solution Explorer and clicking on "Convert to Legacy Deployment Model".

A SSIS project in Project Deployment model creates a deployment packet (with *.ispac extension) that contains everything (all packages/parameters) needed for deployment unlike the Legacy Deployment mode in which each SSIS package is separate unit of deployment.

As per our requirement we need to have one SSIS project with a couple of packages. I will keep the design of the package very simple, it first truncates the target table, moves the data from the source to the target table and sends the confirmation email at the end.

we have one ssis project with a couple of packages

Now what I want is to move data to a table in a different database depending on the environment. For example, if the package is executed with the TEST environment reference then data should be moved to AdventuresWorks2008R2Test database and if the package is executed with the PROD environment reference then data should be moved to AdventuresWorks2008R2Prod database. So let me create one Project Parameter to hold the name of the database which will be passed from an environment variable and used in the TargetConnection's connection string to connect to the target database. To create a project parameter, right click on the project in the Solution Explorer and click on the Project Parameters menu item as shown below:

ssis project parameters

On the Project Parameters window, click on the New Parameter icon in the upper left and specify the name of the project parameter. In this case I want the parameter to hold the database name hence I have specified DatabaseName name for the parameter and the default value specified as "AdventureWorks2008R2Test". This means if the package is executed in the designer, data will move to AdventureWorks2008R2Test database.

ssis project parameters window

The package also contains two connection managers to connect to the databases. SourceConnection connection manager connects to the source to pull data from and will remain constant whereas TargetConnection connection manager gets the database name from a project parameter and varies from environment to environment. Apart from these two connection managers to connect to the databases, I have one SMTP connection manager which is used to send confirmation email as shown in the package design.

connection managers

To make a connection manager to dynamically use the database name, you need to configure the Expression property of the TargetConnection connection manager and specify the value of InitialCatalog property to come from an expression (in this case project parameter which we created above) as shown below:

configure the expression property

As we have specified a default value for the project parameter, if you execute the package it will execute successfully and will move the data to AdventureWorks2008R2Test database (the default value specified for the project parameter).

specified default value for the ssis project parameter


3 - Deploying the project to Integration Services Catalog...

Now that we are done with project and package development, we need to deploy the project to the Integration Services catalog we created above. To deploy the project, right click on the project in the Solution Explorer and click on the Deploy menu as shown below:

deploy the ssis project to the integration services catalog

Clicking on the Deploy menu item will launch the Integration Services Deployment wizard and the first screen of the wizard is a welcome screen as shown below. You can choose not to show this screen next time by clicking on the checkbox on bottom. Click on Next button to move ahead:

deploys an integration services project to an integration services catalog on an instance of sql server

The second screen of the wizard is the place where you actually specify the location to deploy from; you can either choose the deployment packet (*.ispac) file or choose an already deployed package from the Integration Services catalog as the source for the deployment. Since I want to deploy from the SSIS project, I have specified the *.ispac deployment packet name:

deploy from the ssis project

The third screen of the wizard lets you specify the destination where you want to deploy the project. You need to choose the name of the server where you have created the Integration Services catalog and the folder in the catalog where you want to deploy the project. I will use the folder I created above to deploy the project as shown below:

choose the name of the server where you have created the integration services catalog

As I mentioned before, a parameter can have a server default value, the next screen is the place where you define the server default values for all the parameters of the project. Here you can either use the same design default value, specify a new value or choose the value to come from a variable as shown below:

configure parameters

The next screen of the wizard is a review screen where you review your selections, you can click on the Deploy button to start the deployment of the project as shown below:

review selections and deploy ssis project

The final screen of the wizard shows the deployment progress and deployment status as you can see below. If there are any failures they will be marked red and you can click on the Result column to see the reason for the failure.

results of deployment

Since our deployed was successful as shown above, we can connect to the Integration Services Catalog using SSMS and verify the deployment as shown below:

connect using ssms and verify deployment


Summary

In this article I talked about the basics of the new SSIS deployment model called Project Deployment Model, how it differs from the Legacy Deployment Model, how to create an Integration Service Catalog, how to create a project with the Project Deployment Model and finally how to deploy a SSIS project to the Integration Services Catalog.

Stay tuned for my next tip in this series, in which I will discuss creating environments, environment variables, setting up an environment reference in the deployed project, executing deployed project/package using the environment for example either TEST or PROD, analyzing the operations performed on the Integration Services Catalog, validating the deployed project or package, redeploying the project to Integration Services Catalog, analyzing the deployed project versions and restoring to a previous version.

Notes:

  • I have shown features and power of Integration Services catalog using the UI (User Interface), but you can also manage and control it using T-SQL commands.
  • The sample code, example and UI is based on SQL Server 2012 CTP 1, it might change in further CTPs or in the final/RTM release.
Next Steps


Last Update: 8/11/2011


About the author
MSSQLTips author Arshad Ali
Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Thursday, August 11, 2011 - 11:43:29 AM - Simon Read The Tip

This is an excellent article - thank you.

I'm looking forward to these changes to SSIS deployment in Denali!


Thursday, July 26, 2012 - 9:11:53 AM - ravikumar Read The Tip

Hi Arshad,

Really this is an excellent article, i follow the same steps and i can able to cretae multiple environments in SSDT level only.

but the problem is while deploying the project , in the Integration services deployment wizard tool box i can't find the "configure parameters" tab option. But in my project i created two project level parameters and many of the package level parameters. the only problem is i can't see the Configure parameters option in wizard level.

can u explain me on this post in detail of my proiblem .

 

thanks,

Ravikumar 


Wednesday, September 26, 2012 - 1:34:33 AM - Srinivas V M Read The Tip

Thanks a Ton Arshad,

 

Deployment in Denali well expalined and is of real use , Appreaciate it once again,

 

keep up the good work,

 

Regards

Srinivas V M


Friday, October 26, 2012 - 4:47:25 PM - Sardys Read The Tip

Very good informative article. thank you


Sunday, January 06, 2013 - 10:05:07 PM - Roohi Sachdeva Read The Tip

I have read your post and it was quite good but when I try to deploy my package it throws me below error and does not let me deploy the package sucessfully.

===================================

A .NET Framework error occurred during execution of user-defined routine or aggregate "deploy_project_internal":
System.ComponentModel.Win32Exception: A required privilege is not held by the client
System.ComponentModel.Win32Exception:
   at Microsoft.SqlServer.IntegrationServices.Server.ISServerProcess.StartProcess(Boolean bSuspendThread)
   at Microsoft.SqlServer.IntegrationServices.Server.ServerApi.DeployProjectInternal(SqlInt64 deployId, SqlInt64 versionId, SqlInt64 projectId, SqlString projectName)
. (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.2218&EvtSrc=MSSQLServer&EvtID=6522&LinkId=20476

------------------------------
Server Name: ALTIS-ROOHI1\MSSQLSERVER2012
Error Number: 6522
Severity: 16
State: 1
Procedure: deploy_project_internal

It has consumed my day almost and struggling with the same error.


Monday, February 04, 2013 - 10:23:11 AM - Anil Read The Tip

I am getting the same error and not able to resolve...

 

I have read your post and it was quite good but when I try to deploy my package it throws me below error and does not let me deploy the package sucessfully.

===================================

A .NET Framework error occurred during execution of user-defined routine or aggregate "deploy_project_internal":
System.ComponentModel.Win32Exception: A required privilege is not held by the client
System.ComponentModel.Win32Exception:
at Microsoft.SqlServer.IntegrationServices.Server.ISServerProcess.StartProcess(Boolean bSuspendThread)
at Microsoft.SqlServer.IntegrationServices.Server.ServerApi.DeployProjectInternal(SqlInt64 deployId, SqlInt64 versionId, SqlInt64 projectId, SqlString projectName)
. (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.2218&EvtSrc=MSSQLServer&EvtID=6522&LinkId=20476

------------------------------
Server Name: ALTIS-ROOHI1\MSSQLSERVER2012
Error Number: 6522
Severity: 16
State: 1
Procedure: deploy_project_internal

It has consumed my day almost and struggling with the same error.


Monday, February 18, 2013 - 3:09:41 PM - Mario Read The Tip

Roohi, Anil... did you get this resolved? I am getting the same error. any help would be appreciated.


Wednesday, March 13, 2013 - 12:21:39 PM - Dileep Gonti Read The Tip

Great article abut deploying sql 2012 SSIS.

deployed SSIS package with Config file , but packge not reading config file( reading package default values), do we need to change any  specific location?

Thank you,

--DG.


Wednesday, May 01, 2013 - 12:11:47 PM - Neelam Gupta Read The Tip

Awesome!!!


Saturday, May 25, 2013 - 9:22:13 AM - kulmam Read The Tip

How do you handle if you have different password on test and prod environment and you want to change it dynamically? What's the secure way?


Tuesday, August 06, 2013 - 8:14:02 PM - nithin Read The Tip

Great article. But even in 2008 R2 there was an option to deply a whole set of packages if not the project as such. You could create a Manifest file to include multiple packages.


Wednesday, August 07, 2013 - 10:48:28 AM - Arshad Ali Read The Tip

Hi Nithin,

Yes true, but this new features contains many more features than was available as out of box features in previous versions, for example you can rollback to the previous version of package, contains history of deployment, operation logging etc.

 


Thursday, December 12, 2013 - 12:49:24 AM - satish Read The Tip

Very nice explanation.

 

Thank You

satish


Tuesday, December 17, 2013 - 12:06:55 AM - Peter Read The Tip

It doesn't make sense to specify a Server and deploy. 

You should create an environment agnostic package you can deploy to server and the configure its enviornment variables there.


Friday, January 10, 2014 - 5:15:25 PM - Arthur Hill Read The Tip

In this article you state: 'You can create one Integration Services catalog per SQL Server instance'

1. Your image of the explore screen displays 'Integration Service' as the top level of the catalog.

2. My Developer Version of SQL Server 2012 displays 'Integration Service Catalogs' as the top level of the catalog.

I have spent the last few hours attempting to figure out why a label such as 'Integration Service CATALOGS' would be diplayed if there is only one catalog.  Was there once upon a time, plans to have multiple catalogs? 

Do I have some non-standard version of SQL Server 2012?

I really would like to know.

The reason is this.  One of the question in a test bank for exam 70-463 states that this is the proper command structure to execute a DTExec command

'DTExec /ISSERVER "\SSISDB\ContosoIntegration\MoveData.dtsx" /SERVER ".'

If there is only one possible catalog on a server instance why in the world would you have to enter the catalog identifier?


Thursday, January 23, 2014 - 1:12:36 PM - Lucy Z Read The Tip

I have a package with many package parameters.  Now I need to change the scope,  move thses parameters to Project level from package level.  Is there an easy way to copy instead of key in (in SQL2012 SSDT environment)


Friday, January 31, 2014 - 5:07:58 AM - Johanna Read The Tip

Thanks Arshad! Way better than the course book :)


Sunday, February 23, 2014 - 3:43:21 AM - srinu Read The Tip

 

 

Hi i heard we can deply entire project related package.bue here i need deploy only one package in that sistuvation how we deploy single ssis package in ssis 2012 version.any body  tell me how we achive this sistuvation in ssis 2012 version


Thursday, February 27, 2014 - 3:14:32 PM - Bill Read The Tip

How can I call a Child Package in a Execute Package Task that is stored in the SSISDB Catalog in a different Project?


Wednesday, March 12, 2014 - 6:42:08 PM - eric81 Read The Tip

 

For A .NET Framework error occurred during execution of user-defined routine or aggregate "deploy_project_internal":
System.ComponentModel.Win32Exception: A required privilege is not held by the client

 

refer to this article worked for me..

http://blog.oraylis.de/2013/09/fixing-sql-2012-ssis-deployment-error-6522-a-required-privilege-is-not-held-by-the-client/



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.