Working with an ETL design that does a straight insert operation is the most simple, but often ETL processes are required to update data as well. There are several ways to go about updating data with SSIS, one of which (the staging table method) requires a bit more setup but can also perform significantly better. Updating Data with SSIS I…
I ran into a situation this week that brought to light a subtle syntactical error I’d made in creating an SSIS package. I’ve got a client that has given me access to their development server to create some complex extraction queries, which will eventually be rolled into SSIS packages. Since I’m working with read-only access and cannot create stored procedures…
For SSIS developers, the need for proper documentation is crucial. However, the built-in object for documentation, the annotation, is difficult to use. It doesn’t wrap text, doesn’t support varying font styles in a single instance, and doesn’t offer spell checking. Further, all annotations are “at large” and are not attached to a particular object – they are associated with a…
The SSIS expression language is a powerful yet enigmatic entity. Once you get used to its syntax – which is part C#, part T-SQL and part *WTH?!?* – it’s actually somewhat fun to use. However, one thing it appears to be lacking is the ability to use an if/then/else statement. However, there actually is such an instrument, though it doesn’t…
Having cut my teeth on SQL Server 2000, I had some (but not a lot) of DTS experience. Recently I found myself presented with a massive ETL project, and found a perfect opportunity to get started with SQL Server Integration Services. With the help of a couple of great books and one magazine (I’ll mention those shortly), I have spent…
In just a couple of weeks, the PASS Summit will return to Seattle, Washington. This one will be extra special, since it’s going to be the first in-person Summit in three years. When COVID upended normal life back in March of 2020, it also had a huge impact on in-person events. That year, the PASS Summit was changed from in-person…
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…
In my last ETL Antipatterns post, I wrote about the unexciting but very necessary work of documenting ETL processes. The logging of ETL operations is just as (un)captivating as documentation, but is equally as important in the support of data movement and transformation processes. In this post, I’ll discuss a common misstep in ETL process management: ignoring the logs. What…
I usually avoid talking about technology in absolutes, but here’s one that I can share without reservation: On a long enough timeline, every single ETL process will eventually fail. Is your ETL design built to handle a failure? I see far too many SSIS packages, ADF data factories, and other data movement applications built with the assumption of 100% success,…
Azure Data Factory has grown in both popularity and utility in the past several years. It has evolved beyond its significant limitations in its initial version, and is quickly rising as a strong enterprise-capable ETL tool. In my last post on this topic, I shared my comparison between SQL Server Integration Services and ADF. In this post, I’ll walk through…