In SSIS, there is a LEN function to find and return the string length or total number of characters in a string column or character expression. This article explains how to use the LEN function and the syntax for find the string length is shown below.
LEN(<<Character_expression>>)
To demonstrate the SSIS LEN function to find the string length, we use the Employee table, which has 15 records. Please refer to the Union All Transformation, Derived Column Transformation, Built-in Functions, and SQL Server articles in SSIS.
SSIS LEN function to find string length
For this LEN function, 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 LEN function expressions and create new columns.
Next, double-click on it to open the Derived Column Transformation Editor. Drag and drop the SSIS LEN function to the row cell under the Expression and replace the Character_expression with the string column (Name, Education, and Email) to find the length. Either you can manually delete the complete expression or drag and drop the column will automatically replace it.
The below expression returns the total number of characters in the name, education, and email address columns.
LEN( [Name] )
LEN( [Education] )
LEN( [Email Adress] )
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 LEN function package to see the total number of characters in the string name, education, and email address length.
