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: Prerequisite: TRY_CONVERT has existed since SQL Server 2012. The safe pattern … 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

A CSV import runs through without a single error message, and afterwards the quantity column shows a 0 where the source field was simply empty: TRY_CONVERT(int, N”) returns 0, not NULL. The second quirk affects already typed decimal numbers: TRY_CONVERT(int, 1234.5) does not round but cuts off — the result is 1234, not 1235. At a glance: Prerequisite: TRY_CONVERT has existed since SQL Server 2012. The safe … 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: TRY_CONVERT has existed since SQL Server … 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 components in the data flow, 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. In this … Read more

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

by

Anyone diffing two versions of an SSIS package sees change markers scattered across the XML even for a trivial rename — eight “changed regions” in this article’s example, and the diff doesn’t even attribute the rename to the right task. The same modification in a stored procedure shows a three-line diff and is reviewable in … 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 a CSV file from a file share fails with Access is denied — the agent’s service account has no permission on that share. Instead of piling ever more rights onto the service account, the job step switches identity: impersonation at runtime, configured in SQL Server Agent through a proxy. One limit … 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