SQL Server Database Setup in Informatica

  • Share this blog:

SQL Server Database Setup in Informatica

Start Screenshot_24 programsScreenshot_24Microsoft SQLserverScreenshot_24 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 Screenshot_24 programsScreenshot_24 Microsoft SQLserver-Screenshot_24 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

Password: sa

Click ok

Execute the following query in the query panel


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

Creation of ODBC connection to SQL server

Starts-Screenshot_24 settingScreenshot_24 control panelScreenshot_24 administrative toolsScreenshot_24 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 :

Password :sa

Click on next

Select the change default database

Batch 4pm

Click on next click on finish.

Click on text data source, click ok

Creation  of relational connection to the SQL server

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

Header Attribute


Data base Name Batch 4 pm
Server Name Admin




Click ok

Importing source definition from SQL server

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

Password: sa

Click on connect

From show owner select all.

Select the table dept click ok

SQL Server Database Setup in Informatica

Stored Procedure Transformation

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

  1. Normal:

This is the default property set for the stored procedure transformation, it allows you to pass record by record to mapping during session execution.


If you want to enrich your career and become a professional in Informatica, then visit Tekslate - a global online training platform: "Informatica Training" This course will help you to achieve excellence in this domain.
  1. Source preload:

A stored procedure executes before the session reads the data from source

  1. Source post load:

A stored procedure executes after session reads the data from source

  1. Target pre load:

A stored procedure executes before session loads the data into the target.

Informatica Tutorials & Interview Questions

Target Post Load

A stored procedure executes after the session load the data into the target

There are 2 types of stored procedures.

  1. Connect stored procedure
  2. Un connected stored procedure

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

Stored Procedure

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

Password: target

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

Transaction control transformation:

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

  1. Using transaction control transformation at the mapping level
  2. Using commit interval property at the session level

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 Attribute


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

About Author
Author Bio

TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.