Sequence Generator transformation:

• Sequence Generator transformation is Passive and Connected Transformation.
• The Sequence Generator transformation generates numeric values. Use the Sequence Generator to create unique primary key values, replace missing primary keys, or cycle through a sequential range of numbers.
• We use it to generate Surrogate Key in Data Ware House environment mostly. When we want to maintain history, then we need a key other than Primary Key to uniquely identify the record. So we create a Sequence 1,2,3,4 and so on. We use this sequence as the key. 
• For example: If EMPNO is the key, we can keep only one record in target and can’t maintain history. So we use Surrogate key as Primary key and not EMPNO.

Sequence Generator Ports:

• The Sequence Generator transformation provides two output ports: NEXTVAL and CURRVAL.
• We cannot edit or delete these ports.
• Likewise, we cannot add ports to the transformation.
• NEXTVAL: Use the NEXTVAL port to generate sequence numbers by connecting it to a transformation or target.
• For example, we might connect NEXTVAL to two target tables in a mapping to generate unique primary key values.
• CURRVAL: CURRVAL is NEXTVAL plus the Increment By value.
• We typically only connect the CURRVAL port when the NEXTVAL port is already connected to a downstream transformation.
• If we connect the CURRVAL port without connecting the NEXTVAL port, the Integration Service passes a constant value for each row.
• When we connect the CURRVAL port in a Sequence Generator transformation, the Integration Service processes one row in each block. We can optimize performance by connecting only the NEXTVAL port in a mapping.

Points to remember:

• If Current value is 1 and end value 10, no cycle option. There are 17 records in source. In this case session will fail.
• If we connect just CURR_VAL only, the value will be same for all records.
• If Current value is 1 and end value 10, cycle option there. Start value is 0. There are 17 records in source. Sequence: 1 2 – 10. 0 1 2 3 –
• To make above sequence as 1-10 1-20, give Start Value as 1. Start value is used along with Cycle option only.
• If Current value is 1 and end value 10, cycle option there. Start value is 1. There are 17 records in source. Session runs. 1-10 1-7. 7 will be saved in repository. If we run session again, sequence will start from 8.
• Use reset option if you want to start sequence from CURR_VAL every time.

Stored Procedure transformation

1. Stored Procedure transformation is a passive transformation. Stored Procedure transformation is a Connected and Unconnected Transformation
2. Stored procedures are stored and run within the database.
3. A Stored Procedure transformation is an important tool for populating and maintaining databases. Database administrators create stored procedures to automate tasks that are too complicated for standard SQL statements.

Use of Stored Procedure in mapping:

1. Check the status of a target database before loading data into it.
2. Determine if enough space exists in a database.
3. Perform a specialized calculation.
4. Drop and recreate indexes. Mostly used for this in projects.

Data Passes Between IS and Stored Procedure

• One of the most useful features of stored procedures is the ability to send data to the stored procedure, and receive data from the stored procedure. There are three types of data that pass between the Integration Service and the stored procedure:
• Input/output parameters: Parameters we give as input and the parameters returned from Stored Procedure.
• Return values: Value returned by Stored Procedure if any.
• Status codes: Status codes provide error handling for the IS during a workflow. The stored procedure issues a status code that notifies whether or not the stored procedure completed successfully. We cannot see this value. The IS uses it to determine whether to continue running the session or stop.

Specifying when the Stored Procedure Runs

• Normal: The stored procedure runs where the transformation exists in the mapping on a row-by-row basis. We pass some input to procedure and it returns some calculated values. Connected stored procedures run only in normal mode.
• Pre-load of the Source: Before the session retrieves data from the source, the stored procedure runs. This is useful for verifying the existence of tables or performing joins of data in a temporary table.
• Post-load of the Source: After the session retrieves data from the source, the stored procedure runs. This is useful for removing temporary tables.
• Pre-load of the Target: Before the session sends data to the target, the stored procedure runs. This is useful for dropping indexes or disabling constraints.
• Post-load of the Target: After the session sends data to the target, the stored procedure runs. This is useful for re-creating indexes on the database.

Using a Stored Procedure in a Mapping

1. Create the stored procedure in the database.
2. Import or create the Stored Procedure transformation.
3. Determine whether to use the transformation as connected or unconnected.
4. If connected, map the appropriate input and output ports.
5. If unconnected, either configure the stored procedure to run pre- or post-session, or configure it to run from an expression in another transformation.
6. Configure the session.

