How To Clean SSISDB History Periodically

This blog exclusively explains the process of cleaning the execution history of SQL Server Integration Services packages from SSISDB.

Objective

This write-up explains the process of cleaning the execution history of SQL Server Integration Services packages from SSISDB.
 
My Assumption 

In my environment, I had several packages deployed and had been running 24/7 continuously every day. I thought the default job, i.e., "SSIS_Server_Maintenance_Job" was there already to take care of SSISDB database growth.

Issue 

But after a few months, I noticed that the size of SSISDB had grown to be huge!

Next, I started to understand what exactly the default job, provided by Microsoft, is doing. Well, it's not cleaning all the eligible history (as I expected) but just doing the deletion only for a few operations (around first 10 operations)!!!

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c407fb47-e6b5-4e61-a20d-c86d814df0a0/how-to-clean-the-ssisdb-size?forum=sqldatabaseengine

Recommendations 

I Googled a few more websites and came across a few suggestions...
  • http://www.ssistalk.com/2013/01/31/ssis-2012-catalog-indexing-recommendations/
  • https://devjef.wordpress.com/2015/02/03/ssisdb-why-bigger-isnt-always-better/
  • http://thinknook.com/truncate-ssis-catalog-database-operation-log-tables-2012-11-03/

But all the proposals were leading me to do some updates on the Database which I didn't want to/wasn't allowed to do in my environment. So, what next?

Customized Solution 

I decided to understand the SSISDB database model and finally, implemented the SSIS package to delete all the operational data based on the user-defined retention window. The solution has been uploaded to this blog. It contains source code, deployment document, SQL Agent job, release notes, and SSISDB database relational models as well.
 
Clean SSISDB History Periodically 
 
Technical Details
  • Database: SQL Server 2012
  • IDE: SQL Server Data Tools 2012 
SSISDB Database Model
 
If the image is not clear, then don't worry about that; you can find it in the attached zip too.
 
Clean SSISDB History Periodically 
 
Solution
 
Clean SSISDB History Periodically 
 
Project Parameters 
 
Clean SSISDB History Periodically 
 
Package Variables 
 
Clean SSISDB History Periodically 
 
ControlFlow
 
First, to get the retention window value to identify the falling operation IDs within the limit -
 
Clean SSISDB History Periodically 
 
Looping through all OperationIDs one by one and clearing starting from child tables till the parent table. I think, there is no need to explain in detail because the below flow diagram is doing the job already!
 
Clean SSISDB History Periodically 
 
After clean-up operation, we are invoking two default stored procedures provided by Microsoft. These are invoked just to do it the same way the default job "SSIS_Server_Maintenance_Job" does!
 
 Clean SSISDB History Periodically
 
Conclusion
 
After deploying the solution, the first run, it took a lot of time because it had to clean-up all the operations piled-up over the years in SSISDB.
 
From the second run, it's smooth, efficient, and I noticed no disturbance to other package executions.