Design Pattern

TRY_CONVERT // Converting data to date, datetime, datetime2, time

by

Content Overview Probably the most challenging conversion is the conversion of a date and/or time, which is supplied as text, into a value of type date, time, datetime or datetime2. SQL Server provides the following data types, among others, for storing a date and/or time: To convert a date supplied as text, the CONVERT and … Read more

TRY_CONVERT // Converting data to float, real

by

Content Overview SQL Server provides the following data types for storing floating point numbers: The data types float and real are not precise data types. They are suitable for storing particularly large or small values, but at the expense of accuracy. This means that a number equal to 0 can apparently be stored, but which … Read more

TRY_CONVERT // Converting data to money, smallmoney

by

SQL Server provides the following data types for storing currency values: The data type money is essentially identical to the data type decimal(19, 4) and the data type smallmoney to the data type decimal(10, 4). There are internal differences in how SQL Server stores a decimal value and a money/smallmoney value. However, these differences are … Read more

TRY_CONVERT // Converting data to bigint, int, smallint, tinyint

by

This article describes how to safely convert input values to bigint, int, smallint and tinyint data types. A seemingly simple task, one would think. For example, what happens if you want to convert a decimal number or an empty string to a value of type int. Is the converted value automatically rounded? Does function return … Read more

TRY_CONVERT // Converting data to decimal, numeric

by

This article describes how to safely convert input values to bigint, int, smallint and tinyint data types. A seemingly simple task, you might think. For example, what happens if you want to convert a decimal number or an empty string to a value of type int. Is the converted value automatically rounded? Does function return … Read more

Design Pattern // Logging an ETL process with T-SQL

by

How can you judge whether an ETL process has been successful? The mere fact that a process was not terminated with an exception does not necessarily mean that the process did what was expected of it. Readable and evaluable logging of an ETL process can help here. It enables a reliable assessment of the success of an ETL process.

This article presents a design pattern for logging an ETL process based on stored procedures that insert and update log records in log tables. The aim is to create a readable and evaluable protocol that provides answers to essential questions about the execution of an ETL process:

  • How long does the ETL process take in total?
  • How long does a stored procedure, SSIS package, or other component take to execute?
  • How long does it take to execute a specific SQL statement?
  • How many records were processed by an SQL statement?

Of course, we also want to know whether the process as a whole, a procedure or even a specific SQL statement was executed successfully.

The procedure is actually straightforward: At the beginning of each action, a log data record is written and after the action is completed, it is updated with either the status success or failure and, if necessary, other helpful information. That’s it! But it can still be a little more…

Read more