Update Strategy transformation

Update Strategy transformation Active and Connected Transformation. The Update Strategy transformation to update, delete or reject rows coming from source based on some condition.

For example; if Address of a CUSTOMER changes, we can update the old address or keep both old and new address. One row is for old and one for new. This way we maintain the historical data.

Update Strategy is used with Lookup Transformation. In Data warehouse, we create a Lookup on target table to determine whether a row already exists or not. Then we insert, update, delete or reject the source record as per business need.

In PowerCenter, we set the update strategy at two different levels:

1. Within a session
2. Within a Mapping

Update Strategy within a session:

When we configure a session, we can instruct the IS to either treat all rows in the same way or use instructions coded into the session mapping to flag rows for different database operations.

Session Configuration:

Edit Session -> Properties -> Treat Source Rows as: (Insert, Update, Delete, and Data Driven). Insert is default.

Specifying Operations for Individual Target Tables:

You can set the following update strategy options:

1. Insert: Select this option to insert a row into a target table.
2. Delete: Select this option to delete a row from a table.
3. Update: We have the following options in this situation:
1. Update as Update. Update each row flagged for update if it exists in the target table.
2. Update as Insert. Inset each row flagged for update.
3. Update else Insert. Update the row if it exists. Otherwise, insert it.
4. Truncate: Select this option to truncate the target table before loading data.

Flagging Rows within a Mapping:

Within a mapping, we use the Update Strategy transformation to flag rows for insert, delete, update, or reject.

Operation	Constant	Numeric Value
INSERT	DD_INSERT	0
INSERT	DD_INSERT	0
UPDATE	DD_UPDATE	1
DELETE	DD_DELETE	2
REJECT	DD_REJECT	3

Update Strategy Expressions:

Frequently, the update strategy expression uses the IIF or DECODE function from the transformation language to test each row to see if it meets a particular condition. You can write these expression in Properties Tab of Update Strategy transformation.

IIF( ( ENTRY_DATE > APPLY_DATE), DD_REJECT, DD_UPDATE )

Or

IIF( ( ENTRY_DATE > APPLY_DATE), 3, 2 )

Note: We can configure the Update Strategy transformation to either pass rejected rows to the next transformation or drop them. To do, see the Properties Tab for the Option.

Difference between Connected and UnConnected Lookup Transformation:

1. Connected lookup receives input values directly from mapping pipeline whereas UnConnected lookup receives values from :LKP expression in another transformation.
2. Connected lookup trasformation will process each and every row whereas UnConnected lookup will not process each and evry row. It will return the values based expression condition.
3. If the LKP condition is not matched the lookup transformation will return the default value in case of Connected lookup. If no match found for the LKP condition, the lookup transformation will return null values in case of Unconnected lookup.
4. Connected lookup transformation can use static or dynamic cache, whereas Unconnected lookup can use only static cache.
5. Connected lookup returns multiple columns from the same row whereas the UnConnected lookup has one return port and returns one column from each row.
6. Connected lookup supports user-defined default values and the UnConnected lookup does not support user defined values.

Unconnected Lookup transformation

An unconnected Lookup transformation is separate from the pipeline in the mapping. We write an expression using the :LKP reference qualifier to call the lookup within another transformation.

Steps to configure Unconnected Lookup:

1. Add input ports.
2. Add the lookup condition.
3. Designate a return value.
4. Call the lookup from another transformation.

Example for create a unconnected Lookup Transformation

1. Let us assume EMP will be source table and DEPT will be LOOKUP table.
2. Create a target table T_UNCONN_LKP in target designer. Table should contain all ports of EMP table plus LOC.
3. Go to the Mapping Designer.
4. Click Mapping-> Create-> Give name. Ex: m_UNCONN_LKP
5. Drag Source and Target table.
6. Creat an EXPRESSION transformation and pass all ports from Source Qualifier to EXPRESSION transformation.
7. Then connect all fields from EXPRESSION to target except DNAME.
8. Create a LOOKUP transformation.

9. As DEPT is the Source definition, click Source and then Select DEPT.
10. Click Ok.
11. Now Edit the Lookup Transformation. Go to Ports tab.
12. As DEPTNO is common in source and Lookup, create a port IN_DEPTNO ports tab. Make it Input port only and Give Datatype same as DEPTNO.

