SQL & Databases

Data quality in SQL Server // TRY_CONVERT for money and smallmoney done safely

by

Anyone who has imported a point-of-sale report with values like ‘1.234,56 €’ from a CSV into a SQL Server database knows the pattern: TRY_CONVERT(money, ‘1,234.56’) yields 1234.5600. Yet TRY_CONVERT(money, ‘1.234,56’) yields NULL. And even when the import runs cleanly: money / 100 * 100 is not necessarily the same as the input value. At a glance — what this article covers: Prerequisite: SQL Server 2017+ (TRIM is used … Read more

Data Quality in an ETL Process — Catching Technical and Business Errors Before They Reach the Target System

by

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 in SQL Server // TRY_CONVERT for bigint, int, smallint and tinyint done safely

by

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

Data quality in SQL Server // TRY_CONVERT for decimal and numeric done safely

by

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

Design Pattern // Logging an ETL Process with T-SQL — How to Capture Run, Component and Action in Evaluable Log Tables

by

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

SSIS vs. SQL: Readability and Maintainability — how much SQL belongs in an SSIS package?

by

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

SSIS vs. SQL: Source Code Management — Why SP Diffs Are Readable and `.dtsx` Diffs Are Not

by

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

SSIS vs. SQL: Impersonation — How an Agent Job Gets to the Resources It Needs

by

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 vs. SQL — When to Use SSIS, When Pure T-SQL, When to Combine?

by

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

Commenting Complex SQL Statements — Parallel Inline Documentation That Keeps Code Readable

by

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