The SSIS Catalog: Install, Manage, Secure, and Monitor your Enterprise ETL Infrastructure

I’m happy to announce the publication of my latest book. The SSIS Catalog: Install, Manage, Secure, and Monitor your Enterprise ETL Infrastructure is now available on Amazon in both paperback and Kindle formats. In this book, I introduce the reader to the SSIS catalog and describe how it fits into an enterprise ETL architecture. This book is, by design, narrowly…


SSIS Parameters

SSIS parameters help to ease the process of passing runtime values to SSIS packages. For values that can change over time, using parameters in SSIS is an ideal means of externalizing those runtime values. In this post, I’ll demonstrate how to get started using SSIS parameters. What Problem Are We Solving? To understand the need for ETL parameters, let’s first…


Secure Your Data Prep Area

I’ve been building ETL processes for many years, and I’ve learned two universal truths: data preparation is messy, and you must always secure your data prep area. A data prep area is very similar to a commercial kitchen, and in the same way that customers aren’t allowed in the kitchen, so should data consumers be kept from accessing in-process data…


Lessons from Iowa’s Caucus Debacle

Earlier this week, the state of Iowa held its caucuses to choose each political party’s nominee for November’s presidential election. Being the first state in each election cycle to do this, the electorate of Iowa has the attention of the nation; a candidate’s healthy or anemic showing in that state often foretells his or her success for the rest of…


Using the JOIN Function in Reporting Services

The JOIN() function in SQL Server Reporting Services is a handy tool that allows you to turn a list into a delimited string value. This function accepts two parameters, a list and a delimiter with which to separate the output, and returns a string with that list separated by the specified delimiter character. Using the JOIN Function in Reporting Services…


Checking SSIS Variable Values During Runtime

As anyone who reads my blog on a regular basis knows, I’m a big fan of using dynamic configurations, including SSIS parameters variables, to make my ETL architectures as flexible as possible. However, along with those dynamic behaviors comes the challenge of troubleshooting and debugging. Because variables and parameters do not have static values (by design), working your way through…


Find Your Missing SSIS Toolbox

Visual Studio and its business intelligence variant SQL Server Data Tools share a very powerful but extremely busy interface. If you happen to close out of one of the tool windows by mistake, finding the right menu option to re-enable that window can take some practice. In this post, I’ll show you to to restore your missing SSIS toolbox. Restore…


One-Click Package Restart in SSIS

This quick tip is likely to be the most brief of the entire collection, but is also one of the most practical. When testing SSIS packages during development in SQL Server Data Tools, it’s very common to run and re-run the same package numerous times. It’s fairly easy to stop and restart the package each time using the Stop and…


SSIS Layout Formatting Options

Most folks I know go to one extreme or the other when it comes to the visual layout of SQL Server Integration Services packages: either they don’t care a tiny bit about the appearance, or they insist on an easy-to-read SSIS layout before a project is considered complete. I am definitely in the second group, and will almost always spend…


Right-Click File Options in SSMS and SSDT

Ordinarily I wouldn’t blog solely about a set of right-click options in an application, but in this Eleven Days of Festivus series I’m sharing some quick tips that have helped me in SQL Server Management Studio (SSMS) and SQL Server Data Tools (SSDT). When working in Management Studio, you can access several handy functions by right-clicking any open file in…