13. Designate LOC as Return Port. Check on R to make it.
14. Now add a condition in Condition Tab.
15. DEPTNO = IN_DEPTNO and Click Apply and then OK.
16. Now we need to call this Lookup from Expression Transformation.
17. Edit Expression transformation and create a new output port out_LOC of datatype as LOC. 

18. Open the Expression editor and call Lookup as given below:
19. We double click Unconn in bottom of Functions tab and as we need only DEPTNO, we pass only DEPTNO as input.

20. Write the expression as :LKP.LKPTRANS(DeptNo)
21. The final mapping looks like as below..

22. Validate the call in Expression editor and Click OK.
23. Then save the mapping.
24. Create Session and Workflow. Run the workflow and see the data in target table.
25. Note:
26. Make sure to give connection information for all tables.
27. Make sure to give connection for LOOKUP Table also.

Connected Lookup Transformation

1. Let us assume that EMP will be source table. DEPT will be LOOKUP table.
2. Create a target table T_CONN_LKP_EMP in target designer. Table should contain all ports of EMP table plus DNAME and LOC.
3. Creating Mapping by click Tools -> Mapping Designer.
4. Click Mapping-> Create-> Give name. Ex: m_CONN_LKP_EMP
5. Drag EMP and Target table.
6. Connect all fields from SQ_EMP (Source Qualifier transformation) to target except DNAME and LOC.
7. Then go to Transformation-> Create -> Select LOOKUP from list. Give name and click Create.

8. As DEPT is the Source definition, click Source and then Select DEPT.
9. Click Ok.
10. Now Pass DEPTNO from SQ_EMP to this Lookup. DEPTNO from SQ_EMP will be named as DEPTNO1. Edit Lookup and rename it to IN_DEPTNO in ports tab.
11. Now go to CONDITION tab and add CONDITION.
12. DEPTNO = IN_DEPTNO and Click Apply and then OK.
13. We are not passing IN_DEPTNO and DEPTNO to any other transformation from LOOKUP; we can edit the lookup transformation and remove the OUTPUT check from them.
14. The final mapping look like as shown below..

15. Then Mapping -> Validate and save the mapping.
16. Create Session and Workflow. Run the workflow and see the data in target table.
17. Make sure to give connection information for all tables.
18. Make sure to give connection for LOOKUP Table also.
19. We use Connected Lookup when we need to return more than one column from Lookup table.

Lookup transformation

The Lookup transformation is passive transformation.

Lookup is a transformation to look up data in a flat file or a relational table, view, or synonym.
There are two types of Lookups in Powercenter Designer, namely:

1. Connected Lookup
2. Unconnected Lookup .

We can import a lookup definition from any flat file or relational database to which both the PowerCenter Client and Server can connect. We can use multiple Lookup transformations in a mapping.

Lookup transformation act as left outer joiner. If a particular null record matches found, then it returns null value.

The PowerCenter Server queries the lookup source based on the lookup ports in the transformation. It compares Lookup transformation port values to lookup source column values based on the lookup condition. Pass the result of the lookup to other transformations and a target.

For example, if we want to retrieve all the sales of a product with an ID 10 and assume that the sales data resides in another table called 'Sales'. Here instead of using the sales table as one more source, use Lookup transformation to lookup the data for the product, with ID 10 in sales table.

Different caches can also be used with lookup like static, dynamic, persistent, and shared. The dynamic cache cannot be used while creating an un-connected lookup.

We can use the Lookup transformation to perform following:

• Get a related value: EMP has DEPTNO but DNAME is not there. We use Lookup to get DNAME from DEPT table based on Lookup Condition.
• Perform a calculation: We want only those Employees who’s SAL > Average (SAL). We will write Lookup Override query.
• Update slowly changing dimension tables: Most important use. We can use a Lookup transformation to determine whether rows already exist in the target.

TIP: We use Connected Lookup when we need to return more than one column from Lookup table.

Source Qualifier transformation

Source Qualifier transformation is active and connected transformation.

The Source Qualifier transformation represents the rows that the PowerCenter Server reads when it runs a
session.

It is only transformation that is not reusable. And it is the default transformation except in case of XML or COBOL files.

Tasks to performed by Source Qualifier:

• Join data originating from the same source database: We can join two or more tables with primary key-foreign key relationships by linking the sources to one Source Qualifier transformation.
• Filter rows when the PowerCenter Server reads source data: If we include a filter condition, the PowerCenter Server adds a WHERE clause to the default query.
• Specify an outer join rather than the default inner join: If we include a user-defined join, the PowerCenter Server replaces the join information specified by the metadata in the SQL query.
• Specify sorted ports: If we specify a number for sorted ports, the PowerCenter Server adds an ORDER BY clause to the default SQL query.
• Select only distinct values from the source: If we choose Select Distinct, the PowerCenter Server adds a SELECT DISTINCT statement to the default SQL query.
• Create a custom query to issue a special SELECT statement for the PowerCenter Server to read source data: For example, you might use a custom query to perform aggregate calculations.

Joiner Transformations

Joiner transformations connected and active transformation.

Joiner transformation is an example for horizontal merging of tables..

Used to join source data from two related heterogeneous sources residing in different locations or file systems. Or, we can join data from the same source.

If we need to join 'n' tables, then we need 'n-1' Joiner transformations.

The Joiner transformation requires atleast two sources with at least one matching port. The Joiner transformation uses a condition that matches one or more pairs of ports between the two sources.

Joiner can't be used in following conditions:

• Either input pipeline contains an Update Strategy transformation.
• We connect a Sequence Generator transformation directly before the Joiner transformation.

Workig with the Joiner transformation:

1. In the Mapping, import create Joiner Transformation.
2. Edit Joiner Transformation -> Condition Tab
3. Add condition
1. In Joiner, one table is called as MASTER and other as DETAIL.
2. MASTER table is always cached. We can make any table as MASTER.
3. Edit Joiner Transformation -> Ports Tab -> Select M for Master table.
4. We can add as many conditions as needed.
5. Only = operator is allowed.
6. Set Master in Ports tab.
7. Mapping -> Validate
8. Repository -> Save.
9. Create Session and Workflow. Run the workflow and see the data in the target table.

Points to remember:

• Table with less number of rows should be made MASTER to improve performance. Because
• When the PowerCenter Server processes a Joiner transformation, it reads rows from both sources concurrently and builds the index and data cache based on the master rows. So table with fewer rows will be read fast and cache can be made as table with more rows is still being read.
• The fewer unique rows in the master, the fewer iterations of the join comparison occur, which speeds the join process.
• If we join Char and Varchar datatypes, the PowerCenter Server counts any spaces that pad Char values as part of the string. So if you try to join the following:
Char (40) = “abcd” and Varchar (40) = “abcd”

Then the Char value is “abcd” padded with 36 blank spaces, and the PowerCenter Server does not join the two fields because the Char field contains trailing spaces.


The Joiner transformation does not match null values.
Any one of the pipeline contains Sequential transformation, then the Joiner transformation rejects the records.

Aggregator transformation

Aggregator transformation is a connected and active transformation.

The Aggregator transformation allows us to perform aggregate calculations, such as averages and sums. Aggregator transformation allows us to perform calculations on groups.

The Aggregator transformation is created with the following components...

1. Aggregate expression
2. Group by port
3. Sorted Input
4. Aggregate cache

Aggregate expression:

• Entered in an output port.
• Can include non-aggregate expressions and conditional clauses.
• The transformation language includes the following aggregate functions:
• AVG, COUNT , MAX, MIN, SUM
• FIRST, LAST
• MEDIAN, PERCENTILE, STDDEV, VARIANCE
• Single Level Aggregate Function. E.g.: MAX(SAL)
• Nested Aggregate Function. E.g.: MAX( COUNT( ITEM ))

Group By Ports:

• This component defines the groups for a specific port which participates for aggregations, rather than performing the aggregation across all input data. For example, we can find Maximum Salary for every Department.
• When grouping data, the Aggregator transformation outputs the last row of each group unless otherwise specified.
• In Aggregator Transformation, Open Ports tab and select Group By as needed.

Using Sorted Input:

• Use to improve session performance.
• To use sorted input, we must pass data to the Aggregator transformation sorted by group by port, either in ascending or descending order.
• When we use this option, we tell Aggregator that data coming to it is already sorted.
• We check the Sorted Input Option in Properties Tab of the transformation.
• If the option is checked but we are not passing sorted data to the transformation, then the session fails.

Aggregator Caches:

