Task Factory
"I have found Task Factory to be a huge time saver with SSIS development. The SalesForce components alone saved close to two months of work on a major integration project. I highly recommend!"
Alan Rubel
Database Administrator
Joint Commission Resources
Task Factory Loews Corporation
"We bought the product mostly because of the company's reputation for the service they provide and the specific need of a secure SFTP task."

Matt Cushing

Application Systems Analyst/Developer

Loews Corporation

Greater New York City Area

Task Factory - Jay Cole
"I purchase Task Factory over a year ago, and let me tell you, IT WORKS FLAWLESSLY. I haven’t had a single problem with it, and I used to HATE having to make changes with the version you’re using. My advice (for what it’s worth) is to pay the money!"

Jay Cole

Task Factory - Urban Outfitters
"We have found that Task Factory tremendously speeds up the development process and improves performance by means of the high-performance components."

Tim Harris

BI Data Engineer

Urban Outfitters Inc.

Task Factory - Mark Marinovic
"We bought Task Factory primarily for the SCD Type 2 task, as it enabled us to do several things the built-in SSIS SCD task did not do. Task Factory comes highly recommended from me as our ROI was reached in just days."

Mark Marinovic



Buy Now Free Trial Download Compare Editions More Features

Task Factory Data Validation Transform

Acts as a gatekeeper for your data. Many companies receive questionable data from various sources, causing a myriad of production problems and inconsistent reporting. The Data Validation Transform verifies that your data is clean prior to insertion. Rows that are suspicious are flagged for later removal or additional business rules.

Feature Highlights

  • Validates data from a source with the use of regular expressions 
  • Validates Email, Phone number, SSN and many other patterns
  • Regular expression editor included as part of the UI
  • Outputs the results of the data check in each row of the transform output.
  • Easy to use User Interface
Data Validation Transform

Data Validation Transform - Step 1

UI Overview

There are two tabs "Input Columns / Expressions" and "Options"

Input Columns / Expressions


The Grid contains a row for each column from the input source. The grid columns are defined as:

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.


UI Overview

There are two tabs "Input Columns / Expressions" and "Options"

Input Columns / Expressions


The Grid contains a row for each column from the input source. The grid columns are defined as:

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.


Data Validation Transform - Step 2
Options

The options tab contains settings for the regular expressions.

Options

The options tab contains settings for the regular expressions.

Data Validation Transform - Step 3

Adding / Editing / Deleting Validation

To add validation to a column, click the + button in the row where the column is located. The Regular Expression editor will be shown.

Adding / Editing / Deleting Validation

To add validation to a column, click the + button in the row where the column is located. The Regular Expression editor will be shown.

Data Validation Transform - Step 4
To edit the validation of a column, click the ... button.

To edit the validation of a column, click the ... button.

Data Validation Transform - Step 5
To delete the validation from a column, click the X button.

To delete the validation from a column, click the X button.

Data Validation Transform - Step 6

Regular Expression Editor

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

Regular Expression Editor

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

Data Validation Transform - Step 7

Regular Expressions Library

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

Regular Expressions Library

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

Data Validation Transform - Step 8
Matches / Non-Matches


The textboxes show an examples of what data will match and won't the expression

Matches / Non-Matches


The textboxes show an examples of what data will match and won't the expression

Data Validation Transform - Step 9
Adding, Editing and Deleting Expressions

Adding a new expression:Click on the "Add New Expression" button

Adding, Editing and Deleting Expressions

Adding a new expression:Click on the "Add New Expression" button

Data Validation Transform - Step 10
The "Add New Expression / Edit Expression"window will be shown.

  • Expression Name: The friendly name that will be shown in the expression list
  • Expression: The regular expression that will be used to validate the data.
  • Match Examples: Give examples of what will match match the expression
  • Non-Match Examples: Give examples of what won't match the expression

