A Better Way to Clean Up the SSIS Catalog Database – Business Intelligence Article

Earlier this week, I blogged about the automatic cleanup process that purges old data from the SSIS catalog logging tables. This nightly process removes data for operations that are older than 365 days. While this is useful, many SSIS admins have complained that this process is very slow and contentious on large or busy SSISDB databases. In this post, I’ll show to you one of the main reasons this purge process is slow, and will share a more efficient way…

Read More

SSIS Catalog Automatic Log Cleanup – Business Intelligence Article

Built into the SSIS catalog is a mechanism that can automatically purge log data after a set period of time. In this post, I’ll show you how to set up and manage that functionality. SSIS catalog automatic log cleanup The SQL Server Integration Services catalog database – SSISDB – has several dozen logging tables that are used to capture details about the administration of and execution within the SSIS catalog. These tables store the history of administrative tasks (such as…

Read More

SSIS Catalog Execution Parameter Values – Business Intelligence Article

When doing any new development or major overhaul of existing SSIS architecture, I almost always recommend to clients that they deploy those packages to the SSIS catalog. Using the catalog to store and execute SSIS packages takes a lot of the manual work out of development and maintenance, particularly when it comes to package logging. When you execute a package in the SSIS catalog, one of the critical pieces of information collected is the list of parameter values used during…

Read More

Using the SSIS Multiple Flat Files Connection Manager – Business Intelligence Article

When building an ETL pipeline to import data from a text file, it’s very common to have the incoming data spread across multiple files. For example, if you are ingesting files generated on a periodic basis (per day, per hour, etc.), you could have dozens or hundreds of files with identical structure. This is an ideal setup for building a modular solution rather than separate import process for each file. To make this process easier, SQL Server Integration Services include…

Read More