• The PowerCenter Server stores data in the aggregate cache until it completes aggregate calculations.
• It stores group values in an index cache and row data in the data cache. If the PowerCenter Server requires more space, it stores overflow values in cache files.

Note: The PowerCenter Server uses memory to process an Aggregator transformation with sorted ports. It does not use cache memory. We do not need to configure cache memory for Aggregator transformations that use sorted ports.

Working with Aggregato transformation:

1. In the mapping click Transformation -> Create -> Select AGGREGATOR from list. Give name and click Create. Now click done.
2. Pass the required fields (e.g.: SAL and DEPTNO) from SQ_EMP to AGGREGATOR Transformation.
3. Edit AGGREGATOR Transformation. Go to Ports Tab
4. Create output ports (e.g.: 4 output ports: OUT_MAX_SAL, OUT_MIN_SAL, OUT_AVG_SAL, OUT_SUM_SAL)
5. Open Expression Editor one by one for all output ports and give the calculations. Eg: MAX(SAL), MIN(SAL), AVG(SAL),SUM(SAL)
6. Click Apply -> Ok.
7. Drag target table now.
8. Connect the output ports from Aggregator transformation to target table.
9. Click Mapping -> Validate
10. Repository -> Save
11. Create Session and Workflow. Run the workflow and see the data in target table.

Why the Sorter transformation is an active transformation?

Actually the Sorter transformation helps to sort the data in ascending or descending order. And hence the number of input rows to the Sorter transformation remains the same as the output rows.

But the Sorter transformation has an option "Distinct", using this option we can remove duplicate records. The number of records can changed, if the source contains duplicate rows. So the Sorter transformation is an active transformation.

Sorter transformation

Sorter transformation is a connected and an active transformation

The Sorter transformation allows us to sort data in ascending or descending order according to a specified sort key.

We can also configure the Sorter transformation for case-sensitive sorting, and specify whether the output rows should be distinct.

When we create a Sorter transformation in a mapping, we specify one or more ports as a sort key and configure each sort key port to sort in ascending or descending order. We also configure sort criteria the PowerCenter Server applies to all sort key ports and the system resources it allocates to perform the sort operation.

The Sorter transformation contains only input/output ports. All data passing through the Sorter transformation is sorted according to a sort key. The sort key is one or more ports that we want to use as the sort criteria.

Rank transformation

Rank transformation is an active and connected transformation.
The Rank transformation allows us to select only the top or bottom rank of data. It allows us to select a group of top or bottom values, not just one value.

During the session, the PowerCenter Server caches input data until it can perform the rank calculations.

How Union Transformation is Active transformation ?

Generally, the Union transformation returns same number of rows that passed to the transformation. For example; if source is table 1 contains 10 rows and Table 2 contain 5 rows then the Union transformation behaves like a UNION ALL in SQL and returns 1 records. That is the number of records unchanged which means it is a Passive transformation.

But in the Union transformation properties we have one option IS ACTIVE, when we enable this option it follow active mode. otherwise it will follow Passive mode.

Union transformation

The Union transformation is an active and Connected transformation.

The Union transformation is a multiple input group transformation that you can use to merge data from multiple pipelines or pipeline branches into one pipeline branch. It merges data from multiple sources similar to the UNION ALL in SQL statement to combine the results from two or more SQL statements.

Points to remember:

• We can create multiple input groups, but only one output group.
• We can connect heterogeneous sources to a Union transformation.
• All input groups and the output group must have matching ports. The precision, datatype, and scale must be identical across all groups.
• The Union transformation does not remove duplicate rows. To remove duplicate rows, we must add another transformation such as a Router or Filter transformation.
• We cannot use a Sequence Generator or Update Strategy transformation upstream from a Union transformation.

Working with Union transformation:

1. Import/ create soure and target.
2. In the mapping drag source, target, and create Union transformation.
3. Edit Union Transformation. Go to Groups Tab
4. One group will be already there as we dragged ports from Source to Union Transformation.
5. As we have n source tables, we need n input groups. Click Add button to add more groups.
6. We can also modify ports in ports tab.
7. Click Apply -> Ok.
8. Drag target table now.
9. Connect the output ports from Union to target table.
10. Validate mapping.
11. Create Session and Workflow. Run the workflow

Question: I have different flatfiles with same structure presented in different locations, and I need to merge data vertically. Then how many Union transformations I need?
Ans: In this particular scenario, we use "Indirect loading" at session level which will discuss in next posts.

