SSIS Catalog Maintenance in the Azure Cloud

By:   |   Comments   |   Related: > Azure Integration Services


I migrated my SQL Server Integration Services (SSIS) projects to Azure Data Factory, where the SSIS packages are executed in an Azure-SSIS Integration Runtime. The SSIS catalog is hosted in an Azure SQL Database. I’ve noticed the size of the SSISDB database is growing large because of all the logging by the SSIS packages. Is there some sort of maintenance job that I can run in Azure to clean up this database?


The SSIS catalog stores a lot of information in the SSISDB database: the SSIS packages, projects, folders, environments and so on. It also stores a new version of your SSIS project each time you deploy to the catalog. Furthermore, every SSIS package logs information to SSISDB when it is executing. This means the database will continue to grow in size, unless some of the logging and old projects versions are deleted. In an on-premises database, there is an SSIS maintenance SQL Server Agent Job that take care of this problem. It’s generated when the SSIS catalog is created. You can find more info about the maintenance job in the tip SQL Server Integration Services Catalog Best Practices. The problem is Azure SQL DB doesn’t have SQL Server Agent, so there’s no maintenance job.

Another problem is the default logging levels of the catalog log too much information to SSISDB, causing it to grow too quickly. Ideally you create a custom logging level which logs less information. If you’re on a version of SSMS lower than 18.0, you need to use a stored procedure to create a custom logging level. Or you can just upgrade to the latest version of SSMS, as you can see in the screenshot (taken with version 18.0):

custom logging level

For more information about the logging levels in SSIS, you can check out the following tips:

Let’s now take a look at how we can schedule the maintenance job in Azure.

Creating the Maintenance Job

The easiest option – in my opinion – is to create an Azure Data Factory pipeline with a stored procedure activity. The reason for this is because since you are running SSIS in Azure, you already have an Azure Data Factory v2 (ADF) configured. Executing a stored procedure in ADF is straight forward and you can schedule the pipeline using a trigger.

The first step is to add a connection to your SSISDB database in ADF. In the Author pane, go to Connections and then Linked Services. Click on New and select Azure SQL DB from the list of available connectors and click on Continue.

connector to Azure SQL DB

Choose a name for the connection, the integration runtime to connect through (this will normally be the default AutoResolveIntegrationRuntime) and select the subscription, server and database from the dropdowns. You can connect with SQL Authentication, service principal or Managed Identity.

create new linked service

Test the connection and click on OK to create it. The next step is to create a new pipeline in ADF.

add new pipeline

Give it a decent name and add a stored procedure activity to the canvas.

add stored proc activity

Select the activity, give it a name and go to SQL Account to select the connection we created in the previous steps:

select conn to SSISDB

Go to the next tab to configure the Stored Procedure. If you would take a look at an on-premises SSISDB, it has created a SQL Server Agent job, which executes the following script:

DECLARE @role int
SET @role = (SELECT [role] FROM [sys].[dm_hadr_availability_replica_states] hars INNER JOIN [sys].[availability_databases_cluster] adc ON hars.[group_id] = adc.[group_id] WHERE hars.[is_local] = 1 AND adc.[database_name] ='SSISDB')
   IF DB_ID('SSISDB') IS NOT NULL AND (@role IS NULL OR @role = 1)
      EXEC [SSISDB].[internal].[cleanup_server_retention_window]
on-premises maintenance job

We can skip the check for availability replicas and just execute the stored procedure internal.cleanup_server_retention_window.

config stored proc

All that is left is to create a trigger for the pipeline.

create trigger

Choose a name and pick Schedule as the trigger type. Configure the schedule that is appropriate for your environment.

schedule trigger

Click on Next and then Finish. Finally publish your pipeline and trigger.

publish pipeline and trigger

You can trigger the pipeline to test the stored procedure or you can just hit Debug.

trigger ADF pipeline

Keep in mind if your SSIS catalog has been running for a long time, a lot of logging needs to be cleaned up which can take quite some time.

Cleaning Project Versions

Aside from all the logging, there’s an extra overhead in the SSISDB database: old project versions. It’s possible to clean those as well. In the catalog properties, you can configure how many versions the catalog needs to keep before they are removed:

number of project versions

In the ADF pipeline, copy paste the Stored Procedure activity and give it a new name:

new adf activity

In the Stored Procedure tab, change the stored procedure name to [internal].[cleanup_server_project_version].

configure new stored proc

Publish the updated pipeline so the changes can take effect.

Alternatives for Azure Data Factory

It’s not a prerequisite to use ADF to schedule the maintenance of your SSISDB database. You can use any tool in Azure that can execute a stored procedure and that can be scheduled. The two best alternatives are:

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms