SQL Server Integration Services (SSIS) is a powerful tool for creating and managing data integration workflows. With the introduction of the SSIS Project Deployment system in SQL Server 2012, deploying SSIS packages has become much easier. However, there are a few aspects of the SSISDB that could be improved to avoid potential headaches in the future.

Retention Period

By default, the retention period in the SSISDB is set to 365 days. While this may work for some scenarios, it can cause issues when dealing with high-frequency SSIS packages that run hundreds or thousands of times a day. The growing size of the SSISDB can lead to disk space problems or timeouts during autogrowth.

To change the retention period, you can go to the Integration Services Catalogs folder, right-click on the SSISDB, and select “Properties”. However, if your SSISDB is in an unusable state, you can update this property directly using the following SQL code:

SELECT * FROM [catalog].[catalog_properties]
UPDATE [catalog].[catalog_properties]
SET property_value = '7'
WHERE property_name = 'RETENTION_WINDOW'

Cleanup Process

As the SSISDB grows in size, it becomes increasingly difficult to delete old records from it. The existing cleanup job relies on DELETE CASCADE triggers and the tables themselves are not well-indexed for deletion. This can lead to failures, especially when SSIS jobs are running concurrently.

A better approach to cleaning up the SSISDB tables is described in an article by Mark Tassin. His process runs faster and can be executed simultaneously with SSIS jobs. To implement this solution, you need to grant execute rights to Mark’s new stored procedure, cleanup_server_retention_window_bottomup, to the ##MS_SSISServerCleanupJobUser## user in the SSISDB.

If you decide to use Mark’s stored procedure, you may also want to pass in the retention window as a parameter. Here’s an example of how to do it:

DECLARE @retention_window_length INT
SELECT @retention_window_length = CAST(property_value AS INT) FROM [catalog].[catalog_properties] WHERE property_name = 'RETENTION_WINDOW'
EXEC [internal].[cleanup_server_retention_window_bottomup] @retention_window_length = @retention_window_length

In some cases, it may be helpful to reduce the batch size when calling Mark’s stored procedure.

Conclusion

The SSISDB is a valuable addition to SQL Server, simplifying the deployment of SSIS packages. However, it’s important to be aware of the default retention period and the challenges of cleaning up the SSISDB as it grows in size. By adjusting the retention period and implementing a more efficient cleanup process, you can avoid potential headaches in the future.