Note: If the source tables are relational tables then we can write Sql query (Union) at Source Qualifier transformation for best result (in case of more source tables).

Difference between Router and Filter transformations?

1. We cannot pass rejected data forward in filter but we can pass it in router. Rejected data is in Default Group of router.
2. Filter handles single condition where as Router handles multiple conditons.
3. Filter has single target and Router has multiple targets.
4. Filter has no default group.

Router transformation

The Router transformation is an active and connected transformation.
A Router transformation is similar to a Filter transformation because both transformations allow you to use a condition to test data. A Filter transformation tests data for one condition and drops the rows of data that do not meet the condition. However, a Router transformation tests data for one or more conditions and gives you the option to route rows of data that do not meet any of the conditions to a default output group.

A Router transformation consists of input and output groups, input and output ports, group filter conditions, and properties that we configure in the Designer.

Working with Router transformation:

1. Import/ create soure and target.
2. In the mapping drag source, target, create Router transformation .
3. Edit Router Transformation. Go to Groups Tab
4. Click the Groups tab, and then click the Add button to create a user-defined group. The default group is created automatically..
5. Click the Group Filter Condition field to open the Expression Editor.
6. Enter a group filter condition.
7. Click Validate to check the syntax of the conditions you entered.
8. Create another group if neccessary.
9. The rest of the records not matching the above conditions will be passed to DEFAULT group.
10. Click OK -> Click Apply -> Click Ok.
11. Now connect the ports from router to target tables.
12. Then validate Mapping and save.
13. Create Session and Workflow. Run the workflow

Filter transformation

Filter transformation is an active and connected transformation.

Using the Filter transformation, we can filter rows in a mapping. We pass all the rows from a source transformation through the Filter transformation, and then enter a filter condition for the transformation. All ports in a Filter transformation are input/output and only rows that meet the condition pass through the Filter transformation.

Working with the Filter Transformation...

• Import Source and Target.
• Then create Filter transformation.
• Edit Filter Transformation. Go to Properties Tab
• Click the Value section of the Filter condition, and then click the Open button.
• The Expression Editor appears.
• Enter the filter condition you want to apply.
• Click Validate to check the syntax of the conditions you entered.

Tip: Using the Filter transformation at the source side for the best performence because only the filter rows pass to the next level of transformation.

Expression transformation

Expression transformation is a passive and connected transformation.

The Expression transformation is used to calculate values to perform any non-aggregate calculations in a single row before we write to the target. We can also use the Expression transformation to test conditional statements before we output the results to target tables or other transformations.

There are 3 types of ports in Expression Transformation:

1. Input
2. Output
3. Variable: Used to store any temporary calculation.

Calculating Values

To use the Expression transformation to calculate values for a single row, we must include the following ports:

• Input or input/output ports for each value used in the calculation: For example: To calculate Total Salary, we need salary and commission.
• Output port for the expression: We enter one expression for each output port. The return value for the output port needs to match the return value of the expression.

We can enter multiple expressions in a single Expression transformation. We can create any number of output ports in the transformation.

ODBC Connection in Informatica

ODBC stands for Open Data Base Connectivity. An ODBC is a middleware or an interface which allows you any database to Informatica.

How to create an ODBC in Informatica

For creating an ODBC, we can follow two methods:

1. Go to Control Panel => Administrative Tools =>Data Souces (ODBC)
2. Select System DSN tab and click on Add
3. Select the driver Oracle in OraDB10g_home
4. Then click Finish.
5. Enter the following details:
• Data Source Name: Source_Scott_ODBC (your choice)
• TNS Service name: ORCL
• User Id: Scott
6. Then click on Test Connection
7. There enter password and finally click on Ok.

Or you can also creating an ODBC from Informatica level as follows:

1. While Creating the target, first we need impliment the metadata of the target by drag the source in
2. Target Designer and then Generate/Execute SQL.
3. Click on Connect then click on Add.

4. Select System DSN tab and click on Add

5. Select the driver Oracle in OraDB10g_home
6. Then click Finish.

7. Enter the following details:
• Data Source Name: Source_Scott_ODBC (your choice)
• TNS Service name: ORCL
• User Id: Scott

8. Then click on Test Connection
9. There enter password and finally click on Ok.