How to handle table or column names with reserved words in Informatica?

In Informatica, while querying a relational source using SQL overrides the session fails if you use words that are defined as keywords in the target database. for example words like USER, MONTH, YEAR etc are reserved keywords in many popular databases. These words are termed as reserved keywords.

In order to query tables or columns with reserved names you have two options:-

1. Enclose the table or column name in double quotes in the SQL query override.

2. Create a file named reswords.txt in your installation directory of the Integration service, (Usually it is server/bin directory) and add all the reserved words in that file. The text is not case sensitive.

You can create a section for each database you are using your integration service with e.g.:-
[ORACLE]
USER
[SQL Server]
MONTH

Integration service at run time will lookup this file and will automatically place quotes around reserved words and then issue the query to the database.

You may need to turn on SQL-92 standards for some database to use quoted identifiers. Use the connection environment SQL to issue the command. For e.g. for MS SQL server issue the following command:-

SET QUOTED_IDENTIFIER ON

Leave a comment if you need any more details.

 

 

3 thoughts on “How to handle table or column names with reserved words in Informatica?”

  1. Hi Raghav

    I created reswords.txt file in $PMRootDir\bin folder
    and added below to the file.
    [SQL Server]
    OPTION

    Also added SET QUOTED_IDENTIFIER ON to the presql as the target table is in SQL server 08 and still getting the same error while inserting the records to the target.

    Am I missing something?

    1. Hi Dam

      You need to add the command SET QUOTED_IDENTIFIER ON to the connection environment of the sql server connection you are using. It will be in the connections->Relational menu in workflow manager.

      Thanks,
      Raghav

Leave a Reply

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