Those of us who write technical articles and deliver technical presentations are always on the lookout for the perfect data set for demonstration and testing. Microsoft has done a good job of putting together sample databases including Wide World Importers, AdventureWorks, and Northwind Traders. Personally, I’ve found each of these useful, but I had no particular attachment to any of…
A few years back, I wrote a blog post about using an SSIS object variable as a data flow source. In that post, I described how you could load a set of query results into an object-typed variable in SQL Server Integration Services and then use that in-memory data as a source within a data flow. In the comments and…
When moving data in an extraction, transformation, and loading (ETL) process, the most efficient design pattern is to touch only the data you must, copying just the data that was newly added or modified since the last load was run. This pattern of incremental loads usually presents the least amount of risk, takes less time to run, and preserves the…
For the better part of 15 years, SQL Server Integration Services (SSIS) has been the go-to enterprise extract-transform-load (ETL) tool for shops running on Microsoft SQL Server. More recently, Microsoft added Azure Data Factory (ADF) to its stable of enterprise ETL tools. In this post, I’ll be comparing SSIS and Azure Data Factory to share how they are alike and…
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 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…
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…
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…
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…
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…