SSIS Catalog Maintenance in the Azure Cloud


By:   |   Updated: 2019-08-02   |   Comments   |   Related: More > Azure

Problem

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?

Solution

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


Last Updated: 2019-08-02


get scripts

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools