Earlier this month, I hosted the monthly T-SQL Tuesday invitation in which I asked, “What’s in your data detective toolkit?” We got some great responses which I’ll recap here, and I’ll share a few thoughts of my own at the end. First up, Rob Farley shared his approach, which focuses on the bigger picture of the business use of the…
Most of us who work with data have, at least a few times, been presented with a challenge to explore and attempt to make sense of a poorly-defined set of data. Often it’s a collection of text files or Excel documents without any context or documentation. In other cases, it’s a database with no data map or metadata to help…
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…
Ok, I have two admissions that I must bare to the world in this post. The first is that I’ve been a little lazy. I’ve admired – from a distance – this T-SQL Tuesday thing ever since it started. It’s been great! A bunch of people blogging on the same day about their own interpretation of some common topic –…
Recently I discovered a little quirk in T-SQL when using NOT IN with NULL values in the list of values to check. I use the term “quirk” loosely here because the behavior is exactly as designed in SQL Server, though it may not be obvious. Using NOT IN with NULL Values Here’s how it works. If you use a static…
Let’s talk about your development environment. Specifically, I’d like to chat with you about the virtual space where your data architecture team, software developers, and information curators do their development and testing work. A proper development environment is logically separated from the production environment, and is often further partitioned into different realms for initial development, data or functional validation, 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…
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…
The SSIS catalog is a system through which SQL Server Integration Services (SSIS) packages are stored, executed, and logged. Introduced with the release of SQL Server 2016, the SSIS catalog helps to better adapt Integration Services as an enterprise-ready ETL system. In this post, I’ll share a brief introduction to the SSIS catalog, as well as links to content on…
Regular expressions (or simply regex for short) have long been used by system administrators and data professionals for searching and manipulating text. Regular expressions allow the user to find, replace, and manipulate text based on the pattern they define in the expression. While every text editor allows simple search-and-replace capabilities, regex allows for searching for partial matches, using wildcards, and…