SSIS =- How to process an CSV input file with a va

问题:

I need to be able to process an input file where there are 3 types of records designated by the value in the 1st column, RecordType:

H=header record
D=Detail
T=Trailer

I suppose that I would like to transfer all of the detail records into a dest db table. I suppose the header and trailer records, which contain control info specifying when the file was created (header info) and record count/check sum info (in the trailer) should be transferred into separate tables, too.

H,2011-06-02
D,value1,value2,value3,1
D,value1,value2,value3,2
D,value1,value2,value3,3
D,value1,value2,value3,4
T,4, 10

being an SSIS rookie, the only way I can think of, is to process the input file as a fixed length file of 3 columns. col 1 = Pos 1-1 col 2 = Pos 2-2 (the comma separator, a throw-away column) col 3 = the data

Then ,

• transfer the data into a 3 column table.
• Export the data to new files based on record type.
• Reprocess as separate file

I hope not to have to do this, as this is ugly.

My preferred approach would be to adjust the format as follows. (My comma counts may be off, but you probably get the idea-

"RecType","CreateDate","Value1","Value2","Value3","Value4","RowCount","CheckSum"
H,2011-06-02,,,,,,,,
D,,value1,value2,value3,1,,
D,,value1,value2,value3,1,,
D,,value1,value2,value3,1,,
T,,,,,,,,4,10

Unfornately, changing the file format is not an option.

回答1:

Unfortunately this is a common problem. And only way i've found around it is to configure a flat file connection manager with a single varchar column. Put the entire row into the string column.

Then use a conditional split to distingush between header/footer/data row, and direct each type to separate outputs

You then need to either write the data to a file and then do another connection manager to it and process it, or use a derived column to split up the single string (varchar) into the multiple columns using string manipulation, string split or substring etc etc

回答2:

You can configure the connection manager to import the file as a single row and then add conditional split to split the records based on record-type. Then you can add script components for each type of output from split transform. These script components can be used to split the input into required output columns. Then, you can add separate destinations to insert records into different tables.