SSIS Lookup Cache Modes

In SQL Server Integration Services, the lookup component is one of the most frequently used tools for data validation and completion. The lookup component is provided as a means to virtually join one set of data to another to validate and/or retrieve missing values. Properly configured, it is reliable and reasonably fast. To get the most out of this component,…


Using Raw Files in SSIS

SQL Server Integration Services does a great job of retrieving and processing data on the fly, directly in the data flow pipeline. However, there are circumstances that occasionally require the persistence of result sets in SSIS for use during package execution. For these such cases, one option is to use SSIS raw files. In this SSIS Basics post, I will…


SSIS Precedence Constraints

In the control flow for SQL Server Integration Services, the “lines” we commonly use to connect tasks to each other are actually smart controls. These SSIS precedence constraints allow the ETL developer to customize the program flow from one task to another. The most common use of precedence constraints is to simply connect two tasks with the default constraint, which…


How Much Memory Does SSIS need?

One of the most common questions I am asked about ETL configuration is how much memory SSIS requires. When configuring a new instance of SQL Server running SSIS, or adding SSIS to an existing SQL Server installation, it is important to understand how SSIS uses memory, and what factors in your package design will impact the amount of memory used…


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…