top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration Why to Join

    Get Free Article Updates

Facebook Login
Site Registration

Informatica Parameter File Usage - Parametrization Techniques

+2 votes
308 views

Informatica parameter file is a text file which contains 1) Parameter values 2) Variable values for mapping, session and workflows

* Develop environment independent code.
* Control data processing without changing the code in PROD environment
* Easy deployment to production server
* Provide Flexibility to run sessions for different files and databases
posted Apr 26, 2014 by Shatark Bajpai

  Promote This Article
Facebook Share Button Twitter Share Button Google+ Share Button LinkedIn Share Button Multiple Social Share Button
Hi is there any way to schedule workflow using parameter file ... I mean define execution timing in file and execute workflow as per parameter ...
We can schedule workflow through Scheduling tools (Informatica Schedular, ESP Schedular etc) and in Scheduling tool itself we can mention date, time, occurrences to execute workflow.


Related Articles

Informatica PowerCenter is given with a set of options to take care of the error handling in your ETL Jobs.

Error Handling Functions
We can use two functions provided by Informatica PowerCenter to define our user defined error capture logic.

ERROR (): This function causes the PowerCenter Integration Service to skip a row and issue an error message, which you define. The error message displays in the session log or written to the error log tables based on the error logging type configuration in the session. You can use ERROR in Expression transformations to validate data. Generally, you use ERROR within an IIF or DECODE function to set rules for skipping rows.
Ex: IIF (TRANS_DATA > SYSDATE, ERROR ('Invalid Transaction Date'))

Above expression raises an error and drops any record whose transaction data is greater than the current date from the ETL process and the target table.

ABORT (): Stops the session, and issues a specified error message to the session log file or written to the error log tables based on the error logging type configuration in the session. When the PowerCenter Integration Service encounters an ABORT function, it stops transforming data at that row. It processes any rows read before the session aborts. You can use ABORT in Expression transformations to validate data.

Ex: IIF (ISNULL (LTRIM (RTRIM (CREDIT_CARD_NB))), ABORT ('Empty Credit Card Number'))

Above expression aborts the session if any one of the transaction records are coming without a credit card number.

Default Port Value Setting
Using default value property is a good way to handle exceptions due to NULL values and unexpected transformation errors. The Designer assigns default values to handle null values and output transformation errors. Power Center Designer let you override the default value in input, output and input/output ports.

Default value property behaves differently for different port types;
1. Input ports: Use default values if you do not want the Integration Service to treat null values as NULL.
2. Output ports: Use default values if you do not want to skip the row due to transformation error or if you want to write a specific message with the skipped row to the session log.
3. Input/output ports: Use default values if you do not want the Integration Service to treat null values as NULL. But no user-defined default values for output transformation errors in an input/output port.

Default Value Use Case

Use Case 1
Below shown is the setting required to handle NULL values. This setting converts any NULL value returned by the dimension lookup to the default value -1. This technique can be used to handle late arriving dimensions.
enter image description here

Use Case 2
Below setting uses the default expression to convert the date if the incoming value is not in a valid date format.
enter image description here

Error Handling Settings
Error handling properties at the session level is given with options such as Stop on Errors, Stored Procedure Error, Pre-Session Command Task Error and Pre-Post SQL Error. You can use these properties to ignore or set the session to fail if any such error occurs.
Stop on Errors: Indicates how many non-fatal errors the Integration Service can encounter before it stops the session.
On Stored Procedure Error: If you select Stop Session, the Integration Service stops the session on errors executing a pre-session or post-session stored procedure.
On Pre-Session Command Task Error: If you select Stop Session, the Integration Service stops the session on errors executing pre-session shell commands.
Pre-Post SQL Error: If you select Stop Session, the Integration Service stops the session errors executing pre-session or post-session SQL.
enter image description here

READ MORE

Informatica File Transfer Protocol can be used to transfer/move files from different environment into our pre-defined Landing Zone. It can also be used to transfer file to the destination folder/directories. The Integration Service can use FTP to access any machine it can connect to, including mainframes.

Configuring FTP in Informatica Workflow
To use FTP file sources and targets in a session,
- Create an FTP connection object in the Workflow Manager and configure the connection attributes
- Configure the session to use the FTP connection object in the session properties.
- Specify the Remote filename in the connection value of the Session properties.

Guidelines
- Specify the source or target output directory in the session properties. If not specified, the Integration Service in the file in the directory where the Integration Service runs on UNIX or in the Windows System directory.
- Session cannot run concurrently if the same FTP source file or target file located on a mainframe.
- If a workflow containing a session that stages an FTP source or target from a mainframe is aborted, then the same workflow cannot be run until it’s timed out.
- Configure an FTP connection to use SSH File Transfer Protocol (SFTP) while connecting to an SFTP server. SFTP enables file transfer over a secure data stream. The Integration Service creates an SSH2 transport layer that enables a secure connection and access to the files on an SFTP server

Configuring Remote Filename
enter image description here

Multiple Source files read using single FTP Server by parametrization technique
If we want to read multiple source files using single FTP server connection using Informatica parametrization technique as shown below.

Source File1
enter image description here

Source File2
enter image description here

Define variables in the Parameter file as below:
$FTPConnection_CMS=FTP_RTK123
$FTPConnection2_CMS=FTP_RTK123

$Param_FTPConnection_CMS_Remote_Filename=abc123d.2
$Param_FTPConnection2_CMS_Remote_Filename=abc123d.2

READ MORE
Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Bangalore-560102
Karnataka INDIA.
QUERY HOME
...