Search code examples
csvssisetlflat-filessis-2017

SSIS reading flatfile header column

Can you guys help me (point me in the right direction) on how I can achieve the following in SSIS.

So, I have a flatfile that looks like this

    ColumnA  ColumnB  ColumnC  ColumnD  ColumnN
    1        x        APPLE    Random1  MoreRandomData1
    2        y        ORANGE   Random2  MoreRandomData2
    3        z        OTHER    Random3  MoreRandomData3

... and I need to store these data into a table in the following format 

ColumnA, ColumnB, BigBlurColumn
1        x        ColumnC:APPLE, ColumnD:Random1, ColumnN:MoreRandomData1
2        y        ColumnC:ORANGE, ColumnD:Random2, ColumnN:MoreRandomData2
3        z        ColumnC:OTHER, ColumnD:Random3, ColumnN:MoreRandomData3

Here's my question:
1. How can i read the header/column of a flatfile? 2. Is it possible to pivot the result of #1

If I can managed to manipulate both #1 and #2 the reset will be fairly easy for me to do in SSIS, obviously I can script these however my client insist on using SSIS as this is there standard ETL tool.

Any ideas on how I can achieve above scenario?

Thanks

Solution

  • In the flat file connection manager, uncheck First row contains header option. Then go to Advanced Tab, delete all column and leave one and change its length to 4000.

    In the data flow task, add a script component that split each row and:

    1. Read the columns headers from the first row
    2. Generate the desired output columns in all remaining rows

    The following answers (different situations but they are helpful) will give you some insights: