07 October, 2020
Start programsMicrosoft SQLserver click on enterprise manager
From left plane expand the console root, select the data base, folder right click, click on new data base.
Enter the database name batch4pm click ok
Start programs Microsoft SQLserver- click on query analyzer.
Connect to the SQL server to the with the following details
SQL server: Admin
Select SQL server: authentication
Login name: sa
Execute the following query in the query panel
USE BATCH 4 pm
Create table dept(deptno integer,dept name varchar(10),locvarchar(10));
Insert into dept values(10,’sales’,’texas’)
Insert into dept values(20,’account’,’dollar’)
Insert into dept values(30,’marcket’,’DC’)
Select * from dept;
Press f5 to execute
Starts- setting control panel administrative tools data sources (ODBC)
Select the system menu tab click on add
Select the driver SQL server click on finish
Name: Batch 4pm _SQL_server
Server Name: admin
Click on next
Select with SQL server authentication
Login id :
Click on next
Select the change default database
Click on next click on finish.
Click on text data source, click ok
Open the client workflow manager from connection menu select relational
Select the type SQL server, click on new
Name: Batch 4pn_reader_SQl
User name :sa
Password : sa
|Data base Name||Batch 4 pm|
From tools menu select source analyser.
From source menu click import from data base.
Connect to the data base with following details
ODBC data source: batch _SQL _server
User name : sa
Owner name: sa
Click on connect
From show owner select all.
Select the table dept click ok
This is the type passive transformation which is used to import the stored procedure from the data base.
A stored procedure should exist in the data base before use the stored procedure transformation in the mapping.
Set the following properties to define the stored procedure transformation
This is the default property set for the stored procedure transformation, it allows you to pass record by record to mapping during session execution.
A stored procedure executes before the session reads the data from source
A stored procedure executes after session reads the data from source
A stored procedure executes before session loads the data into the target.
A stored procedure executes after the session load the data into the target
There are 2 types of stored procedures.
Create the following stored procedure in the target database
Create procedure annual – tax where (sal in number, tax out number) is
Tax := sal * 0.17;
Create source and target definitions
Empno, ename, job, sal, tax, deptno(emp -proc)
Create a mapping with the name M_Emp _proc_call
Drop the source and target definition
From transformation menu select create select the transformation type
enter the name click on create
connect to the database the following details
ODBC data source: batch 4pm _ target _oracle
User name : batch 4pm
Owner name: batch 4pm
Click on connect
Select the procedure click ok
From SQ- Emp connect the port sal to the stored procedure
From stored procedure connect the port tax to the target
From source qualifier connect the remaining port to the target
From repository menu click on save
This is the type an activity transformation which allows you to control the transaction which are bounded by commit and roll back transaction control operation
The power center supports 2 different ways to control the transactions
The conditional transaction control expression can be developed using transaction control transformation at mapping level
IFF (sal>3000, commit, roll back)
A commit interval is the no of rows at which an integration service apply commit to the target
This property can be set at session level
The default commit interval is 10000
Create a source and target definitions
Empno, ename, job,sal, deptno
Create a mapping with the name M_ Transaction_control
Drop the source and target definitions
Create the transformation type transformation control
From source qualifier copy the required ports to the transaction control transformation
From transaction control transformation connect the ports to target
Double click on transaction control transformation selct the properties tab
|Transformation control condition||IFF(sal>2500, To –commit –after To – Rollback -after)|
Click apply and click ok
For in-depth understanding of Informatica, click on