Using Temporal Tables for Slowly Changing Dimensions

One of my favorite recent additions to SQL Server is the ability to use temporal tables to retain change history. As I wrote in an earlier post on this topic, temporal tables allow one to easily configure a table keep all of the changes (including updated and deleted rows) in a designated history table. In this post, I will share…


Webinar: Change Detection in SQL Server

Change detection is a critical component of any system that moves data from one structure to another. Most data movement mechanisms are designed to move a subset of the data – only that which is new or changed since the last load. For that to work properly, there must be a reliable system for detecting delta data touched since the…


SSIS Catalog Dashboard

For organizations using SQL Server 2012 and newer, the SSIS catalog is the ideal tool for storing, executing, and monitoring ETL logic. The SSIS catalog includes built-in reports that show execution activity for current and historical operations. While these built-in reports are very useful, they have one significant limitation: they can only be viewed from within SQL Server Management Studio….


Why I’m Breaking Up with Facebook

I have been in a serious relationship for more than 12 years. My partner in this relationship has brought me joy through the years, but lately, I feel like I’m giving to this relationship far more than I’m getting out of it. The relationship no longer brings me the joy that it once did, and has suffered from several breaches…


A Better Way to Clean Up the SSIS Catalog Database

Earlier this week, I blogged about the automatic cleanup process that that will clean up 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…


Personal Touch: An Argument Against Automation

At the far end of an out-of-the-way aging strip shopping center in southern Louisiana, there stands a small and modest sushi restaurant. The exterior could be described as tasteful minimalistic: there are no big signs, no flashy advertising, and the word “fancy” would never be used to describe it. Inside, it is a small space, seemingly purposeful in its meager…


SSIS Catalog Automatic Log Cleanup

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…


SSIS Catalog Execution Parameter Values

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. SSIS Catalog Execution Parameter Values…


Processing Multiple Files in SSIS with the Foreach Loop

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…


Using the SSIS Multiple Flat Files Connection Manager

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…