What is the SSIS Catalog?

Microsoft gave SSIS a complete makeover with the 2012 release. Most of the changes were cosmetic in nature – pun intended. The biggest change with SSIS 2012 is deployment to the new SSIS Catalog.

In earlier versions of SSIS you had an overwhelming number of decisions to make. Should the packages live on a file share or in MSDB? Should your configurations go in an XML file or maybe in a SQL Server table? How will you protect sensitive information? What about a using framework or third party tool to make managing SSIS easier?

Starting with SSIS 2012, you can forget all those decisions by using the new Project Model deployment and the SSIS Catalog. OK, I admit that switching your existing SSIS projects to the new model is going to take some planning and work, but the end results will be worth it.

The SSIS Catalog is the one place where you will manage SSIS projects and packages, including the configuration and monitoring. If you take a look at SSMS, you will see a new folder, Integration Services Catalogs. The catalog is not set up by default, you will have to create it. Even though the name is plural, at this time, you can create just one catalog.

ssisc1

To create the catalog, right-click Integration Services Catalog and select Create Catalog. You will be asked to enable CLR integration and to enter a password for encryption. If you don’t want your to enable CLR in your environment, you can always create an instance just for SSIS.

ssisc2

Once you click OK, you will see an SSISDB icon and an SSISDB database.

ssisc3

If you are so inclined, you can take a look at the tables and views in the new SSISDB database. The SSIS Catalog has built in logging and reports, but you could create your own reports if you wish.

Once the catalog is in place, you can deploy your projects, and they’ll show up in a folder under the catalog. From here, you can set up environments, map parameters, and run packages, but those are topics for another day.

About Kathi Kellenberger

I am a SQL Server professional working for Linchpin People. I love talking about SQL Server to anyone who will listen, just ask my two year old granddaughter. I love to write and teach. I am so humbled and honored to say that I made SQL Server MVP in 2013.
This entry was posted in SSIS. Bookmark the permalink.

4 Responses to What is the SSIS Catalog?

  1. DURGA PRASAD . says:

    How the SSIS project will identify the newly created Integration Services Catalog, SSISDB.

    • The project will not really need to identify the SSIS catalog. You can change the model from Project Model and Package Model. Otherwise, you can deploy the project to any server where the catalog is in place.

  2. Eduardo Horbino says:

    IS there a way we could update the passwords of oledb connection managers in SSIS Catalog thru powershell or t-sql. If it’s possible, could show some basic examples.

    Thanks,
    Ed Horbino

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s