Product Overview

Dimension Merge SCD

• Designed to handled most of the dimension loading scenarios (as per Kimball Method) in few clicks (e.g. Type1 SCD, Type2 SCD, inferred members)
• Supports very large dimensions (Several millions rows)
• 100x faster than native Microsoft SCD Component
• Cleaner and Non-destructive mechanism unlike native Microsoft SCD Component which destorys down stream components when you re-run MS SCD wizard.
• Inbuilt Surrogate Key management
• "Special" (unknown) member support, per Kimball Method best practices.
• Includes a "Row Change Reason" output column on all (except Unchanged) outputs, per Kimball Method best practices.
• Supports simple and advanced styles of Row Auditing for inserts and updates, per Kimball Method best practices.
• Flexible SCD 2 "current row" handling - permits specification of the date "endpoints". Comparatively, the only choice with the MS SCD Wizard is to have the "expiry" date be NULL to signify the current record.
• Flexible SCD 2 date handling - permits specification of what date expired and new rows get marked with. The SCD Wizard leaves that up to a Derived Column component that will get destroyed when the Wizard is run again to adjust other properties.
• Options to use other data types for the Current Member and Inferred Member columns - not just Boolean types as in the SCD Wizard.
• Supports "retiring" dimension members that have been deleted from the Source System by marking their "expiry date" with a real date.

SalesForce.com Source

• Easily retrieve the data stored in your salesforce.com account with a few simple clicks.
• SOQL (SalesForce query language) can be edited in the UI
• Query can use variables as parameters (e.g. where DateCreate = <@VarDateCreated>)
• *Must have an enterprise salesforce.com account to use this task. This is a salesforce.com requirement.

SalesForce.com Destination

• With this component you will be able to very easily add data from your system into your salesforce.com account with very little configuration.
• The easy to use UI allows mapping of columns from your data to columns in the salesforce.com
• *Must have an enterprise salesforce.com account to use this task. This is a salesforce.com requirement.

Sharepoint Source

• Easy to use UI allows for quick mapping of fields from local source data to to sharepoint list columns

Upsert Destination  (Batch Update Or Insert)

Secure FTP Task

• Used to interact with a secure ftp site
• Supports SSH, Implicit SFTP and Explicit SFTP
• Supports HTTP, SOCKS4, SOCKS4a and SOCKS5 proxies
• Supports all actions provided by native FTP task
• Easy to use User Interface

Compression Task (Zip or Unzip)

PGP Encryption Task

• File encryption - The PGP Task can encrypt a file that can be sent securely increasing the security available around transporting files through FTP and Email.
• File decryption - The PGP Task can be used to decrypt a file sent to your system.
• File signing - The PGP Task can sign a file to allow an end user / system to be able to verify the file being sent was actually created your system.
• File verification - The PGP Task can verify the signature of a file sent to your system from a client. This will allow you to verify the file actually was created by the client and not hacked by something else and sent to your system.

Address Parse Transform

• Used to parse unformatted address data into USPS standardized address data.
• Two outputs can be used to detect valid and invalid addresses
• Granular detailed data available about each address is parsed for use in the data flow.
• Address data elements include AddressLine1, AddressLine1, City, State, PostalCode, StreetNumber, StreetDirection and much more!

Email Source

Data Validation Transform

• 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

File Properties Task

• Used to read the properties of an existing file.
• Will read the properties like File Exists, File Size, File Name, File Content, Creation Time, Is File In Use, Hidden, ReadOnly and assign them to SSIS package level variables
• Easy to use User Interface

Surrogate Key Transform

• Adds an identifier column to a data flow used to uniquely identify a row. This is useful if your data source does not contain an identifier or you simply want to add a row counter to your data flow.
• Allows the use of a variable to determine seed and increment

• The delete batch transform allows you to easily delete data in a table without writing any sql statements and then continue on with the other tasks in the data flow.

Update Batch Transform

• The update batch transform allows you to easily update data in a table without writing any sql statements and then continue on with the other tasks in the data flow.

Advanced Email and SMS Task

• Sends emails from within an SSIS package through SMTP
• Inbuilt HTML editor for HTML email
• Supports sending SMS messages
• Template based email (template can be external file, variable, or direct input)
• Allows use of User/System Variables as placeholders in the email body so variables can be automatically replaced when email is sent

Terminator Destination