The "Add New Expression / Edit Expression"window will be shown.

  • Expression Name: The friendly name that will be shown in the expression list
  • Expression: The regular expression that will be used to validate the data.
  • Match Examples: Give examples of what will match match the expression
  • Non-Match Examples: Give examples of what won't match the expression

Data Validation Transform - Step 11
Editing expressions
  • Click the "Edit Expression" button
  • The "Add New Expression / Edit Expression" window will show. Edit any text and click the OK button

Editing expressions
  • Click the "Edit Expression" button
  • The "Add New Expression / Edit Expression" window will show. Edit any text and click the OK button

Data Validation Transform - Step 12
Deleting expressions
  • Click the "Delete Expression" button to delete an expression from your library

Deleting expressions
  • Click the "Delete Expression" button to delete an expression from your library

Data Validation Transform - Step 13
Regular Expression Textbox
  • The regular expressions textbox shows the currently selected expression from the expressions list

Regular Expression Textbox
  • The regular expressions textbox shows the currently selected expression from the expressions list

Data Validation Transform - Step 14
Testing Expressions - To test the currently selected expression, add some text to the "Test Expression Text" textbox. Put each value you want to test on a new line. Click the "Test Expression" button to see the results. 

Values that match the expression will show up in green
Values that don't match the expression will show up in red.
Click the "Add Validation To Column" button when you're ready to add validation to the selected column.


Testing Expressions - To test the currently selected expression, add some text to the "Test Expression Text" textbox. Put each value you want to test on a new line. Click the "Test Expression" button to see the results. 

Values that match the expression will show up in green
Values that don't match the expression will show up in red.
Click the "Add Validation To Column" button when you're ready to add validation to the selected column.


Data Validation Transform - Step 15
Data Validation Transform Output

The output returned from the Data Validation Transform will include an extra column for each column that has validation added to it. The new column name will have "_IsValid" appended to the source column name. For instance if you added validation to the "EmailAddress" column from the source, the output will contain an extra column named "EmailAddress_IsValid". The data type of the added columns is DT_BOOL (boolean) and will set to True if the validation succeed and False if the validation fails.

Here is an example of output from a data validation transform object where the output was attached to a Flat File Destination.


Data Validation Transform Output

The output returned from the Data Validation Transform will include an extra column for each column that has validation added to it. The new column name will have "_IsValid" appended to the source column name. For instance if you added validation to the "EmailAddress" column from the source, the output will contain an extra column named "EmailAddress_IsValid". The data type of the added columns is DT_BOOL (boolean) and will set to True if the validation succeed and False if the validation fails.

Here is an example of output from a data validation transform object where the output was attached to a Flat File Destination.


Data Validation Transform - Step 16
Validation has been added to the EmailAddress column. There is an extra column named "EmailAddress_IsValid" and it's data type is Boolean.

Validation has been added to the EmailAddress column. There is an extra column named "EmailAddress_IsValid" and it's data type is Boolean.

Data Validation Transform - Step 17
Optimal Use of the Data Validation Transform

The optimal way to use the Data Validation Transform is to use a conditional split to handle valid and invalid data. Here is a screenshot of an example of what the data flow looks like using Data Validation Transform, Conditional Splits and Flat Files destinations.

Optimal Use of the Data Validation Transform

The optimal way to use the Data Validation Transform is to use a conditional split to handle valid and invalid data. Here is a screenshot of an example of what the data flow looks like using Data Validation Transform, Conditional Splits and Flat Files destinations.

Data Validation Transform - Step 18

This screenshot shows how the Conditional Split was setup.

When this package is run, any data that has an invalid value in the EmailAddress column will be directed to the "Invalid Data" flat file and the valid data will be directed to the "Valid Data" flat file.

This screenshot shows how the Conditional Split was setup.

When this package is run, any data that has an invalid value in the EmailAddress column will be directed to the "Invalid Data" flat file and the valid data will be directed to the "Valid Data" flat file.

View Our Suite of Pragmatic Works Products
Copyright 2014 by Pragmatic Works