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














































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

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

Deployment has always been a challenge for SSIS developers as they needed to write scripts for each individual package deployment. Though not impossible, but it is quite tedious and error prone especially if there are a lot of SSIS packages. SSIS developers have been 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 that this is changing with the inclusion of the SSIS Package Deployment Model in SQL Server 2012. In this tip we cover the remaining items on how to use the new Package Deployment Model.

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 that has an ispac extension and hence streamlines the deployment process.

In my last tip in this series I talked about basics of the new SSIS deployment model called Project Deployment Model, described some of the basic elements of this new deployment model, how it differs from the Legacy Deployment Model, how to create Integration Service Catalog, how to create a project with Project Deployment Model and finally how to deploy SSIS project to the Integration Services Catalog.

In this tip I am going to talk about Creating Environments, Environment variables, Setting up an environment reference in the deployed project, Executing deployed project/package using the environment for example either for 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 deployed project versions and restoring back to desired point if needed.

So what we all I am covering this tip series:

  1. Creating Integration Services Catalog (Covered in Part 1)
  2. Creating a SSIS project with Project Deployment Model (Covered in Part 1)
  3. Deploying the project to Integration Services Catalog (Covered in Part 1)
  4. Creating Environments, Environment variables
  5. Setting up environment reference in the deployed project
  6. Executing deployed project/package using the environment for example either for TEST or PROD
  7. Analyzing the operations performed on the Integration Services Catalog
  8. Validating the deployed project or package
  9. Redeploying the project to Integration Services Catalog
  10. Analyzing deployed project versions and restoring to desired one

4 - Creating Environments and Environment Variables...

As demonstrated in my last tip, you can create as many folders as you want inside the Integration Services Catalog. Each folder that you create, automatically creates two subfolders. The Projects subfolder will contain all the projects deployed to that folder whereas the Environments subfolder will contain all the environments created inside your created folder. We will create two environments (TEST and PROD) for our deployed project to be executed.

To create an environment, right click on the Environments subfolder and click on Create Environment menu item as shown below:

ssis package deployment model

As you can notice in the below image, I have created two environments (TEST and PROD) as per our need:

creating enviroments and enviroment variables

Now right click on each individual environment, click on Properties and go to Variables on the left of the Properties page as shown below. Here we need to create a environmental variable to hold the database name to be used in that particular environment. As you can notice, for PROD environment I have created a variable called DatabaseName of type string and specified AdventureWorks2008R2Prod as its value. Likewise for TEST environment I have created a variable called DatabaseName of type string and specified AdventureWorks2008R2Test as its value.

ssis enviroment properties


5 - Setting up environment reference in the deployed project...

Now that we have deployed the project to the Integration Services Catalog and created environments, we need to set the reference or establish an association which tells project/packages what environments can be used. To do that, right click on the deployed project and click on Properties, on the Project Properties page click the References on the left and on the right side page click on Add Reference to add a reference for TEST and PROD environments as shown below:

enviroment reference in deployed ssis project

Add references for both environments (TEST and PROD) as shown above. Once done click on Parameters on the Project Properties page and specify the value of the project parameter (DatabaseName) which will come from an environment variable called DatabaseName as shown below:

ssis project properties


6 - Executing deployed project/package using the environment for example either for TEST or PROD...

Now that we are done with project deployment, creation of environment/environments variables, setting up reference for these environments, its time for project/package execution. You can execute all the packages inside a project at once or you can select an individual package to execute. Right click on the project/package and click on the Run menu item as shown below to launch the Run Package window:

executing deployed ssis project/package

On the Run Package window, specify the environment you want the package to be executed for. You can select the environment from the Environment references combo box. If you want your package to be executed in 32 bit runtime on the 64 bit server you can check the box on the bottom right after the Parameters grid:

select the enviroment  you want the ssis package to be executed for

Once you hit the OK button on the above screen, it will start executing the package and will display the execution status (like who initiated it, what time it was initiated, the current status, etc) in the Package Running Information window as shown below:

sql server 2011 package running information

If you want to verify what parameter values were used during execution, you can click on the Parameters tab on the Package Running Information window as shown below. I selected PROD environment when executing the package and hence the value from the PROD environment variable has been used to feed the value for the parameter:

parameter values used during execution


7 - Analyzing the operations performed on the Integration Services Catalog...

Integration Services Catalog tracks all the activity or operations that you perform on it. It also tracks the internal operations performed. Integration Services catalog provides a cleanup job which can be controlled by several catalog properties. To view what operations have been performed on the Integration Services Catalog, right click on the catalog and click on Operations menu item as shown below:

integration services catalog

The Operations window will list all the operations performed in a grid in descending order. You can double click any of the rows in the grid to display the detail information about the operation and its execution.

analyzing the operations performed


8 - Validating the deployed project or package...

Validation is the process to avoid runtime failures, it checks that the package does not have any issues which can cause it to fail during runtime. For example, it confirms that either parameters have necessary values or can get the necessary value with a specific environment reference. You can either validate the whole project at once or each package individually. To validate a project, right click on the project and click on Validate menu item as shown below:

