SSISDB Benefits and Configuration

Lots of people are using the SSIS catalog database, SSISDB, for SSIS packages.

Actually, using the SSIS catalog is best practice but there are still some shops that use File System Packages. These shops should really consider migrating to the SSIS catalog. Some benefits are:

• Versioning: Version history is kept allowing developers to see list of all versions with date and time of deployment. This makes restoring a package to a specific version much easier.
• Validation: SSIS Catalog validation can be done as a separate step for packages or project instead of having validation included as a part of overall execution time.
• Logging: Logging can be enabled in the package execution GUI.
• Environments & Parameters: SSISDB now becomes a repository for environments allowing for mapping of parameters to test and\or production. This makes it easier to move packages between environments.
• Reports: SSIS Dashboard is based on Reporting Services (SSRS) and gives details of execution or validation of packages and projects.
• Permissions: Permissions can be applied on each folder, project, environment and operation. SQL Server roles can be selected and read, execute or other permissions can be granted or revoked for them. All permission details are stored in tables in SSISDB catalog database

One note of caution with regards to enabling logging… Be careful to only log those things that you really need. Otherwise, you may end up with a SSISDB that is quite large.

There are 4 logging levels that can be implemented at the server, job and package execution levels: None, Basic, Performance & Verbose.

• None: Logging is turned off
• Basic (default value): All events are logged, except custom and diagnostic events
• Performance: Only performance statistics, OnError and OnWarning events are logged
• Verbose: All events are logged

There is a performance hit if you use the Performance or Verbose logging levels, so use them with caution.

Maintenance of the data in SSISDB also needs to be performed regularly and carefully. It is common that while purging old data from SSISDB for there to be package timeouts so plan a good time to run the ‘SSIS Server Maintenance Job’ that is installed by default to help clean up old records.

Make sure that you are only retaining the history that you need. Check your retention settings to make sure you don’t still have the default value or 365 days of history being kept.

A little bit of thought for the care of your SSISDB database and settings will assure that you will have an SSIS environment that will hum along.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!