Scenario 1

We have a source table as follows:

We have to move the into the target tables such that one

MY SOURCE		MY TARGET
SNo Name 1 A 2 B 1 C 3 D 4 F 1 E 2 G		SNo Name 1 A 2 B 1 C 3 D 4 F 1 E 2 G

After importing Source and Target, create a Sorter transformation to sort the data.
Then create an Expression transformation with 3 ports named say v_Name, v_SNo (2 variable ports) and o_Name (output port).
Give an expression as follows
v_Name -> IIF(SNo = v_SNo, v_Name||Name, Name)
v_SNO -> SNo
o_Name -> v_Name
Finally connect the ports to Target, create and execute the session and workflow.

Sequence Generator transformation:

• Sequence Generator transformation is Passive and Connected Transformation.
• The Sequence Generator transformation generates numeric values. Use the Sequence Generator to create unique primary key values, replace missing primary keys, or cycle through a sequential range of numbers.
• We use it to generate Surrogate Key in Data Ware House environment mostly. When we want to maintain history, then we need a key other than Primary Key to uniquely identify the record. So we create a Sequence 1,2,3,4 and so on. We use this sequence as the key. 
• For example: If EMPNO is the key, we can keep only one record in target and can’t maintain history. So we use Surrogate key as Primary key and not EMPNO.

Sequence Generator Ports:

• The Sequence Generator transformation provides two output ports: NEXTVAL and CURRVAL.
• We cannot edit or delete these ports.
• Likewise, we cannot add ports to the transformation.
• NEXTVAL: Use the NEXTVAL port to generate sequence numbers by connecting it to a transformation or target.
• For example, we might connect NEXTVAL to two target tables in a mapping to generate unique primary key values.
• CURRVAL: CURRVAL is NEXTVAL plus the Increment By value.
• We typically only connect the CURRVAL port when the NEXTVAL port is already connected to a downstream transformation.
• If we connect the CURRVAL port without connecting the NEXTVAL port, the Integration Service passes a constant value for each row.
• When we connect the CURRVAL port in a Sequence Generator transformation, the Integration Service processes one row in each block. We can optimize performance by connecting only the NEXTVAL port in a mapping.

Points to remember:

• If Current value is 1 and end value 10, no cycle option. There are 17 records in source. In this case session will fail.
• If we connect just CURR_VAL only, the value will be same for all records.
• If Current value is 1 and end value 10, cycle option there. Start value is 0. There are 17 records in source. Sequence: 1 2 – 10. 0 1 2 3 –
• To make above sequence as 1-10 1-20, give Start Value as 1. Start value is used along with Cycle option only.
• If Current value is 1 and end value 10, cycle option there. Start value is 1. There are 17 records in source. Session runs. 1-10 1-7. 7 will be saved in repository. If we run session again, sequence will start from 8.
• Use reset option if you want to start sequence from CURR_VAL every time.

Stored Procedure transformation

1. Stored Procedure transformation is a passive transformation. Stored Procedure transformation is a Connected and Unconnected Transformation
2. Stored procedures are stored and run within the database.
3. A Stored Procedure transformation is an important tool for populating and maintaining databases. Database administrators create stored procedures to automate tasks that are too complicated for standard SQL statements.

Use of Stored Procedure in mapping:

1. Check the status of a target database before loading data into it.
2. Determine if enough space exists in a database.
3. Perform a specialized calculation.
4. Drop and recreate indexes. Mostly used for this in projects.

Data Passes Between IS and Stored Procedure

• One of the most useful features of stored procedures is the ability to send data to the stored procedure, and receive data from the stored procedure. There are three types of data that pass between the Integration Service and the stored procedure:
• Input/output parameters: Parameters we give as input and the parameters returned from Stored Procedure.
• Return values: Value returned by Stored Procedure if any.
• Status codes: Status codes provide error handling for the IS during a workflow. The stored procedure issues a status code that notifies whether or not the stored procedure completed successfully. We cannot see this value. The IS uses it to determine whether to continue running the session or stop.

Specifying when the Stored Procedure Runs

• Normal: The stored procedure runs where the transformation exists in the mapping on a row-by-row basis. We pass some input to procedure and it returns some calculated values. Connected stored procedures run only in normal mode.
• Pre-load of the Source: Before the session retrieves data from the source, the stored procedure runs. This is useful for verifying the existence of tables or performing joins of data in a temporary table.
• Post-load of the Source: After the session retrieves data from the source, the stored procedure runs. This is useful for removing temporary tables.
• Pre-load of the Target: Before the session sends data to the target, the stored procedure runs. This is useful for dropping indexes or disabling constraints.
• Post-load of the Target: After the session sends data to the target, the stored procedure runs. This is useful for re-creating indexes on the database.

Using a Stored Procedure in a Mapping

1. Create the stored procedure in the database.
2. Import or create the Stored Procedure transformation.
3. Determine whether to use the transformation as connected or unconnected.
4. If connected, map the appropriate input and output ports.
5. If unconnected, either configure the stored procedure to run pre- or post-session, or configure it to run from an expression in another transformation.
6. Configure the session.

Findind sum of the salary

1. Drag source and target tables into Mapping Designer.
2. Design the mapping as shown below.

3. Connect all ports from Source Qualifier transformation to Expression transformation.
4. Create an output variable in Expression transformation as shown below:
• o_Sum_Sal = CUME(sal)
5. Connect all ports fromm Expression transformation to target.