Whitepaper : What is the SSIS Catalog?

Microsoft has introduced a new model for deploying SQL Server Integration Services (SSIS) packages into SQL Server 2012. Prior to releasing this new model, the locations to store SSIS packages were:

  • Deploying SSIS packages to a directory on the file system.
  • Deploying SSIS packages to the package store. This option stores the packages on the file system but uses the SQL Server Directory to store them in.
  • Deploying SSIS packages to SQL Server. This option stores the packages in the MSDB database in the system table syssispackages.

And the methods for deploying SSIS packages into the above locations were:

  • Create a deployment utility for the project the packages were developed in
  • Use the DTUTIL command line utility
  • Use SQL Server Management Studio (SSMS) to deploy packages manually

One can immediately begin deploying projects, once the SSIS catalog has been created on the instance. One can upgrade the packages that have been developed in SQL Server to support the new deployment model by using the “Upgrade Wizard” which is available in Data Tools or Visual Studio. One lose some capabilities after upgrade procedure such as:

  • Configurations used to configure onge available. This has been replaced by ‘Environments’.ew model, the locations to store SSIS packages were:
  • ‘Project Parameters’ now replace configurations.

This whitepaper describes how to create SSIS objects and projects and how to execute the SSIS packages. Examples provided in this whitepaper should provide someone new to the catalog a basis to use the SSIS catalog. For the more experienced, the whitepaper summarizes some more advanced features of the catalog and show how to create SSIS objects and projects and how to execute the SSIS packages. In particular, the whitepaper discusses how to create the SSIS catalog, and the SSIS catalog properties and objects. Further, the whitepaper discusses the deployment and versioning of SSIS projects. Further, the whitepaper discusses executing packages via SSMS, SQL Server agent, and stored procedures. Finally, the whitepaper discusses the SSISDB database, in particular execution and operational data.

This whitepaper does not contain all the features and functionality available in the SSIS catalog but provides enough insight and examples to explore the power of the catalog. The advantages of using the SSIS catalog include:

  • Robust deployment environment
  • Built in logging of package execution
  • Metadata around projects, packages, and parameters
  • Analysis of package performance history
  • Enhanced T-SQL scripting and stored procedures for package execution and deployment

By having access to this data, the database administrator or developer can build more robust extract, transform, and load (ETL) solutions, troubleshoot performance issues and execution failures while easily deploying projects. The examples provided here should provide someone new to the catalog a basis to use the SSIS catalog. For the more experienced, it summarizes more advanced features of the catalog.

Presenter: Stan Geiger
Share This:  Facebook Twitter LinkedIn

Stan Geiger has over 25 years' experience with databases. Stan has been in various roles such as business intelligence (BI) architect, data architect and developer. He has designed and built large-scale data warehouses and multidimensional cube installations in various industries such as finance, healthcare, energy, and the US Department of Defense (DOD). He is the Product Manager for the Business Intelligence products at IDERA.

Register to read the full whitepaper.



Register for Free Whitepaper

Register For a Free Whitepaper

*
*
*
*
*