A single value that cannot be converted — a date in the wrong format, a number with the wrong decimal separator — and the entire ETL run aborts. Data quality in an ETL process means catching such errors proactively: identifying, logging and isolating them before they reach the target system. This article is the entry point to … Read more
Data quality starts with type conversion — and for integer columns, TRY_CONVERT has a few quirks that tend to slip past the import path: an empty string becomes 0 (not NULL), any decimal or thousands separator yields NULL, and a typed decimal is silently truncated — without rounding. This article sorts out the rules, shows the safe conversion pattern, and adds a … Read more
Anyone who has watched a price import turn ‘123.45 €’ into a NULL instead of the expected decimal number knows the drill: TRY_CONVERT(decimal(5, 2), ‘123,45’) returns NULL, because a comma isn’t accepted as a decimal separator. And even with the comma gone, TRY_CONVERT(decimal(5, 2), ‘1234.56’) is also NULL — this time because of one integer digit too many. At a glance: Prerequisite: SQL Server 2017+ (TRIM in … Read more
An ETL process finishes without an exception — but was everything really loaded that should have been? The mere fact that a process did not abort says nothing about whether it actually did what was expected of it. A readable, evaluable log is what turns a gut feeling into a defensible statement. This design pattern … Read more
Three ways to model the same ETL task in SSIS. One takes 10 minutes and is straightforward. One takes hours, 40 Data Flow Tasks, and won’t survive the next requirements change. The question “how much SQL belongs in an SSIS package?” decides maintainability, readability, and development speed — not tool loyalty. What you’ll take away: … Read more
Diff two versions of an SSIS package — even a trivial rename produces eight “changed regions” in the XML, and the diff doesn’t even locate the actual edit correctly. The same modification in a stored procedure shows three lines of diff, reviewable in 30 seconds. Source code management is a maintainability decision — not a … Read more
A SQL Server Agent job that needs to read from a file share fails with Login failed — the agent’s service account has no permission there. The solution is impersonation at runtime, configured in SQL Server Agent through a proxy user. One dead end remains: plain Transact-SQL Script steps don’t support impersonation — the workaround is to wrap the T-SQL in an … Read more
SSIS, T-SQL, or a combination of both? There is no single right answer. What there is, is a handful of decision criteria against which every concrete choice — technology and extent of use — should be measured: readability, source code management, impersonation. This article series takes these three axes and delivers a concrete argument per axis. Anyone arguing ETL … Read more
Anyone who writes a 200-line SELECT with a recursive CTE understands it completely while writing it — and three weeks later, not a word of it. Inline comments are the safety net against that. The problem: placed badly, they destroy the very readability they are meant to preserve. What this article covers: Prerequisite: The examples run against AdventureWorksDW2017 (table [dbo].[DimEmployee], a … Read more
An SSIS package fails, you open the execution log to find the cause — and face a tree in which the tasks don’t appear in the order they ran, but alphabetically by name. For complex packages that log is simply unreadable. The good news: a well-thought-out naming convention solves the problem completely — without any … Read more
