Eliminate extra columns from file

[Gunner49]

Hello All,

I have a requirement where the source file can have few extra columns than what is present in a table. I need to eliminate the extra columns and only load based on the metadata from the table.

Explored the option of Dynamic datatype but the table would be pre defined and hence cannot drop the table and recreate. Is there any workaround for this?

Example :

Source File :

Col1,Col2,Col3,Col4,Col5

Table Metadata:

Col1,Col2,Col3

Need to eliminate Col4 & Col5 from loading. This could be 'n' number of columns.

Thanks.

[akumar2301]

1) you are using dynamic metadata type or specific to table ?
2) extra columns are always at the end ?
3) do you can get number of fixed columns or number of column to remove in advance ?

[Gunner49]

@uganesh Thank you.

Please see my response 

1) you are using dynamic metadata type or specific to table ?

By defining a specific structure in the schema and in case I have few extra columns in the end while loading it to dbOutput, it works fine and ignores the additional columns.

However, I have 100 such files and can't define the schema structure, hence my plan is to use dynamic metadata. Using this option is resulting in an "invalid identified" at the target as these columns are not present

2) extra columns are always at the end ?

Yes. Extra Columns are always the end

3) do you can get number of fixed columns or number of column to remove in advance ?

No. We don't get such information. The tables would be pre-defined and we need to truncate and Insert the data from source files to their relevant tables.

Thanks