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

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
"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



Buy Now Free Trial Download Compare Editions More Features

Task Factory Replace Unwanted Characters Transform

The replace unwanted characters transform allows the user to remove unwanted characters or words from source data

Feature Highlights

  • Two options available to be used to replace unwanted characters
    1. Predefined set of characters (alpha, numeric, alpha numeric)
    2. Custom characters or words to be removed
  • The replaced data can be outputted to the same column in the transform or a new output column.
SSIS Replace Unwanted Characters Transform

SSIS Replace Characters Transform- Step 1
The replace unwanted characters transform is a flexible component that is easy as typing in the characters or words you want to remove from your source data and having those characters replaced. You have two options of replacing unwanted characters that are explained below.

The replace unwanted characters transform is a flexible component that is easy as typing in the characters or words you want to remove from your source data and having those characters replaced. You have two options of replacing unwanted characters that are explained below.

SSIS Replace Characters Transform- Step 2

Actions

To start using the Replace Characters transform you need to select an Action from the "Choose Action" column.

There are two actions that can be used with the Replace Characters Transform:

1) Replace Alpha/AlphaNumeric/Numeric characters and replace with user defined value - This option gives you 6 predefined options to replace characters. This option is useful if you need a simple way to remove unwanted characters without knowing the specific characters you want to remove but know what you want to keep. For instance, only keeping numbers in a phone number and removing everything else.

Parameters

 Action

a) ReplaceAlpha : Remove/Replace letters in the alphabet [a-z and A-Z] from the input string (Example: ACE-800#23 will be replaced with -800#23)

b) ReplaceNonAlpha : Remove/Replace any thing other than letters in the alphabet from the input string (Example: ACE-800#23 will be replaced with ACE)

c) ReplaceNumeric : Remove/Replace numbers [e.g. 0-9] from the input string (Example: ACE-800#23 will be replaced with ACE-#)

d) ReplaceNonNumeric : Remove/Replace anything other than numbers from the input string (Example: ACE-800#23 will be replaced with 80023)

e) ReplaceAlphaNumeric : Remove/Replace any charactsrs other than [A-Z],[a-z] and [0-9] from the input string (Example: ACE-800#23 will be replaced with -#)

f) ReplaceNonAlphaNumeric : Remove/Replace any charactsrs other than [A-Z],[a-z] and [0-9] from the input string (Example: ACE-800#23 will be replaced with ACE80023)

 Replace Match Character With  The character or word to replace the characters defined in the action


Actions

To start using the Replace Characters transform you need to select an Action from the "Choose Action" column.

There are two actions that can be used with the Replace Characters Transform:

1) Replace Alpha/AlphaNumeric/Numeric characters and replace with user defined value - This option gives you 6 predefined options to replace characters. This option is useful if you need a simple way to remove unwanted characters without knowing the specific characters you want to remove but know what you want to keep. For instance, only keeping numbers in a phone number and removing everything else.

Parameters

 Action

a) ReplaceAlpha : Remove/Replace letters in the alphabet [a-z and A-Z] from the input string (Example: ACE-800#23 will be replaced with -800#23)

b) ReplaceNonAlpha : Remove/Replace any thing other than letters in the alphabet from the input string (Example: ACE-800#23 will be replaced with ACE)

c) ReplaceNumeric : Remove/Replace numbers [e.g. 0-9] from the input string (Example: ACE-800#23 will be replaced with ACE-#)

d) ReplaceNonNumeric : Remove/Replace anything other than numbers from the input string (Example: ACE-800#23 will be replaced with 80023)

e) ReplaceAlphaNumeric : Remove/Replace any charactsrs other than [A-Z],[a-z] and [0-9] from the input string (Example: ACE-800#23 will be replaced with -#)

f) ReplaceNonAlphaNumeric : Remove/Replace any charactsrs other than [A-Z],[a-z] and [0-9] from the input string (Example: ACE-800#23 will be replaced with ACE80023)

 Replace Match Character With  The character or word to replace the characters defined in the action


SSIS Replace Characters Transform- Step 3

2) Replace specified characters or words with user defined value. - This options give you the ability to replace any characters or words from the input source with a user defined value. This is useful when you need to be more selective with the characters that will be replaced. For instance if you need to replace characters in a large amount of data without remove the newline characters.

Parameters

 Characters to be searched  Define what characters should be replaced. Certain characters will need to be entered as escape sequence (e.g. Tab [\t], CarriageRerurn [\r], LineFeed [\n], VerticalTab [\v], SingleQuote [\'], DoubleQuotes [\"], NullCharacter [\0]). 
 Words to be searched

 Define words or unicode characters to be replaced. All words must be seperated by vertical bar { | }. Escape characters can be used in this parameter as well. Example: If you want to replace words "joe" "bob" or new line characters then enter -> joe|bob|\r\n 

Unicode Characters: If you have some unicode characters you want to replace then specify \u (e.g. to replace greek letter specify \u03B2 in your word list)

Reference: http://en.wikipedia.org/wiki/List_of_Unicode_characters 

 Replace with  Define the character(s) or word(s) to replace the unwanted characters or words defined in the "Characters to be searched" and "Words to be searched" parameters. Usually this value is left blank.
 Ignore Case  Choose whether the words or characters being searched should use case matching. The default is false.
 Maximum Replacements  Specify how many occurances you want to replace. Setting this value to -1 will replace all occurances in the input string

2) Replace specified characters or words with user defined value. - This options give you the ability to replace any characters or words from the input source with a user defined value. This is useful when you need to be more selective with the characters that will be replaced. For instance if you need to replace characters in a large amount of data without remove the newline characters.

Parameters

 Characters to be searched  Define what characters should be replaced. Certain characters will need to be entered as escape sequence (e.g. Tab [\t], CarriageRerurn [\r], LineFeed [\n], VerticalTab [\v], SingleQuote [\'], DoubleQuotes [\"], NullCharacter [\0]). 
 Words to be searched

 Define words or unicode characters to be replaced. All words must be seperated by vertical bar { | }. Escape characters can be used in this parameter as well. Example: If you want to replace words "joe" "bob" or new line characters then enter -> joe|bob|\r\n 

Unicode Characters: If you have some unicode characters you want to replace then specify \u (e.g. to replace greek letter specify \u03B2 in your word list)

Reference: http://en.wikipedia.org/wiki/List_of_Unicode_characters 

 Replace with  Define the character(s) or word(s) to replace the unwanted characters or words defined in the "Characters to be searched" and "Words to be searched" parameters. Usually this value is left blank.
 Ignore Case  Choose whether the words or characters being searched should use case matching. The default is false.
 Maximum Replacements  Specify how many occurances you want to replace. Setting this value to -1 will replace all occurances in the input string

SSIS Replace Characters Transform- Step 4

Output Actions

The Replace Unwanted Characters transforms has the option of either replacing the data from a source column and outputting the replaced data into the same column or creating a new output column.

Output Actions

The Replace Unwanted Characters transforms has the option of either replacing the data from a source column and outputting the replaced data into the same column or creating a new output column.

SSIS Replace Characters Transform- Step 5
If "Output the fixed data to a new output column" is chosen, the new output column name will be "inputcolumn_output". For instance, if the source column is "FirstName", the output column will be "FirstName_output".

If "Output the fixed data to a new output column" is chosen, the new output column name will be "inputcolumn_output". For instance, if the source column is "FirstName", the output column will be "FirstName_output".

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