Validating Data Types in Expressions
When using character-based source connectors (ASCII, XML, spreadsheets) with strongly typed target connectors (databases, binary), it is important to include data validation functions in your mapping expressions.
You can interpret data typing errors that occur by using functions or error handing statements that halt your transformation. Without these measures, hard-to-interpret errors may occur, such as no data being returned to the target file or table.
Example
Source: ASCII (Delimited) with fields that contain date and number values
Target: SQL database with fields containing Date/Time and 16-bit Integer data types
In this case, check the source values to make sure they can successfully be represented in those formats. Here are two methods to do this:
• Use functions to test your data - Functions such as IsDate and IsNumeric can help determine whether or not character data can be represented as another data type.
• Set up an error handler - You can use the On Error Go To statement to setup an error handler. Next, use one of the explicit conversion functions, such as CBool, CByte, CDate, CDbl, CInt, CLng, CSng. Character data that cannot be represented in the target data type results in an expression error and is caught by the error handler. This makes it easier to understand why the conversion was not successful.
Best Practice — Always explicitly validate data when converting between two different data types, such as 32-bit integer to 16-bit integer, or when converting between different source/target connectors where data types have disparate maximum/minimum values.
Last modified date: 02/01/2024