TRANSFORMATION 1

1. Transformation
   

   A transformation is a repository object that generates, modifies or passes data.
   

   
    

2. Active transformation
   

   
    

   à
   Change the number of rows that pass through the transformation. For example, the Filter transformation is active because it removes the rows that do not meet the filter condition.
   

   
    

   à
   Change the transaction boundary. For example, Transaction control transformation is an active because it defines a commit or roll back transaction based on an expression evaluated for each row.
   

   
    

   à
   Change the row type. For example, Update strategy transformation is active because it flags rows for insert, update, delete or reject.
   

   
    

Notes:

        à All multi-group transformations are active because they might change the number of rows that pass through the transformation.

 

        à We cannot connect multiple active transformations to single input group active transformation. For example, SQ_emp, SQ_dept à Filter is not possible.

    

        à We cannot connect multiple active transformations to single input group passive transformation, because the Integration service may not be able to concatenate the rows passed by active transformation. For example, SQ_emp, exp_dept à Exp.

 

        à The sequence generator transformation is an exception to this rule. We can connect SG transformation and an active transformation to single input group active or passive transformation because SG does not receives input data, it generates unique numeric value.

 

1. Passive transformation
   

 

A passive transformation does not change the number of rows that pass through it, maintains the transaction boundary and transaction type.

 

We can connect two passive transformations to the same downstream active or passive transformation.

 

1. Unconnected Transformation
   

 

An unconnected transformation is not connected to other transformation in the mapping. It can be called within another transformation and returns a value to that transformation.

 

 

 

 

 

1. Application Source Qualifier
   

 

It is an active transformation represents the rows that the IS reads from an application such as ERP.

 

1. Active: Normalizer, XML generator, XML parser, XML SQ, US
   

   Passive: Data masking, External procedure, HTTP, Lookup, Input, output, Stored procedure.
   

   Active/passive: Custom, Java, SQL, Unstructured data.
   

   
    

2. Multi-group transformations
   

 

Joiner: Contains two input groups, the master source and detail source, and one output group.

Router: Contains one input group and multiple output groups.

Union: Contains multiple input groups and one input group.

Custom: Contains any number of input and output groups.

 

1. Blocking Transformation
   

   
    

   A blocking transformation is a multiple input group transformation that blocks incoming data. IS block data at input group while the IS waits for a row from different input group.
   

   
    

   à Joiner transformation configured for unsorted input.
   

   à Custom transformation with the Inputs May Block property enabled.
   

 

1. Comment indicator: -- or //
   

 

 

1. Local variables
   

 

We can use local variables to stored data temporarily, to store values from prior rows, to capture multiple return values from stored procedure, to store the result of an unconnected lookup.

 

We can use local variables in Aggregator, Expression and Rank transformations.

 

1. Order of evaluation (Port Order)
   

   
    

   Input ports, Variable ports, Output ports.
   

   
    

   Input ports: The IS evaluates all input ports first since they do not depend on any other ports. Therefore we can create input ports in any order.
   

   
    

   Variable ports: Variable ports can reference input ports and variable ports, but not output ports. The IS evaluates variable ports after input ports.
   

   
    

   
    

   Output ports: Output ports can reference input ports and variable ports. The IS evaluates output ports last.