Data Validation

Data Validation

Parent Previous Next

Data Validation Transform

The "Data Validation Transform" will validate data from an SSIS source by using regular expressions defined in the UI.

 Column - Contains the column name of the input source column.

 Expression - This will contain the name of the expression used to validate the data in that source column if validation is added to it.

 "Buttons Column" - This column will contain the buttons used to Add, Edit, or Delete the expression. Edit and Delete are only available if validation has been added to the column

Options Tab

  Regular Expression Options

o  Ignore Case - This will prevent distinguishing between upper and lowercase string values

o  Ignore Whitespace - This will ignore unescaped whitespace

o  Culture Invariant - Specifies that cultural differences in language are ignored

o  Multiline - This changes the meaning of "^" and "$" so they match at the beginning and end of any line and not just the string

o  Singleline - Changes the meaning of "." so it matches every character

Adding / Editing / Deleting Validation

To add validation to a column, click the + button in the far right column of the Input Columns / Expressions tab. This will bring up the "Regular Expression" editor.

Regular Expression Editor UI Overview

The regular expression editor is used to create, edit and test regular expressions that can be used to validate your data.

  Expressions Library

o  Expressions List - The expressions list contains all of the expressions that are part of your library

o  Matches / Non-Matches - The text boxes show an example of what will or won't match the expression

 Regular Expression Textbox - this textbox shows the currently selected expression from the expressions list.

 Test Expression Text - Here you can test the currently selected expression by adding text. Put each value you want to test on a new line. Once done click "Test Expression" to see the results

Regular Expression Library Toolbar

There are three options on the toolbar:

- Add Expression

- Edit Expression

- Delete Expression

Clicking on Add or Edit Expression will bring up a similar window. Clicking Add Expression will bring up the Add Expression window, which is blank, while clicking the Edit Expression button will bring up the Edit Expression window, which has the same fields, just populated with the current rules.