How to Setup SQL Server Database in Informatica?

    Start  --> programs --> Microsoft 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 data base 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 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 --> 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 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 NameBatch 4 pm
    Server NameAdmin





    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.


    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 targetThere are 2 types of stored procedures

    1. Connect stored procedure
    2. Un connected stored procedure

    Create the following stored procedure in the target data baseCreate procedurerr annual – tax where (sal in number, tax out number ) is

    BeginTax := 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 definitionFrom transformation menu select create select the transformation type


    Stored Procedure

    enter the name click on create

    connect to the data base the following detailsODBC data source: batch 4pm _ target _oracle


    User name : batch 4pmOwner name: batch 4pm

    Password: targetClick on connect

    Select the procedure click okFrom SQ- Emp connect the port sal to stored procedure

    From stored procedure connect the port tax to the targetFrom 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 operationThe 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 conditionIFF(sal>2500, To –commit –after  To – Rollback -after)

    Click apply and click ok

    Source: TekSlate