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…
Last week, I attended the annual PASS Data Summit in Seattle. This was the fourth year of the event since Red Gate took over stewardship of PASS after that organization ceased operations in 2021. This has always been my favorite data community event; I first attended in 2005, and since 2009 I haven’t missed a single Summit. This year’s Summit…
SQL Server Integration Services (SSIS) is a mature, proven tool for ETL orchestration and data movement. In recent years, Python has exploded in popularity as a data movement and analysis tool. Surprisingly, though, there are no native hooks for Python in SSIS. In my experience using each of these tools independently, I’d love to see an extension of SSIS to…
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…