SSIS reading flatfile header column

Question

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

Are there always 5 columns or could there be more or less? I would just import the lot into a 5 column varchar staging table, but untick headers so it imports the headers as though they are data. Add an Identity column to the staging table so that it numbers the rows. Row 1 is always the header. Then use T-SQL to do everything else. — Nick.Mc, Mar 06 '19 at 05:13
@Nick.McDermaid The shape of the flat-file changes except for the first two columns which is a mandatory field — fo2bug, Mar 06 '19 at 05:19
In that case you need a staging table with just one column which you load the entire row into then split the columns afterwards with T-SQL string functions. Or follow @Hadi suggestion and use a script transformation if you like .Net better — Nick.Mc, Mar 06 '19 at 06:42

Hadi · Accepted Answer · 2019-03-06T05:49:09.250

1

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:

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

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

edited Mar 06 '19 at 05:49

answered Mar 06 '19 at 05:42

Hadi

36,233
13
65
124

Thanks @Hadi, I managed to achieve what I needed while follow some of the ideas from your suggested link. I did add a staging table though just to make things more interesting. – fo2bug Mar 07 '19 at 03:11

score 0 · Answer 2 · answered Mar 06 '19 at 06:34

0

Try dumping the data into a staging table and then use STRINGAGG() function to concatenate the data into the format you want and move it to the destination table.

answered Mar 06 '19 at 06:34

Srikar mogaliraju

215
1
10

SSIS reading flatfile header column

2 Answers2