top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

how to reduce the rework on Informatica ETLs due to change of table and column names

+2 votes
485 views

Currently, we have a lot of ETLs developed and the underlying table and column names are going to change. For example, the physical names used abbreviated names rather than full names since we wanted to deploy on Oracle, but for now, we decided we will use only SQL Server, hence there is a discussion on using full names at the DB level. This will impact the ETLs that we have already developed.

I was just wondering whether there is any efficient way to remap the ETL to the changed column names? I can provide a file with old and new table/column names as input.

Any help on this will be really appreciated.

posted Oct 13, 2014 by Sachin

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

One possible way is to use a SQL Override value in all of your Source Qualifier transformations that contains a SQL query with parameter file substitutions. This way you can change the column names and SELECT them in mapping sources without modifying the Source definitions. Of course this would require some up-front refactoring of your existing Source Qualifier transformations.

Alternatively, at the database level you could create views onto the existing tables that use the old abbreviated column names, effectively providing a translation between the new column name and the old one. This would not require any changes to your existing ETL.

However, I would generally advise against changing anything in this case, especially since the difference is purely cosmetic - it will only produce maintenance and complexity headaches.

answer Oct 15, 2014 by Shweta Singh
Similar Questions
+2 votes
How to change file names in multiple folders in Informatica?

I have this informatica requirement where, i am getting some files say 1000 files, these files gets divided into multiple folders on unix server. i want to rename this file, let me give you an eg:

File name:input
abc1.txt
abc2.txt
abc3.txt

Now I want to change these file names by adding account_no,client_no,datestamp(all these values resides in one table). eg:
abc1_123_123_date.txt
abc2_456_321_date.txt
abc3_789_765_date.txt

This is just an eg: ..like this I ll be having say 10 listfiles and each listfile ll contain 100 files.Do i need to create 10 mappings for each listfile or is there any way that i can implement this in single mapping.

Please note:I cannot club all files in one folder and then change the file names and then again divide into original directories, as sometimes we get files in separated directories and we have to change there name and keep them in that particular folder only(for other business logic).

Kindly provide solution for this.

+1 vote
Informatica: Converting a table into multiple target tables based on one column.

In my source table data is 

s_name,p_name,value
s1 ,   p1,     10
s1 ,   p2,     xyz
s1 ,   p3 ,    abc
s2 ,   p1 ,    20
s2 ,   p2 ,    xyz
s2 ,   p3 ,    abc

I want two target tables, first table is based on s_name s1, second table based on s_name s2. Both table contains contains p_name and value.

The target table data like as

Table s1

p_name,value 
p1,    10
p2,    xyz
p3,    abc

Table s2 

p_name,value 
p1,    20
p2,    xyz
p3,    abc
0 votes
Get max value of a column in my source table in Informatica

I have the following table with the shown data in it:

send_date | household_ID

11-20-2014 | 123
11-20-2014 | 456
11-15-2014 | 789

I need to do 2 things:

1) Calculate the max value for send_date

2) Filter out any rows whose send_date does not match that value

In other words, I want the output to be:

send_date | household_ID

11-20-2014 | 123
11-20-2014 | 456

Row number 3 should be filtered out as its send_date is not the max.

I tried creating an aggregate, grouping by all columns, and creating a new output port called MAX_DATE with an expression of MAX(SEND_DATE), then have a filter transformation with the condition MAX_DATE = SEND_DATE

This lets all rows through, though. What can I do to make this work....

+2 votes
Informatica metadata table - Primary key column from target table

Couple of tables in our Database doesm't has any key. However, key was created in target table at informatica level.

Now my need is, need to identify informatica metadata table where I can find tgt table and corresponding column name marked as key.

Kindly suggest

0 votes
Informatica metadata table - Primary key column from target table

Couple of tables in our Database doesn't has any key. However, key was created in target table at informatica level.

Now my need is, need to identify informatica metadata table where I can find tgt table and corresponding column name marked as key.

Kindly suggest

...