Handling special characters with Informatica

Sometimes users may face issues while processing special characters in the data like Arabic, Hebrew, Chinese etc. using Informatica PowerCenter. Let’s discuss about how we can process such non English scripts in Informatica PowerCenter workflows.

Eg: Source data = D33450–1127

after processing the source data with Informatica powercenter, target data looks like below.

Target data = D33450?1127

Sometimes target data will get corrupted and returns unreadable format.

Step 1:

Make sure the Informatica Admin page settings are configured as below

Admin console properties-

a. Power Centre repository -> Code page option should be set to -> “Unicode”

b. Integration Service -> DataMovementMode → “Unicode”

Step 2:

DB Properties-

a. NLS_CHARACTERSET=UTF8
b. NLS_NCHAR_CHARACTERSET=AL16UTF16
c. NLS_LENGTH_SEMANTICS=CHAR (session) / =BYTE (db)

Step 3:

1. Informatica power center server level setting — set NLS_LANG in the app .bash_profile to AMERICAN_AMERICA.AL32UTF8.
2. Update the Code Page setting for both the source and target connection from ‘MS Windows Latin 1 (ANSI), superset of Latin1’ to ‘UTF-8 encoding of Unicode’.

Post validating these three steps, Informatica should be able to process the special characters without any issue.