SSIS =- 如何根据记录类型(标题、详细信息、预告片)处理具有可变列数的 CSV 输入文件 [英] SSIS =- How to process an CSV input file with a variable number of columns based on record type (Header, Detail, Trailer)

问题描述

我需要能够处理一个输入文件，其中有 3 种类型的记录由第一列中的值指定，RecordType:

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

我想我想将所有详细记录传输到 dest db 表中.我认为包含指定文件创建时间的控制信息(标题信息)和记录计数/校验和信息(在预告片中)的标题和结尾记录也应该传输到单独的表中.

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

作为SSIS菜鸟，我能想到的唯一方法就是将输入文件作为3列的定长文件进行处理.第 1 列 = 位置 1-1col 2 = Pos 2-2(逗号分隔符，丢弃列)col 3 = 数据

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

然后，

• 将数据传输到 3 列表中.
• 根据记录类型将数据导出到新文件.
• 重新处理为单独的文件

我希望不必这样做，因为这很丑陋.

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.

推荐答案

不幸的是，这是一个常见问题.我找到的唯一方法是使用单个 varchar 列配置平面文件连接管理器.将整行放入字符串列中.

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

然后您需要将数据写入文件，然后对其执行另一个连接管理器并对其进行处理，或者使用派生列将单个字符串 (varchar) 拆分为使用字符串操作、字符串拆分的多个列或子串等

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

这篇关于SSIS =- 如何根据记录类型(标题、详细信息、预告片)处理具有可变列数的 CSV 输入文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！