SQL University – SSIS 2012 Deployments, Lesson Two — Part A

Lesson Two. Deploying Projects in SSIS 2012 (Today’s class is longer than I expected, we’ll take a break and finish tomorrow.)

Before we get started, a big thank you to Jorge Segarra (blog|Twitter) for inviting me to write for SQL University .

PreRequisite: Lesson One

By the end of this part of the lesson you should understand the following processes/terms.

  1. Building SSIS Projects
  2. Ispac files
  3. The SSIS Catalog
  4. Deploying ispac files

By the end of the second half of the lesson, the following will be covered:

  1. SSIS Catalog Environments
  2. SSIS Catalog Environment Variables.
  3. SSIS Executions
  4. Building Agent jobs with SSIS Catalog projects.

So you’ve got your Project with a Parent package. (by the way, you should designate Parent Packages as Entry Point packages inside a project by right clicking them and selecting “Entry Point Package”). You’ve got some child packages. You’ve got some variables. You’ve got project level parameters for values which should be available to all packages inside a project (like say, a directory for importing/exporting files, a client to process, a business unit to process, etc. ) and you’ve got package parameters for values which need to be passed from parent packages to child packages. You’re ready to Build, Deploy and Execute in SSIS 2012 utilizing all of its new toys features.

Oooo shiny new SSIS Project—Ready for Deployment

I briefly touched on how a “Build” in SSIS now generates an ispac file in Lesson One. To build the project and create the ispac file , simply Right Click the project and select Build. Or click on Build from the main menu in SSIS and select Build __ (projectname). The ispac file will go to the bin/Development directory under your root project directory.

An ispac file. It contains all the elements in the project. Packages, parameters, connection managers, everything.

Now you’ve got a file to really deploy. Good for you. You’ve hung on this far. Where are you going to send the little ispac file?

There is a new destination for Project Deployed Projects. It is called the SSIS Catalog. And it’s kind of a big deal. Think of it as ETL Ground Control.

The SSIS Catalog is located inside Management Studio, under the typical place most of us spend all of our time: Database Engine Connections. (By the way, the Integration Services Service connection is still available. Remain Calm! )

A new Node. A new (non-MSDB) Era. SSIS inside Database Engine connections.

What the SSIS Catalog gives us:

  • Integration Services Dashboard— This refers to the built in logging available to all Projects deployed–regardless of logging coded in the package design (old school, still available, remain calm). There is a suite of reports available to peruse regarding package executions. See Jamie Thomson’s post here on the reports and logging: http://t.co/OOoLwgh. Again, to my veterans, there is a way to integrate old school custom logging with the new logging in the SSIS Catalog. There is a new system variable, ServerExecutionID, which premiered with RC0, that you can add to your custom logging. That way, you retain your old logging and are also able to link your old auditing data to the new logging in 2012. Logging is one feature in SSIS that the old and the new can both be used in one project.

Use a familiar SSRS looking dashboard to snoop on your SSIS.

  • A more robust control panel for manually executing packages— especially the ability to manually override connection managers and variable values–something that was trickier than it should have been pre-2012.
  • Folders. I know that “folders” have been around for a long time. And the folders inside the SSIS Catalog are pretty plain. They are only one level deep, available for creation under the main node of Integration Services, no customized subfolders. BUT when you create a folder, you automatically get the Projects folder and the Environments Folder. Folders give the benefit of logical grouping AND security is configurable at the folder level.

We brought our own subfolders, thanks!

  • Environments–great idea! Environments are catalog objects which enable you to pre-set suites of values for configured items in a project. An example would be to use one each for your Production, QA, UAT, and Development environments. You could configure the proper server names for the connection managers.
  • Environment Variables. Oh Boy. These also are a great idea. But the name is unfortunate. You know why? Because in legacy deployment model and configurations, there’s also a beast named Environment Variables (see http://msdn.microsoft.com/en-us/library/ms141682.aspx Direct and Indirect configurations for more info. ) Anyhoo, Environment variables in the context of the SSIS catalog are variables inside Environments which enable you to substitute variable values for the values of connection managers and or other parameters in the packages you execute.

Let’s use what we’ve learned so far and deploy a Project!

You deploy projects with the Integration Services Deployment Wizard. Launch the deployment wizard from BIDS or from the SSIS Catalog. From BIDS, right click the project and select deploy. From the SSIS Catalog, right click on the projects folder underneath a main folder and select deploy. The same wizard appears. You can deploy a file (ispac) or you can grab a previously deployed project from the SSIS catalog and deploy it to another folder on the same Server, or to a different server.

Wizards. There’s always a wizard.

Steps of deploying from the Integration Services Deployment Wizard once it is launched:

  1. Introduction. There is an optional Introduction page. Click Next (you can also click the box to never see it again)
  2. Select source. Select your source project. If deploying in BIDS, this is most likely the ispac file you just built, which will be default value. You can also navigate to a project on a server which has already been deployed.
  3. Select destination. This is the folder inside your SSIS catalog where you want to deploy the project. You can choose a folder under your catalog you have already created, or you can create new folder from the deploy wizard.

Destinations. You can create the new folder here under the catalog if you ‘d like, or choose an existing folder.

4. Review/Results. You get to watch it spin and watch the steps as they succeed or fail. Note the changing protection level. The SSIS Catalog will use its own key to encrypt sensitive items in projects and packages, and you’ll never have to worry about which encryption method to use AGAIN. What will the certification exams use now as material to confound us?

Most Deployment wizards do not come with screaming Kevin Bacon. I special ordered mine.

Well kids, we’ve covered a lot and you all look hungry. You guys run out to the snack bar and get some caffeine, and we’ll push through the rest of the material when you get back. Will someone bring me back some animal crackers?

We’ll finish this tomorrow. Thanks Jorge for SQLU!

Lesson Two Part B (which should have been called Lesson Three)

4 thoughts on “SQL University – SSIS 2012 Deployments, Lesson Two — Part A

  1. Mike,

    EXCELLENT question. There is enough material for how to automate the deployment for a whole new blog post–let me refer you to some good ones already written. Basically, Msft has built a whole suite of cmdlets in PowerShell to handle interacting with the catalog. There are also TSQL commands.

    PowerShell Posts:

    And on using TSQL:

    Thanks for reading Mike.

Leave a Reply