SQL SERVER – Creating the SSIS Catalog – Notes from the Field #058

[Notes from Pinal]: SSIS is very well explored subject, however, there are so many interesting elements when we read, we learn something new. A similar concept has been Creating the SSIS catalog.

SQL SERVER - Creating the SSIS Catalog - Notes from the Field #058 Tim_Mitchell Linchpin People are database coaches and wellness experts for a data driven world. In this 58th episode of the Notes from the Fields series database expert Tim Mitchell (partner at Linchpin People) shares very interesting conversation related to how to Creating the SSIS Catalog.


With the release of SQL Server 2012, Integration Services (SSIS) received a significant overhaul. The most notable change was the addition of the SSIS catalog, a new deployment and storage mechanism that helps to centralize and standardize the storage, execution, and logging of SSIS packages. In this post, I will briefly illustrate how to set up the SSIS catalog on a new installation of SQL Server 2012 or 2014.

The SSIS Catalog

When connecting to an instance of SQL Server using SSMS 2012 or 2014, you’ll notice that there is a new node in the tree, entitled Integration Services Catalogs. By default, this node will be empty as shown below.

SQL SERVER - Creating the SSIS Catalog - Notes from the Field #058 58-1

Before we can begin deploying SSIS packages to the catalog on this instance, we need to create the catalog. It is worth noting here that, although the plural node name of Integration Services Catalogs implies that we may have more than one catalog, we can only create one SSIS catalog per SQL Server instance.

Creating the SSIS Catalog

To create the SSIS catalog, simply right click the Integration Services Catalogs node and choose Create Catalog.

SQL SERVER - Creating the SSIS Catalog - Notes from the Field #058 58-2

Next, you’ll be presented with the Create Catalog dialog, which allows you to set the options for creating the catalog. There are really only two things that you need to configure here:

  • Enable automatic execution of Integration Services stored procedure at SQL Server startup. This option will enable the automatic execution of a cleanup stored procedure when SQL Server starts. This stored procedure will check for and update any packages whose status in the catalog was left in limbo due to an unexpected shutdown of SQL Server. I don’t know of any downsides to enabling this, so I recommend that you leave this box checked.
  • Encryption password. Because you can store sensitive information (such as database or FTP passwords) in the SSIS catalog, this database requires an encryption password to properly secure those assets.

SQL SERVER - Creating the SSIS Catalog - Notes from the Field #058 58-3

You’ll also notice that the name of the catalog database (SSISDB) is presented in this window, but it is not editable. In the current versions (2012/2014) of SSIS, the name of the catalog database must be set to SSISDB. Also, the SSIS catalog requires enabling CLR integration, so this option is shown but is also not editable.

Once you’ve configured the encryption password and selected the option of whether to allow the cleanup stored procedure to run, you’re ready to create the catalog. Simply click the OK button in this window to create the catalog. After so doing, you can browse to the Object Explorer in SSMS and see that we now have the SSISDB catalog listed under the Integration Services Catalogs node. The catalog is now ready for use!

SQL SERVER - Creating the SSIS Catalog - Notes from the Field #058 58-4

One final note on the SSIS catalog: If you browse the list of databases after creating the catalog, you’ll notice that there is a new user database named SSISDB in the list of databases. The SSIS catalog is contained in its own database on the SQL Server instance on which it is installed. Unlike prior versions of SSIS which stored the SSIS assets in MSDB, the SSIS catalog uses a dedicated user database to better isolate the SSIS storage.

Conclusion

With a new installation of SQL Server 2012 or 2014, the SSIS catalog is not installed by default. This brief post demonstrated how to easily and quickly set up the SSIS catalog on a new installation of SQL Server.

If you want me to take a look at your server and its settings, or if your server is facing any issue we can Fix Your SQL Server.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – Reverse String Word By Word – Part 3
Next Post
SQL SERVER – Reverse String Word By Word – Part 4

Related Posts

4 Comments. Leave new

  • Buddy Arbuckle
    October 16, 2015 1:49 am

    Does the Catalog have to be on the SSIS server? We have standalone SSIS servers without a SQL Server instance and the dtsx files are executed use config files and Windows Tasks Scheduler.

    Reply
  • Hennie de Nooijer
    March 18, 2016 7:09 pm

    Can I script this ? Create it on the DEV and deploy this to the PROD?

    Reply
  • Hello,

    Is it possible to have Integration Services Catalog folder in the SQL Server instance even if SQL Server Integration Services is not installed in the server.

    Thanks in advance.

    Regards,
    Ghassen

    Reply
  • This should be edited for MSSQL 2017+ to Enable the Server as a SSIS SCALE out master. :)

    Reply

Leave a Reply

Menu