BI - Integration Services

Description of the SSIS Toolbox Items

Script Task

Just do a simple task and you can code some actions like evaluate some variables, also you can evaluate two Script task with AND or OR conditional.

File System Task
Can do all the actions to interact with a FTP.

Execute SQL 
You can execute a sql sentence, also you can put the result in a variable or variables.

Execute Process Task
You can execute an external program like a *.bat where you can send it some parameters

Expression Task
Evaluate variables with many functions like Round, Sum, Date functions, etc. You can use it in a For Loop Container.

Send Mail Task
As the name say, you send an email with all the attributes you can imagen.

Data Flow
The Data Flow Task is used to transfer data from a source to a destination and can transform the data as needed.

Data Conversion
Transfor the data type, usefull in case of non-unicode to unicode

Devived Column
 create or replace a column in the data stream

Aggregate
Rolling up data, with these you can Groups by the information. This element is the one who use the most memoty in the process.

Sort
Sort data based on any column in the Data Flow path

Look up
Perform the equivalent of an inner and outer hash join. The only difference is that the operations occur outside the realm of the database engine.

Row Count
Count the row and store the value in a variable to be used later, for example in an expression that evaluate if is bigger than cero then continue.

Union All
Combines multiple inputs in the Data Flow into a single output rowset. It is very similar to the Merge Transform, but does not require the input data to be sorted.

Script Component
Can manipulate the data:

• Transform—Generally, the focus of your Data Flow will be on using the script as a transform. In this role, you can perform advanced cleansing with the out-of-the-box components.
• Source—When the script is used as a source, you can apply advanced business rules to your data as it’s being pulled out of the source system. (This happens sometimes with COBOL files.)
• Destination—When the script is used as a destination, you can use the script to write out to a non-OLE DB destination, like XML or SharePoint.

Conditional Split
The Conditional Split Transform uses the SSIS expression language to determine how the data pipeline should be split. For this example, all you need to know is that the Conditional Split Transform is checking to see if customers have more than five kids so they can receive the extra coupon.

OLE DB Commmand
Is used to run a SQL statement for each row in the Data Flow

Fuzzy Lookup
Gives other alternatives to dealing with dirty data while reducing your number of unmatched rows. The Fuzzy Lookup Transform matches input records with data that has already been cleansed in a reference table. It returns the match and can also indicate the quality of the match. This way you know the likelihood of the match being correct.
NOTE A best practice tip is to use the Fuzzy Lookup Transform only after trying a regular lookup on the field first. The Fuzzy Lookup Transform is a very expensive operation that builds specialized indexes of the input stream and the reference data for comparison purposes. Therefore, it is recommended to first use a regular Lookup Transform and then divert only those rows not matching to the Fuzzy Lookup Transform.

Fuzzy Grouping 
To examine the contents of suspect fields and provide groupings of similar words. You can use the matching information provided by this transform to clean up the table and eliminate redundancy.