Administering SSIS Using The SSIS Catalog

In this article, we will go through the below topics in detail.

• Using the SSIS catalog
• Choosing and using the different deployment models
• Using T-SQL with SSIS
• Managing security in SSIS
• Scheduling and monitoring packages

Welcome to the most exciting Topic in this Tutorial, the administration Topic. Okay, it is probably the most dreaded Topic, as administration tends to be a dry subject, but this Topic is a must-read for anyone who needs to execute, manage, and administer SSIS packages. You will learn how to parameterize, deploy, and administer the SSIS service. You will see the SSIS catalog and how it will make executing packages much easier. You will also learn how to create a standalone ETL server, about some of the command-line utilities, and the T-SQL commands you can use to make your job easier. After reading this Topic, you’ll be able to create a package that doesn’t require any effort to migrate from development to production after the first deployment.

Using the SSIS Catalog

The SSIS catalog was introduced in SQL Server 2012. It is the central storage location for SSIS. Here you will administer projects, packages, parameters, and environments. Log in to the database engine in SSMS to work with the SSIS catalog, which is shown in below screenshot.

When you first install Integration Services, the SSIS catalog will need to be created. Create it manually by right-clicking on the Integration Services folder in the Database Engine in SSMS and selecting Create Catalog. The Create Catalog prompt will ask for a password. This password creates a database master key, and the master key encrypts sensitive data in the catalog.

The SSIS catalog is implemented as a SQL Server database named SSISDB. This database contains tables, views, stored procedures, and other objects. Queries can be written to the SSISDB just as you would to any other SQL database. In the SSIS catalog, every object is stored in a folder. Folders can be used to set up security that controls which users can see the objects in the catalog folders. For example, if you want users to have administrative rights over the folders, then grant them MANAGE_OBJECT_PERMISSIONS in the SSISDB. The SSISDB is the storage location for all Integration Services objects. The SSIS catalog is used to administer this database. Security is covered in depth later in this Topic.

Setting the SSIS Catalog Properties

The SSIS catalog has several properties that you can modify to change its behaviour. Right-click on the SSISDB under the Integration Services Catalogs folder and select Properties. below screenshot shows this Catalog Properties window.

The SSIS catalog holds the information about all of your package executions, whether the package failed or succeeded. It contains a list of all the packages in your catalog and the executable tasks in those packages. Parameters and their values are stored in the SSIS catalog. Just about any object or property associated with your SSIS packages is housed in the SSIS catalog. The stored procedures are the tools used to control and manage the SSIS catalog. There is also a library of views that make it easier to view the information in the SSISDB. It will make the information in this Topiceasier to understand if you take a few minutes to look at some of the views and the tables in the SSISDB. Go to SSMS and browse through the tables and views to become familiar with the objects in the SSISDB.

When you execute a package, the package execution is written to the SSISDB. In fact, just about anything you do to the SSIS catalog is going to write or at least alter data in the SSISDB. There are even built-in reports now that query from the executions saved in the SSISDB. The properties of the SSIS catalog are stored in the SSISDB also.

You can alter the SSIS catalog properties using the catalog’s built-in stored procedures. To view the properties, run the stored procedure catalog.catalog_property. To change the properties, run the stored procedure catalog.configure_catalog. However, note that the properties shown in below screenshot have different names than the properties used by the stored procedure. Below Table resolves these differences.

SSIS Catalog Properties

That is right; you can now use T-SQL to control the SSIS environment. The SSIS catalog includes a wide range of stored procedures that represent a TSQL API for managing the SSIS server. In fact, this Topichas an entire section on using T-SQL with SSIS. Here is an example of running the stored procedure to set the cleanup property to false:

EXEC [SSISDB].[catalog].[configure_catalog]
@property_name=N’OPERATION_CLEANUP_ENABLED’, @property_value=N’FALSE’

Parameters can be encrypted in the SSIS catalog. This enables you to store connection strings with a password without any fear of a plain-text password, unlike previous versions. To set the encryption algorithm, use the Encrypt_Algorithm property. To do so, first you must put the catalog in single-user mode. Then run the catalog.configure_catalog stored procedure to set the algorithm to one of the following options:

• DES
• TRIPLE_DES
• TRIPLE_DES_3KEY
• DESX
• AES_128
• AES_192
• AES_256 (default)

The encryption algorithm AES_256 is the default level. No one encryption level is perfect for all situations. AES stands for Advanced Encryption Standard, and DES stands for Data Encryption Standard. DES was the standard since 1977, and now AES has been the standard since 2001. AES is generally the faster encryption. A thorough encryption explanation is beyond the scope of this Tutorial ; in fact, there are entire Tutorials on just encryption. However, here are some basics to keep in mind:

• The stronger the encryption, the more the CPU is used.
• Longer keys give better encryption than shorter keys.
• Long passwords are stronger than short passwords.

Executing and deploying packages writes data to the SSISDB, and as you can imagine, after many package deployments and executions there will be a lot of data stored in the SSISDB. This data will need to be cleaned up. A SQL Agent job is run to clean up old data from package executions and project deployments. Two properties are used to control this operation:

• Operation_Cleanup_Enabled: Setting this to true enables the operation job.
• Retention_Window: Set this to specify the maximum number of days to keep data.

You can control some operations using stored procedures like catalog.operations and catalog.extended_operations_info.

Another great feature of SSIS is project versioning. Gone are the days of deploying a project and overwriting packages and losing them forever. Now when you deploy a project there is a version number assigned, and you can retrieve old versions of projects.

After you have deployed a project dozens of times, you will want to clean up the old versions. The version cleanup job deletes old versions of your projects. Two properties control the behaviour of the version cleanup job:

• Version_Cleanup_Enabled: Set this to true to enable the version cleanup job.
• Max_Project_Versions: Set this to specify the maximum number of versions to keep in history.

 

Frequently Asked SSIS Interview Questions & Answers

 

You can use the stored procedure catalog.object_version to view versioning information. You can also right-click on a project in the catalog and select Versions to see the versions of a particular project (see below screenshot). Toward the bottom-right corner of this window is a button for restoring old versions of projects.

Another property of the SSIS catalog is the validation timeout. Validation is the process of checking the connections in an SSIS package. The time it takes to run this validation varies according to your servers. Validation runs asynchronously and has a timeout property that controls how long the service will wait for a response. Packages with more connections take longer to validate. The Validation_Timeout property is set in seconds, with a default of 300 seconds (5 minutes). You can also run validation on a project by running the stored procedure catalog.validate_project or on a package by running catalog.validate_package. Alternately, you can right-click on a project or package in SQL Server Management Studio and select Validate.

SSISDB

When you install Integration Services 2014, it will not automatically install the database named SSISDB. To create this database you will need to rightclick on |the SSIS catalog in SSMS and select Create Catalog. Below screenshot shows an image of this database and some of its tables.

This database contains all the information and objects for the SSIS catalog. You will administer Integration Services using the SSIS catalog, but your DBA will back up and secure the SSISDB just like any other database. Because of the encryption key specified when creating the catalog, backup and restoring, particularly restoring to a different server, is more involved than a typical database.

There are several tables and stored procedures in the SSISDB. When you execute a package, deploy a project (in the project deployment model), delete a project, or do any other work with Integration Services, you are making changes to the SSISDB. Therefore, you can control your Integration Services instance using T-SQL. This is discussed in more detail in the “Using T-SQL with SSIS” section.

 

List of Related Microsoft Certification Courses:

SSRS	Power BI
SSAS	SQL Server
SCCM	SQL Server DBA
SharePoint	BizTalk Server
Team Foundation Server	BizTalk Server Administrator