SQL Server SSIS Sample Component: ConfigureUnDouble

Brief Description

ConfigureUnDouble takes a text column, and, removes bracketing quotes if present, plus places double quotes inside the text with sinqle quotes.

Overview

ConfigureUnDouble is an SSIS dataflow component that:

- Allows user to configure the component with a chosen quote character.
- Removes any such quotes that bracket input strings if present.
- Replaces doubled quote characters with single instances inside the string.

The SSIS flat file adapter doesn't process qualified strings where double quotes are used to indicate a single embedded quote.

This transform takes a text column, and, removes bracketing quotes if present, plus places double quotes inside the text with sinqle quotes.

SSIS users that want to duplicate DTS 2000 flat file processing can call their output column unqualified, then place this transform to clear up the quotes.

Configure the quote character by, on the first tab of the advanced editor, setting the "Qualifier" custom property to the desired value.

The conversion is performed in-place. Select the desired string column on the "Input Columns Tab" of the advanced editor, then change its Usage Type to READWRITE.

Included in this install is 4columns.txt, a file that contains a variety of quote/character combinations. Use a flat file source to feed this data to the ConfigureUnDouble component.

This component is part of a series of components that illustrate increasingly complex behavior, each one exercising a greater proportion of the SSIS object model. If studying in order, this component follows RTrimPlus, and precedes UnpackDecimal.

This component was built to provide an introduction to the use of the custom properties in component configuration. Also illustrated are:

- Use ProvideComponentProperties to initially add a custom property
- Validating custom properties
- Retrieving custom properties in PreExecute

Top of page