A single date string that cannot be parsed, and the entire ETL run aborts. The design pattern for ETL process architecture presented here prevents exactly that: bad data is isolated, not passed along. TL;DR — what this article covers: Prerequisite. Basic familiarity with ETL processes. This is a conceptual article — not a step-by-step tutorial. The technology examples are … Read more
If you’ve ever imported a CSV column with mixed date formats into a datetime column, you know: Type conversion is where bad date values either surface or slip silently into the pipeline. SQL Server leaves you alone with style codes the moment the format strays from the documented ones — TRY_CONVERT covers the documented style formats, anything else needs a parsing strategy of your own. … Read more
Anyone who has ever taken over a yes/no column from a legacy export knows the pattern: the source delivers ‘J’, ‘ON’, or a plain ‘x’, and TRY_CONVERT(bit, N’J’) answers the German notation with NULL. Out of the box, SQL Server understands only integer strings and the literals ‘true’/’false’ at the bit target — every other yes/no notation needs an explicit mapping. At a glance: Prerequisite: TRY_CONVERT has … Read more
If you have ever imported a series of measurements where every empty cell landed in the target table as 0, you know the trap: TRY_CONVERT(float, ”) does not return NULL, it returns 0. The average across that column is wrong afterwards, and nothing about the result gives it away. At a glance: Prerequisite: TRY_CONVERT has existed since SQL Server 2012. The safe … Read more
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
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
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
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
