Two Things I Would Change in SSISDB Setup

, 2019-01-11 (first published: )

Starting with SQL Server 2012, the SSIS Project Deployment system was introduced.  If you right click on the Integration Services Catalogs folder, you can “Create Catalog” and the SSISDB is born.

I think this has been a great addition to SSIS and makes deploying SSIS packages a lot easier.  Still there are a few things I would change.  The first thing I would change is the retention period and the second is the process to clean up old data.

The retention period defaults to 365 days.  I am not sure why they (Microsoft) choose this default, but it has caused me a lot of headaches.  To get to this property, go to Integration services catalogs and then right click the SSISDB and select properties.

NOTE: if you find you cannot edit this dialog because your SSISDB has gotten into an unusable state you can update this property directly.

select * from [catalog].[catalog_properties]
update [catalog].[catalog_properties]
 set property_value = '7'
 where property_name = 'RETENTION_WINDOW'

Often, the SSIS packages I maintain, are on a schedule where they run hundreds sometimes thousands of times in a day.  This can cause a problem sooner or later when the SSISDB grows to a size where you either run out of disk space or it has trouble trying to autogrow the SSISDB (Error 0x80131904 – Timeout expired). 

This leads to the second thing I would change: the cleanup job.  Once you get an SSISDB that is really large it becomes almost impossible to delete records in it.  First the cleanup_server_retention_window proc depends on DELETE CASCADE triggers.  Second the tables themselves are not indexed well for deletion.  I have seen the existing cleanup job fail because SSIS jobs are running. 

I found this article by Mark Tassin to be very helpful in developing a better way to clean up the SSISDB tables.  His process runs faster and can be running at the same time as SSIS jobs.

NOTE: If you do use this solution and you just replace the first step in the SSIS Server Maintenance Job you do need to grant Mark’s new proc cleanup_server_retention_window_bottomup execute rights to the ##MS_SSISServerCleanupJobUser## user in the SSISDB.

NOTE: if you do use Mark’s proc you may want to pass in the Retention window with sql code something like this:

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

NOTE: I found in some cases it was helpful to reduce the @batch_size when calling Mark’s proc.

The bottom line is I like the SSISDB, but I highly suggest you looking into these two changes to make sure you don’t end up with a SSISDB headache down the road.

Rate

5 (13)

Share

Share

Rate

5 (13)

Related content

Reproduced with kind permission from the blog of Ashvini Sharma (MSFT)

InfoPath forms can be saved to XML, these XML Files can later be used in SSIS XMLSource adapter to pull out the data in tables and columns. However, there are some common problems you may meet in these scenarios. This article describes how to work around these potential problems. The issues mentioned in this article is not only specific to InfoPath files, it can also be referenced in other similar situations as well.

2005-06-20

1,328 reads

Easy Package Configuration

One of the age old problems in DTS is moving packages between your development, test and production environments. Typically a series of manual edits needs to be done to all the packages to make sure that all the connection objects are pointing to the correct physical servers. This is time consuming and gives rise to the possibility of human error, particularly if the solution incorporates many DTS packages. Many companies have provided their own custom solutions for managing this problem but these are still workarounds for a problem that is inherently DTS's.

2004-12-14

1,761 reads

File Inserter Transformation

SQL Server 2005 has made it a lot easier for us to loop over a collection and with each iteration do something with the item retrieved. In this article we are going to show you how to iterate over a folder looking at the files within and doing something with those files. In this instance we will be entering the filename into a SQL Server table and we will then load the actual files we have just found into another SQL Server table. You will note here that there is still the need to load the file names into a table as an intermediate step just as we need to do in SQL Server 2000.

2004-11-17

2,506 reads