SSISDB folders, projects, environments explanation

Integration Services Catalog

The 2012 release of SQL Server brought a new capability for SSIS package management and I wanted to put together a comprehensive post on some of the management side of things. Getting the nomenclature sorted out can be rather helpful so let's start there. Within SQL Server Management Studio, SSMS, connect to a 2012 instance. At the bottom, there should be an "Integration Services Catalogs." If that expands when you click the plus sign, then someone has configured the server to run Integration Services.

1. SSISDB

Despite the "Catalogs" part of the above label, there is only one allowable catalog for the 2012 release---the SSISDB. That is a physical catalog/database on the instance that contains all of the bits and bobs for managing Integration Services from within SQL Server for those using the new Project deployment model.

And lo, the people rejoiced as Microsoft has stopped stuffing everything into msdb...

Right click options

• Active Operations - Lists all currently running SSIS packages with an option to Stop the selected package.
• Create Folder - Creates a folder with an optional description
• Start PowerShell - Self explanatory
• Reports - A variety of native reports about package executions.
• Delete - This deletes the SSISDB catalog. Doing this will result in having to recreate the catalog and a loss of all your history.
• Refresh - Self explanatory
• Properties - Contains information about the default logging settings and the project versioning.

2. Folder

A folder contains Project(s) and, optionally, Environment(s).You must create a folder. The deployment wizard will not automagically create one for you. You may call it whatever you like. You may have all of your SSIS projects under a single folder, have a 1:1 mapping between projects and folders or anything in between. Folders are only 1 level deep - No nesting

Right click options

• Start PowerShell - Self explanatory
• Reports - All Executions, All Validations
• Delete - This deletes the folder, assuming there are no Projects or Environments below it.
• Refresh - Self explanatory
• Properties - Contains the name, version and a description.

3. Projects

This is automatically created when a folder is created.

Right click options

• Deploy Project - IS Dpeloyment Wizard
• Import Packages - Integration Services Project Conversion Wizard
• Start PowerShell - Self explanatory
• Reports - Only Custom Reports are available
• Refresh - Self explanatory

4. An instance of a project

In this example, I had an SSIS project created called SSIS2012. I also had one called SQLSaturdaySSIS. This is the name of the associated .dtproj in Visual Studio. If you don't like this name in the Integration Services Catalog, you cannot change it here. You must update your original solution, recompile it and deploy it back into the catalog.

Right click options

• Configure - This allows for the specification of values for Parameters and/or Connection Managers.
• Validate - Attempt to validate the deployment. Ensures all the metadata is valid, connections work, etc, without actually "running" the packages.
• Move - Relocate the project from one folder to another.
• Versions - The catalog now keeps track of previous revisions of your deployed project. This is not proper version control.. Phil Brammer has a clever writeup about extracting old versions of projects.
• Export - This allows you to pull the .ispac out of SQL Server to disk
• Start PowerShell - Self explanatory
• Reports - Two native report options
  • All Executions - Provide a history of all package executions, where all is however far back you have the catalog configured to retain history
  • All Validations - Provide a history of all package validations.
• Delete - Deletes the project
• Refresh - Self explanatory
• Properties - Provides the Name, Identifier, Description, Project Version and Deployment Date. Name and Description come from the .ispac's @Project.manifest file. Identifier, Version and Deployment date are all things internal to the catalog's management. The Deployment date though, that can be handy if things "suddenly" start acting up you can quickly determine if there might have been a code change. Emphasis on might because deploying the same .ispac three times in a row will result in three different versions in history with no actual change.

5. Packages

This folder is automatically created when you deploy an ispac into a folder.

Right click options

• Start PowerShell - Self explanatory
• Reports - Only has the option for Custom reports
• Refresh - Self explanatory

6. A package

Right click options

• Configure - This allows for the specification of values for Parameters and/or Connection Managers
• Execute - Brings up the dialog to prepare a package for execution.
• Validate - Attempt to validate the deployment. Ensures all the metadata is valid, connections work, etc, without actually "running" the packages.
• Start PowerShell - Self explanatory
• Reports - Two native report options
  • All Executions - Provide a history of all package executions, where all is however far back you have the catalog configured to retain history
  • All Validations - Provide a history of all package validations.
• Refresh - Self explanatory
• Properties - Exposes the Name, Identifier, whether it's an Entry Point package and the Description.

7. Environments

Environments are the successor for Configuration resources (XML, Table, OS Environment Variable, Registry value).

Right click options

• Create Environment - Create a new Environment under this folder. Requires a Name and an optional Description.
• Start PowerShell - Self explanatory
• Reports - Only has the option for Custom reports
• Refresh - Self explanatory

. An Environment

FileSystem and Database are examples of two Environments I have created. You can define as many Environments as you wish. I typically see people naming them for the server class they are in (dev, test, load, stage, prod) but I don't think that makes as much sense as Environments based on the type of thing they configure (FileSystem, Database, etc). Future blog material though. For now, know they exist as a dictionary of values. You can mark them as Sensitive so you can store secure materials.

Right click options

• Move - Relocate the Environment from one folder to another. Any configurations built on that Environment do not have a cascading update applied.
• Start PowerShell - Self explanatory
• Reports - Only has the option for Custom reports
• Rename - Changes the name of the Environment.
• Delete - Deletes the Environment
• Refresh - Self explanatory
• Properties - 3 tabs. One is the Name, ID and Description. Second is a list of the Name, Description, Type, Value, Sensitivity of each variable. The Third tab describes the permissions associated to the Environment.

GUI isn't everything

Even though there's a GUI for all this management, don't hesitate to click that Script button on any of these interfaces and look at the generated scripts. I might use the GUI to build out my Environment and configure a project to use them on our Dev server but you can be assured I've clicked the Script button and saved that out to a version controlled file. When it's time to migrate up the chain I update server names and file paths and hit F5. You're welcome to make mistakes hand key those values per environment but while you're doing that, I'll be off checking StackOverflow.