0

I have to load the flat file using informatica power center whose structure is not static. Number of columns will get changed in the future run.

Here is the source file:

enter image description here

In the sample file I have 4 columns right now but in the future I can get only 3 columns or I may get another set of new columns as well. I can't go and change the code every time in the production , I have to use the same code by handling this situation.

Expected result set is:-

enter image description here

Is there any way to handle this scenario? PLSQL and unix will also work here.

Yash
  • 39
  • 1
  • 7
  • Can you tell what the target is? And what is the expected result? – Maciejg Aug 24 '22 at 10:01
  • Added the expected result. – Yash Aug 24 '22 at 21:29
  • If source structure is not fixed and target depends on source, as you mention in a comment below, then it is not possible to have this loaded by a single mapping in PowerCenter. You might try building mapping generatotor for this. Honestly I do not see another solution than just automating or not the building process. I guess you'll end up having one mapping for each source anyway. – Maciejg Aug 25 '22 at 20:31

1 Answers1

0

I can see two ways to do it. Only requirement is - source should decide a future structure and stick to it. Come tomorrow if someone decides to change the structure, data type, length, mapping will not work properly.

Solutions -

  1. create extra columns in source towards the end. If you have 5 columns now, extra columns after 5th column will be pulled in as blank. Create as many as you want but pls note, you need to transform them as per future structure and load into proper place in target.

  2. This is similar to above solution but in this case, read the line as single column in source+ source qualifier as large string of length 40000.
    Then split the columns as per delimiter in Informatica expression transformation. This splitting can be done by following below thread. This can be also tricky if you have 100s of columns.
    Split Flat File String into multiple columns in Informatica

Koushik Roy
  • 6,868
  • 2
  • 12
  • 33
  • my target table structure is completely depends on the source structure. Is there any other way to do this ? – Yash Aug 24 '22 at 21:20