SQL Server Database Setup in Informatica

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

Login name: sa

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

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

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.

2. Source preload:

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

3. Source post load:

A stored procedure executes after session reads the data from source

4. 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

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

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

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