Exam Prep 70-463: Deploying projects in SSIS 2012


This post is part of a series on this blog that will help me, and hopefully you, pass exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012.  So far, we’ve covered:

You’ve finished developing your package and you’re ready to release it to the world!  So, how exactly do you do that?  As with most things, it depends.  Today we’re talking about the different paradigms for deploying SSIS packages and how you’ll likely do it in SSIS 2012 and up.

Deployment Models

Depending on what version of SSIS you’re running, you might use one of two pretty different methods for deploying a project.  Older versions of SSIS used the Package deployment model.  But starting with 2012, a new model, called the Project deployment model was introduced.  Check out the table below for a comparison.

 

Package deployment model Project deployment model
Unit of deployment Package Project
Deployment method Packages and configuration files are copied to a file system or stored in MSDB Projects are deployed to the SSIS catalog on SQL Server
Dynamic values Configurations assign values to package properties Parameters are used to pass in values
Environment values Configuration files are used to store environment-specific configuration values Environment variables are used to set environment-specific parameters
Execution Packages are executed using dtExec or DTExecUI at the command line Packages can be executed stored procedures, the SSMS GUI, or dtExec

Deploying projects in the Project Deployment Model

In the project deployment model, projects are deployed to an Integration Services server, which is just an instance of SQL Server that houses the SSIS catalog as a database called SSISDB.  This database stores all projects deployed to the Integration Services server, as well as their respective packages, parameters, environments, and operational history.  You can view the SSISDB objects within SSMS and query its views just like you would any other database.  You would also manage it just like your other user databases (e.g. back it up, perform index maintenance, etc.).

Note that the SSISDB database isn’t there by default.  To create it, right-click on the Integration Services Catalogs node in the Object Explorer of SSMS and click Create Catalog…  CLR Integration is required in the Project Deployment model, so if you don’t already have it enabled, you’ll see a checkbox that will allow you to enable it now.  The next checkbox you’ll see is Enable automatic execution of Integration Services stored procedure at SQL Server Startup.  Checking this box allows the catalog.startup stored procedure to run at instance startup.  This procedure will fix up the operational status of any packages that may have been running when the instance went down.  Finally, enter a password for the database master key that will be used to encrypt data in the catalog.  Oh yeah, one more thing, Integration Services needs to be installed on the server where you’re creating the SSISDB catalog.  Otherwise, you get this:

Sad trombone

Sad trombone

To deploy a project, you need to start by creating a folder in the SSISDB database, if you haven’t already.  This folder is a way to organize related projects.  Once you’ve done that, in SSDT you can right-click on the project and click Deploy.  The deployment wizard will walk you through selecting the instance where your SSISDB catalog lives and the folder you want to deploy to.  Using one of the test prep kit’s sample projects, this is what you’ll see inside SSMS after you’ve deployed the project.

Viewing your deployed project in SSISDB

Viewing your deployed project in SSISDB

Another way to deploy a project is to build it in SSDT, which will create a deployment file with a .ispac extension.  From SSMS, right click on the Projects node of your folder and click Deploy Project.  Select your .ispac file and click Next.  You’ll then see the same screen to select the destination that you want to deploy to.

Additional Resources

For more information on deploying projects in SSIS 2012, check out the following resources:

 


Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

3 thoughts on “Exam Prep 70-463: Deploying projects in SSIS 2012