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:

• Tables and Schemas in the data warehouse
• Dimensions and Slowly Changing Dimensions
• Fact tables and measures
• Intro to columnstore indexes
• Columnstore indexes and partitioning
• Introduction to SQL Server Data Tools (SSDT)
• Connection Managers
• Control Flow Tasks and Containers
• Data Flow Source Adapters
• Data Flow Destination Adapters
• Data Flow Transformations
• Variables and Parameters
• SSIS Expressions
• Transactions and Checkpoints
• Logging in SSIS 2012

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.

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

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

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:

• Deployment of Projects and Packages
• Create the SSIS Catalog