Starting with SQL Server 2012, Integration Services (SSIS) packages can now be deployed to a single source for managing execution in multiple environments. The SSIS Catalog is a single database container for all deployed packages. The configuration files are replaced with Environments. Deployed versions are tracked historically and a package can be reverted to a previous deployment. On top of these features, internal reports with a dashboard help when debugging errors or examining performance over time.
To use this feature, the SSIS Catalog must be created. The System Administrator of the SQL Server instance needs to create the Catalog or a user with elevated permissions for creating databases. Figure 1 shows the menu after right-clicking on the Integration Services Catalog folder in SQL Server Management Studio (SSMS).
The Create Catalog… meu option will launch the Create Catalog window. Some configuration takes place during its creation. CLR Integration will need to be enabled on this instance. On SQL Server Startup, the automatic execution of SSIS stored procedure(s) can be enabled or not. The most important part is to provide a strong password for the management of this new database container.
When the configuration is complete, a new database is created on this instance: SSISDB. The database needs the same maintenance as any other production database on this system. Maintenance items include backups, index rebuild/reorganize and update statistics. The recovery mode of the database inherits the properties from the Model database just like all new databases. Simple recovery mode is adequate for SSISDB because of the infrequent deployments.
A user does not need to be a SysAdmin to deploy projects (and packages) to the Catalog. There is a Database Role named ssis_admin in the SSISDB database. This role contains the permissions to deploy projects from Visual Studio. A developer’s Active Directory (AD) account or an AD group can be added to this role.
Deploying SSIS projects, along with the project’s package(s), was added in SQL Server 2012 as well as the SSIS Catalog. This organization of packages in a SSIS Project enables objects, properties and values to be shared among the packages in a project. Figure 5 shows the project.param file associated with a SSIS Project.
This project has multiple packages. The Source Database and Source Server is shared with packages like DimProduct and DimCategory. The connection is created under the Connection Manager in the Project’s solution as seen in Figure 6. Not only can the packages can share parameter values, it also shares these database connections. This example contains staging, source and destination databases. The prefix (project) is added to the Package Connection Managers’ connections as seen in Figure 6 under the objects of the package.
In this environment, the development team uses a separate server as the testing team and production system. The use of Project Parameter enables a single change to effect multiple packages in the project. To deploy the project to the SSIS Catalog, right-click the project in the solution explorer of Visual Studio.
The Integration Services Deployment Wizard will show a welcome screen. You can disable this screen for future deployments. The Select Source will default to the project selected while in Visual Studio. The wizard first brings you to the Select Destination page, but you can use the back button to go to the Select Source page. The Source Selection can be changed, but usually the deployment wizard is launched from the project being deployed. Figure 8 shows the Select Destination screen where the Server Name and Path in the SSIS Catalog are selected.
The Server name will be the instance the Catalog was created. The Path can be an existing one or a New Path can be created. The same path can be used for different projects. All project packages will be together under the project in the Path.
The Review screen gives the options to view the selected source and destination. Once the Deploy button is selected on the Review screen, the Results screen will show the success list or an indication of what failed during deployment.
A package can be launched in several ways. In SQL Server Management Server (SSMS), the Integration Services Catalog folder can be expanded to see the Paths created in the Catalog. Figure 10 shows the menu from right-clicking the package DimCategory in the SSIS Catalog.
After selecting Execute… from the menu, the execution prompts for Parameters, Connection Managers and Advanced options. Figure 11 show the Parameters that can be changed before the DimCategory package is executed. This is where we can change the server or database names for the Project Parameters before the package is executed.
One of the cool features of the SSIS Catalog are free reports. Figure 12 shows the execution report that can be displayed after launching the package. There is a prompt to view or not view the report. There are two additional links to drill down into Messages and Performance. Individual Tasks can be drilled into to show messages from different steps of the package.
If there is an error, the messages are the best place to start to debug a package. To see the performance of this package over time, click the View Performance drill down report.
There is a main Dashboard that can be used to see an overview of all packages running from the Catalog. This dashboard is launched from SSMS by right-clicking on the SSISDB folder under the Integration Services Catalog. Figure 13 shows the path to the report.
Figure 14 shows for today one package has executed successfully and another has failed.
By clicking on the Failed number, the next report gives the ability to drill into the Messages returned from the success and fail steps of the package like in Figure 15
As you can see, the SSIS Catalog has a wealth of information and management for SSIS projects and packages. The reports and drill down capabilities help with debugging problems before opening the package in Visual Studio. Performance reports can give an idea if a package is taking longer to run in the last execution than previous executions. History can be configured and is explained more in the side note at the end of this article. The Catalog is a great place to store and manage SSIS package deployment and execution.
Useful links
Side Note
Other configuration options are available by going to the properties of the Integration Services Catalog from SSMS. Clean Logs Periodically can be set to true or false. This enables or disables a SQL Server Agent Job that is run on a scheduled time. Retention Period (days) indicates to the job how long to keep history of the execution of the packages.
The descriptions of the Server-wide Default Logging Level options can be seen at the following link: Enable Logging for Package Execution on the SSIS Server. The Maximum Number of Version per Project indicates how many deployments will be saved to the catalog of a project. Purging these versions can be controlled by the property Periodically Remove Old Versions.
Starting as a developer in COBOL while at LSU, he has been a developer, tester, project manager, team lead as well as a software trainer writing documentation. Involvement in the SQL Server community includes speaking at SQLPASS.org Summits and SQLSaturday since 2011 and has been a speaker at IT/Dev Connections and Live! 360.
Currently, he is the Chair of the PASS Excel Business Intelligence Virtual Chapter and worked on the Nomination Committee for PASS Board of Directors for 2016.
View all posts by Thomas LeBlanc
- Performance tuning – Nested and Merge SQL Loop with Execution Plans - April 2, 2018
- Time Intelligence in Analysis Services (SSAS) Tabular Models - March 20, 2018
- How to create Intermediate Measures in Analysis Services (SSAS) - February 19, 2018