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

Processing Multiple Files in SSIS with the Foreach Loop – Business Intelligence Article

Yesterday I wrote about the little-known but still useful multiple flat file connection manager. In this post, I will briefly show a more commonly used alternative approach for processing multiple data files: the foreach loop container. The SSIS foreach loop container The foreach loop container is used to iterate through a discrete list of items at runtime. That list could be a static list of items coded at design time, rows in a result set, nodes in an XML file,…

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

Extract the File Name in SSIS Data Flows using the FileNameColumnName Property – Business Intelligence Article

Posted By: Tim Mitchell December 14, 2018 When extracting data from a flat file, it is handy to have the name of the file from which the data was retrieved. Whether you capture this information for auditing purposes, or you include it directly in the output table, the odds are good that you’ll want to have that filename for use later in the process. You could do this using an SSIS variable, but a simpler way to include the file…

Read More

We provide a omprehensive collection of self improvement articles focusing on productivity, motivation and self confidence.