The regular expression transformation exposes the power of regular expression matching within the pipeline. One or more columns can be selected, and for each column an individual expression can be applied. If all columns selected pass their tests then rows are passed down the successful match output. Rows that fail to pass all tests are directed down the alternate output.

This transformation is ideal for validating data through the use of regular expressions. You can enter any expression you like, or select a pre-configured expression within the editor. You can expand the list of pre-configured expressions yourself. These are stored in a Xml file, %ProgramFiles%\Microsoft SQL Server\90\DTS\PipelineComponents\RegExTransform.xml.

If you want to use regular expressions to validate data then try the RegexClean Transformation.

The component is provided as an MSI file, however to complete the installation, you will have to add the transformation to the Visual Studio toolbox by hand. This process has been described in detail for the Trash Destination, just select Regular Expression Transformation in the Choose Toolbox Items dialog.

Version 1.0.0.76 - Installer update and improved exception handling.
(28 Jan 2008)

Version 1.0.0.41 - Update for user interface stability fixes.
(2 Aug 2006)

Version 1.0.0.24 - SQL Server 2005 RTM Refresh. SP1 Compatibility Testing.
(12 Jun 2006)

Version 1.0.0.9 - Public Release for SQL Server 2005 IDW 15 June CTP
(29 Aug 2005)