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
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 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
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
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
Read a CSV file, transform the data, load the result into SQL Server: for the file part, SSIS is the obvious choice, for the transformation pure T-SQL. The best solution is often a combination of both. There is no single right way, but there are three decision criteria against which every concrete choice should be … Read more
