The SSIS HEX is a string function that returns a hexadecimal value of the given integer or numeric column. This article explains how to use the HEX function with an example and the syntax for finding the hexadecimal value of an integer is as shown below.
HEX(<<integer_expression>>)
To demonstrate the HEX function, we use the Employee table below, which has 15 records. Please refer to the Union All Transformation, Derived Column Transformation, Built-in Functions, and SQL Server articles in SSIS.
SSIS HEX function
For this HEX function example, add a Data Flow Task and double-click on it to enter the Data Flow region. Next, add the OLE DB Source to read data from the above SQL database table. Please join the OLE DB Source to Derived Column Transformation because it helps to write HEX expressions and create new columns.
Next, double-click on it to open the Derived Column Transformation Editor. Drag and drop the SSIS HEX function to the row cell under the Expression and replace the integer_expression with the Phone column to find the hexadecimal value.
Here, we used the LEFT function to get the leftmost two digits in a phone number. Next, convert it to an unsigned integer using DT_UI8. Similarly, we divided the income by 1000 to get the first two digits and which is the currency datatype, changed it to the unsigned integer.
HEX((DT_UI8)LEFT(Phone, 2))
HEX((DT_UI8) (YearlyIncome / 1000))
Click OK to close the Editor window.
Drag and drop the Union All Transformation and connect the Derived Column to it. Next, right-click on the connection node and choose the Enable Data Viewer. Please run the SSIS HEX function package to see the hexadecimal values of the phone number and yearly income.
