• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

SQL Server Database Setup in Informatica

SQL Derver 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 data base 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

USE BATCH 4pm

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

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

  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.

Desired to gain proficiency on Informatica? Explore the blog post on Informatica training to become a pro in Informatica.
  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.

Target Post Load

A stored procedure executes after 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 data base

Create procedurerr 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 data base 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 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 mapping level
  2. Using commit interval property at 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 indepth understanding of Informatica, click on

Summary
Review Date
Reviewed Item
SQL Server Database Setup in Informatica
Author Rating
5

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on SQL Server Database Setup in Informatica"

Leave a Message

Your email address will not be published. Required fields are marked *

Site Disclaimer, Copyright © 2016 - All Rights Reserved.