How To Create Relational Connection In Informatica For Sql Server

SQL Server Database Setup in Informatica

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

Password: sa

Click ok

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

Creation of ODBC connection to SQL server

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

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	Value
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

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.

Source preload:

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

Source post load:

A stored procedure executes after session reads the data from source

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.

Connect stored procedure
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

Begin

Tax := sal * 0.17;

End;

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

Using transaction control transformation at the mapping level
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

Procedure

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	Value
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

Rank Transformation in Informatica
Aggregator Transformation in Informatica
Expression Transformation in Informatica
Lookup Transformation in Informatica
ETL Project Architecture
Interview Questions