When designing the ETL architecture for new or changing systems, one of the more common needs is to create a process that copies all of the data from a set of tables (perhaps even all tables) from a source system to a destination database. This is especially prevalent when building an operation data store (ODS), or building a set of…
It’s that time of the year again – the Festivus season is upon us! In the next few weeks, families all over the world will be gathering around the Festivus pole while they wait for the traditional Festivus meat loaf to finish cooking. Afterward, they get some things off of their chests during the airing of grievances, followed by the…
Earlier this week I wrote about the basics of change tracking in SQL Server, and showed how to get started using this technology for change detection. In this post, I’ll continue what I started by demonstrating how change tracking fits into a larger design pattern for end-to-end incremental load ETL processes. Incremental Load Overview ETL processes fall into one of…
Redundancy is sometimes a good thing. However, redundant code usually isn’t. Creating multiple copies of the same coding logic – whether it’s a C# script, T-SQL procedure, SSIS package, or otherwise – leads to inefficiencies in maintaining the same code base in several locations. This design pattern tends to lead to a technical debt that is extremely expensive to repay….
So the question came up earlier today about the RIGHT() and LEFT() functions in the SSIS expression language. Like the Transact-SQL functions, one might assume that these functions would exist in SSIS expression language to snatch a specified subset of a string. That assumption would be only half right. Don’t go digging for a LEFT() function in the expression language,…