SSIS String Replicate

The SSIS REPLICATE function will replicate or duplicate a string or character expression to the given number of times mentioned in second argument. This article explains how to use the REPLICATE function with an example and the syntax for duplicating the string to multiple times is as shown below.

REPLICATE(<<Character_expression>>, <<times>>)

To demonstrate the SSIS String REPLICATE 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 String REPLICATE function

For this string replication, 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 to write REPLICATE 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 string REPLICATE function to the row cell under the Expression and replace the Character_expression with the string column (Occupation) ad set the times to 2 to replicate the Occupation multiple times. Either you can manually delete the complete expression or drag and drop the Occupation column will automatically replace it.

REPLICATE( "Hi", 3 ) - Repeat Hi for three times.

REPLICATE( [Occupation], 2 ) - Repeat employee occupation for two times.
SSIS String Replicate 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 the Enable Data Viewer. Please run the SSIS string REPLICATE function package to see the Hi word and Occupation repeated multiple times.

SSIS String Replicate function output