SSIS CODEPOINT function

The SSIS CODEPOINT is a string function to find and return the Unicode value of the leftmost character in a string or character expression. This article explains how to use the CODEPOINT function with an example and the syntax for finding the Unicode value of a character is shown below.

CODEPOINT(<<Character_expression>>)

To demonstrate the SSIS CODEPOINT 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.

Source Table

SSIS CODEPOINT function to find Unicode Value

For this, let me 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 because it helps to write CODEPOINT expressions and create new columns.

Data Flow to add Derived Column

Next, double-click on it to open the Derived Column Transformation Editor. Drag and drop the SSIS CODEPOINT function to the row cell under the Expression and replace the Character_expression with the string column for Unicode values. Either you can manually delete the complete <<character_expression>> or drag and drop the Name column will automatically replace it.

  • CODEPOINT(Name) – The Unicode value of the first character in each employee’s Name.
  • CODEPOINT(Occupation) – Unicode value of the first character in each employee Occupation.
  • CODEPOINT(Email Address) – The Unicode value of the first character in each employee’s Email.
SSIS CODEPOINT Function Expression

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 Enable Data Viewer. Please run the SSIS CODEPOINT Function package to see the Unicode values of the leftmost characters in the Name, Occupation, and Email address.

SSIS CODEPOINT Function package output