In SSIS, there is a built-in LOWER function to convert the given Uppercase string or character expression to Lowercase characters. This article explains how to use the LOWER function with an example and the syntax for the conversion of uppercase strings to Lowercase characters is as shown below.
LOWER(<<Character_expression>>)
To demonstrate the SSIS LOWER function to convert string to lowercase, we use the Employee table below, which has 15 records. Here, we changed the Name and Occupation to Uppercase by altering the query.
SSIS LOWER function to convert String to Lowercase
For this LOWER 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 LOWER expressions and create new columns.
Next, double-click on it to open the Derived Column Transformation Editor. Drag and drop the SSIS LOWER function to the row cell under the Expression and replace the Character_expression with the string column name for converting to lowercase. Either you can manually delete the complete <<Character_expression>> expression or drag and drop the Name column will automatically replace it.
- LOWER(Name) – Convert the uppercase characters in the Name field to the Lowercase string.
- LOWER(Occupation) – Convert the uppercase characters in the Occupation column to Lowercase.
Click OK to close the Editor window. Please refer to the Union All Transformation, Derived Column Transformation, Built-in Functions, and SQL Server articles in SSIS.
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 LOWER function package to see the Uppercase characters in Name and Occupation converted to Lowercase string.
