Using the Text Merge Component

The Text Merge component is a transformation component used to merge column and variable data into a text template. Drag and drop columns or variables onto the editor to construct the template. When the template is complete, set test values for columns that are in the template and click the preview button to view an example of the merge.

General Page

Configure general settings by specifying columns and variables to merge into a text template.

List of Columns & Variables

Drag and drop columns or variables onto the editor. When the Text Merge component runs, it will replace all occurrences of columns and variables with their value. The merged text is sent to the output column specified by Output Column Name.

Test Value

Below the list of columns and variables there is a panel that displays information about the selected column or variable. When selecting a column you can specify the Test Value. When the Preview button is clicked, the editor uses the Test Values to perform a test merge and displays the result in the previewer.

Preview

Selecting Preview will launch a new window using the Test Values to perform a test merge and displays the result in the previewer.

Preview Mode

In the previewer, the preview mode option shows the text in its intended format. This option is strictly a design tool and does not affect runtime. The available preview modes are:

Plain Text

JSON

XML

Output Column Name

The name of the output column that will contain the merged text.

Expression fx Icon

Click the blue fx icon to launch SSIS Expression Editor to enable dynamic update of the property at run time.

Generate Documentation Icon

Click the Generate Documentation icon to generate a Word document which describes the component's metadata including relevant mapping, and so on.

Headers & Footers Page

Configure header and footer settings along with how to merge them with the body text

Merge Mode

Specify how to merge the specified Header and Footer text with the body text. There are three different Merge Modes:

Every Row - Prepends the Header text and appends the Footer text to every record. The merged text is directed to the Default Output with the column name of that of the Output Column Name setting found on the Output & Error Handling Page

Every Batch - Prepends the Header text and appends the Footer text every batch. The batch size is determined by the Batch Size option. The merged text is directed to the Merge Output with the column name of that of the Output Column Name setting found on the Output & Error Handling Page

Entire Buffer - Prepends the Header text and appends the Footer text to the entire buffer. The merged text is directed to the Merge Output with the column name of that of the Output Column Name setting found on the Output & Error Handling Page

Batch Size

Specifies how many records a merged record will contain when using the Every Batch Merge Mode.

Header

The text to use as the header when merging. Since it is not guaranteed to process at the record level it may not contain column or variable tokens.

Footer

The text to use as the footer when merging. Since it is not guaranteed to process at the record level it may not contain column or variable tokens.

Error Handling Page

The Error Handling page allows you to specify how errors should be handled when they happen.

There are three options available.

Fail on error

Redirect rows to error output

Ignore error

When the Redirect rows to error output option is selected, rows that failed to be sent will be redirected to the 'Error Output' output of the Transformation Component. As indicated in the screenshot below, the blue output connection represent rows that were successfully sent, and the red 'Error Output' connection represents rows that were erroneous. The 'ErrorMessage' output column found in the 'Error Output' may contain the error message that was reported by server or the component itself.