Tim Mitchell
Follow Tim Mitchell on Twitter  Subscribe on YouTube  Connect on LinkedIn  Subscribe to the Data Geek Newsletter
SSIS Precedence Constraints

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

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

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…


Change detection

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

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 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…


Personal touch

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…


delete

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

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. When you execute a package…