Cleaning Up Unused SSIS Packages from MSDB
By Tom Nonmacher
SQL Server Integration Services (SSIS) is a powerful tool that allows for the integration and transformation of data. It is an essential component for managing data warehouses and data extraction, transfer, and loading (ETL) operations. Over time, you may accumulate a number of SSIS packages in your MSDB database that are no longer used or needed. This blog post will guide you through the process of identifying and removing these unused packages from SQL Server 2012 and SQL Server 2014.
Before you start the cleanup process, it's crucial to have a thorough understanding of the packages that are currently stored in the MSDB. This information can be obtained by querying the sysdtspackages90 table in the MSDB database. This table holds details about all the SSIS packages that are stored in MSDB.
USE msdb;
GO
SELECT name, createdate, description
FROM dbo.sysdtspackages90;
The next step is to identify the packages that are no longer in use. This can be a challenging task as there is no built-in mechanism in SQL Server that tracks the usage of SSIS packages. However, a common approach is to review the package's metadata, such as its creation date and last modified date, to infer whether the package is still in use.
Once you've identified the unused packages, the next step is to remove them from the MSDB database. This can be achieved by using the sp_dropdtspackages90 stored procedure. However, be cautious when using this procedure as it will permanently delete the specified SSIS package.
USE msdb;
GO
EXEC dbo.sp_dropdtspackages90 @name = 'UnusedPackageName', @id = 'PackageID';
It's important to note that the approach described above will only work for SQL Server 2012 and SQL Server 2014. If you're using MySQL 5.6, DB2 10.5, or Azure SQL, you'll need to use different methods to clean up unused SSIS packages, as these databases do not natively support SSIS packages.
In conclusion, maintaining a clean and efficient MSDB database is a critical task for any DBA. Regularly reviewing and removing unused SSIS packages will ensure your database runs smoothly and efficiently. As always, remember to backup any data or packages before making changes to your database.