SSIS Execution Status

SSIS Execution Status Lookup

The SSIS catalog comes packaged with a rich set of built-in reports that give those monitoring Integration Services a window into what has been happening inside the catalog. However, if you work with SSIS long enough, you’ll eventually need to write your own queries against the underlying tables. Of the many things the built-in catalog tables and views do well,…


Unzip Files with SSIS

Unzip Files with SSIS

In my last post I shared how to build a package in SSIS to perform simple file archive (zip) operations. In this post, I’ll show you how to go the other direction and unzip files with SSIS. Unzip Files with SSIS Similar to the file zip operation, this solution uses the following tools: 7Zip: This free utility manages the creation…


Zip Files in SSIS

Of the very many things SQL Server Integration Services does well, one of its shortcomings is that it does not have a built-in way to create compressed archive (ZIP) files. Generating such files is a common need in ETL processes, so it’s surprising that the fifth generation of SSIS still does not have a native task to manage this operation….


SSIS Package Validation

SSIS Package Validation in the Catalog

Built into the SQL Server Integration Services catalog is the ability to run a validation without actually executing the package. Running a package validation in the SSIS catalog performs a high-level check against the underlying metadata to check for common points of failure (especially those related to data flows). SSIS package validation is not designed to capture every metadata issue,…


SSIS Data Taps

SSIS Data Taps

One of my favorite testing features of SSIS is also one of the most underutilized. SSIS data taps were introduced with the SSIS catalog in SQL Server 2012 as a way to capture data within one leg of a data flow task and write it out to a file for testing or auditing purposes. In this brief post, I’ll show…


Parameterizing Settings in SSIS

A Shortcut for Parameterizing Settings in SSIS

I’ve written quite a bit about the benefit of externalizing changing values in SSIS packages. Moving static values such as connection strings and file paths to a configurable input makes easier the tasks of testing, changing, and auditing the process in the future. The short and generic story here is: don’t hard code values that can change. In SQL Server…


The Eleven Days of Festivus

The Eleven Days of Festivus

It’s that time of the year again – the Festivus season is upon us! In the next few weeks, families all over the world will be gathering around the Festivus pole while they wait for the traditional Festivus meat loaf to finish cooking. Afterward, they get some things off of their chests during the airing of grievances, followed by the…


On Being Disruptive

Being disruptive is a powerful way to conduct business. Steve Jobs and Bill Gates insisted that computers belonged not just in business offices but in the homes of regular joes, and built businesses around a need that had not fully manifested itself. Twitter disrupted the social media space by bringing to market a medium for short, frequent, and publicly visible…


A Better Way to Execute SSIS Packages with T-SQL

There are several ways to execute SSIS packages that have been deployed to the SSIS catalog, and my favorite way of performing this task is to execute SSIS packages with T-SQL. There are a couple of challenges that come up when executing catalog-deployed packages using the default settings in T-SQL, but I have workaround for those issues which I’ll cover…


SQL Server Change Tracking

Video: Introduction to SQL Server Change Tracking

SQL Server change tracking is a lightweight and synchronous mechanism for detecting inserts, updates, and deletes in tracked tables. For those of us who spend a lot of time in ETL, change tracking is a useful tool for defining what data has changed to make the extract-transform-load process more efficient. SQL Server Change Tracking In this 30-minute video, I introduce…