validate the ssis project

When you are validating a project or package you need to specify the environment reference to use for parameters and environment variables for reference resolution. Select the reference from the Environment references combo box and click on OK to start the validation operation. In my case I have created a dummy environment (called NA) with incorrect variable values and I am expecting validation to fail.

select the reference from the enviroment references combo box

And as expected I get the validation error as shown in the image below. So what I did is, I provided a wrong value for the DatabaseName variable of NA environment which could not be validated because that database does not exist and hence it failed:

ssis project validation information


9 - Redeploying the project to Integration Services Catalog...

Changes are inevitable, no matter how much time you spend during planning, design and development there will come a time when you need to make changes either to fix bugs, to improve the performance or add features. So now the question is how we can go ahead and redeploy the changes to Integration Services Catalog? The process of redeploying a SSIS project is not much different from the deploying it the first time. After you are done with your changes in the project (notice I have added one more package in the project for customer data movement), simply go to Solution Explorer, right click on the project name and then click on Deploy menu item as shown below:

redeploying a ssis project

Clicking on Deploy menu will launch the Integration Services Deployment Wizard. On the Select Destination page you can see one warning about preexistence of the project on the Integration Services Catalog, the rest of the steps remain the same as a deployment. Please note, Integration Services Catalog maintains the versions of project deployment (the max number of versions to be maintained is controlled by catalog properties), the last version will be replaced with latest deployment, but the previous versions will also be available in the catalog in case you want to revert back.

enter the destination server name and where te project is located in the integrationservices catalog


10 - Analyzing deployed project versions and restoring to desired one...

To view the previous versions of a deployed project, you can right click on the Projects folder in Integration Services catalog and click on Properties. On the Project Properties window, click on Previous Versions link on the left of the Project Properties as shown below. On the right side you will notice all the project versions which are currently being maintained by Integration Services catalog. You can revert back to any of the available version by clicking on the Restore this version menu.

analyzing deployed ssis project versions and restoring

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 future CTPs or in final/RTM release.
Next Steps


Last Update: 8/23/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




Recommended For You








Comments and Feedback:
Monday, November 12, 2012 - 11:08:46 AM - Steeve Read The Tip

First of all, nice article!

I am looking for the "best practice" for deployment on SQL-Server 2012.

At the moment, I have two different configuration settings in Visual Studio. One for "Development" (dev), the other for "Production" (prod). In the configuration settings itself, I set up the deployment path in the SSISDB-Catalogue for dev and prod.

In the project parameters, I defined the parameters for each configuration setting. That is the way, I deploy for production and development. And in some days, we will have a second SQL-Server 2012 for testing purpose. Then, it is easy for me, to configure the second server in the configuration setting.

Ok, every time I deploy, I have to check whether I choosed the correct configuration ;)

That is the way I deploy. So, what is the better way?


Tuesday, January 15, 2013 - 10:52:02 PM - Phaneendra Read The Tip

Hi Steeve,

If you look at a typical deployment scenario of any application across environments, at some point you need to conciously change the environment details before deployment or make one copy for each environment and use that copy based on the environment where you want to deploy. However, in this case, there would be maintenance overhead because of the changes that could come up while moving from Dev to Test, Test to UAT and UAT to Prod.

Just my thought.

Regards,

Phaneendra Babu Subnivis


Wednesday, May 01, 2013 - 11:14:54 AM - senthil Read The Tip

Thanks for the detailed page ... Can you explain if it is possible multi person deployment on the same catalog , with out distrubing other person packages ?? thanks


Wednesday, July 03, 2013 - 5:12:55 PM - Paul Lee Read The Tip

I got this error when I try to click on server name.  The data base is in SQL authentication and not allowing to insert credential.

TITLE: SQL Server Integration Services
------------------------------

Failed to connect to server . (Microsoft.SqlServer.ConnectionInfo)

------------------------------
ADDITIONAL INFORMATION:

Login failed for user 'NETOPS\paul.lee'. (Microsoft SQL Server, Error: 18456)

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

------------------------------
BUTTONS:

OK
------------------------------
Any idea how to change or enter login name and password during sever selection?


Friday, January 03, 2014 - 6:17:48 PM - Uziel Bueno Read The Tip

Hi Paul Lee.


The deployment wizard needs to know beforehand where the SSIS is located.

Inside Visual Studio over your project ssis item in the solution explorer, right click and select properties.

 

In the properties window at the left panel locate Configuration Properties, expand this node and select Deployment.

There you should set your server name as SERVER_IP\SQL_INSTANCE or SERVER_NAME\SQL_INSTANCE

and Server Project Path as /SSISDB/FolderName/ProjectName.

 

Hope this helps.


Tuesday, February 18, 2014 - 10:57:29 AM - Shalu Read The Tip

Awesome article



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

Signup for our newsletter


Comments
*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 | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.