• This free component allows you to quickly add a destination to your package without any configuration.
• This is useful when developing your ssis packages and want to quickly add a destination without having to configure a flat file destination or any other destination that will eventually be deleted.

Data Cleansing Transform

• Used to cleanse data from a source by applying cleansing rules to the data.
• The Data Cleansing task has 16 built in algorithms that allow you to clean data without any programming or sql updates.

Replace Unwanted Characters Task (v2.0)

• 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.

Null Handler Transform  (v2.0)

• Three actions available to handle null or blanks values
  1. Convert NULL value to user defined value
  2. Convert blank value to NULL
  3. Convert blank value to user defined value
• Output replaced data to the same column in the transform or to a new output column.

RegEx Replace Transform  (v2.0)

• There are two actions that can be used with this transform.
  1. The first action is to replace regex matched data with a user defined value. This option allows for the use of capture groups to be part of the replace value. For instance if the regular expression was for a phone number it would look like something like “(\d{3})[-\)\.\s]?(\d{3})[-\)\.\s]?(\d{4})”. There are three capture groups in that regular expression and you could use $1, $2, $3 to extract the area code, prefix and line number.
  2. The second action allows the user to define a regular expression to extract data from the source column. There are several options when using this action including extracting capture groups, separating multiple values by a delimiter and providing a “no match” value.
• The replaced data can be outputted to the same column in the transform or a new output column.

Trim Plus Transform  (v2.0)

• Whitespace trim actions – Trims whitespace from column data
  1. Trim all trailing and leading whitespace characters
  2. Trim all trailing whitespace characters
  3. Trim all leading whitespace characters
• Specified character trim actions – Allows the user to defined the character(s) to be trimmed from the source column data
  1. Trim specified characters from start and end
  2. Trim specified characters from start
  3. Trim specified characters from end

Case Transform  (v2.0)

• SimpleProperCase – Capitalize the First Letter of Each Word (reference file is not used so its very fast but no symbol/word replacement is performed like other styles)
  1. Text_In : the cOw jumped Over the MOON
  2. Text_Out: The Cow Jumped Over The Moon


• Name – Standardize Prefixes & Suffixes
  1. Text_In : MR / MRS JOHN VAN DER CAMP PHD
  2. Text_Out: Mr and Mrs John Van der Camp, Ph. D.


• Address –Eliminate Conflict Between Street Suffix & City / State / Zip
  1. Text_In : 1234 SE 41ST CT APT 26E (CT is also the abbreviation for Connecticut)
  2. Text_Out: 1234 SE 41st Ct, Apt. 26E


• CSZ (City State Zip) – Avoid Confusion Between “Company” & “Colorado”
  1. Text_In : PINE, CO 80470
  2. Text_Out: Pine, CO 80470


• Title – Capitalize the First Letter of Each Word, Convert Symbols
  1. Text_In : (c) PRAGMATICWORKS, INCORPORATED
  2. Text_Out: © Pragmatic Works, Inc


• Sentence – Capitalize the First Letter of Each Sentence
  1. Text_In : TASKFACTORY(tm) IS A TRADEMARK OF PRAGMATICWORKS INC.
  2. Text_Out: TaskFactory™ is a trademark of Pragmatic Works Inc.


• UpperCase – Capitalize each letter in the column data
  1. Text_In : hi, this is task factory.
  2. Text_Out: HI, THIS IS TASK FACTORY.


• LowerCase – Lowercase each letter in the column data
  1. Text_In : Hi, This is Task Tactory
  2. Text_Out: hi, this is task factory.

Error Output Description Transform  (v2.0)

Advanced Execute Package Task  (v2.2)

• Can be used to execute child packages on a file system or within SQL Server
• Can be used with password protected packages
• Allows for variables to be mapped from the parent package to be used in the child package
• Allows for the reading of variables from child packages to be used in your package

Advanced Execute Process Task  (v2.2)

• Commands are executed as if you're using the command line
• Can be used to call basic command functions like del, copy, ren, etc.
• There can be multiple commands per instance of the Advanced Execute Process Task unlike the native SSIS execute process task
• Commands can use variable replacement without the use of expressions

Download File Task  (v2.2)

• Download files over HTTP without the use of FTP
• Uses native HTTP Connection Manager within SSIS
• Easy to use UI has you download files over HTTP in minutes