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.
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:
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.
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.
For more information on deploying projects in SSIS 2012, check out the following resources: