SSIS是否可以支持加载每行中具有不同列长的文件？ [英] Can SSIS support loading of files with varying column lengths in each row?
问题描述
目前，我每天收到约75万行的文件，每行开头都有3个字符的标识符。
Currently I receive a daily file of around 750k rows and each row has a 3 character identifier at the start.
对于每个标识符，列数可以更改但特定于标识符（例如SRH总是有6列，AAA总是有10列，依此类推）。
For each identifier, the number of columns can change but are specific to the identifier (e.g. SRH will always have 6 columns, AAA will always have 10 and so on).
我希望能够自动执行此文件通过SSIS进入SQL表。
I would like to be able to automate this file into an SQL table through SSIS.
该解决方案当前是使用VBA在MSACCESS中构建的，只是使用CASE语句遍历记录集，然后将记录写入相关表中。
This solution is currently built in MSACCESS using VBA just looping through recordsets using a CASE statement, it then writes a record to the relevant table.
我一直在阅读SSIS中的BULK INSERT，BCP（带有格式文件）和条件拆分的方法，但是我似乎总是在加载时遇到第一个障碍
I have been reading up on BULK INSERT, BCP (w/Format File) and Conditional Split in SSIS however I always seem to get stuck at the first hurdle of even loading the file in as SSIS errors due to variable column layouts.
数据文件是用竖线分隔的，看起来与下面类似。
The data file is pipe delimited and looks similar to the below.
AAA|20180910|POOL|OPER|X|C
SRH|TRANS|TAB|BARKING|FORM|C|1.026
BHP|1
*BPI|10|16|18|Z
BHP|2
*BPI|18|21|24|A
（*我加了*表示这些是父记录的子记录，在这种情况下BHP可以在其下有多个BPI记录）
(* I have added the * to show that these are child records of the parent record, in this case BHP can have multiple BPI records underneath it)
我希望能够将TXT文件加载到暂存表中，然后可以编写TSQL以遍历记录并将它们解析到它们的相关表（AAA-tblAAA，SRH-tblSRH ...）
I would like to be able to load the TXT file into a staging table, and then I can write the TSQL to loop through the records and parse them to their relevant tables (AAA - tblAAA, SRH - tblSRH...)
推荐答案
我认为您应该将每一行读为DT_WSTR类型且长度= 4000的一列，那么您需要实现相同的逻辑在脚本组件（VB.NET / C＃）中使用vba编写的文章中，有类似的文章可以为您提供一些见解：
I think you should read each row as one column of type DT_WSTR and length = 4000 then you need to implement the same logic written using vba within a Script component (VB.NET / C#), there are similar posts that can give you some insights:
- 无法识别SSIS衣衫CR的文件CRLF
- SSIS将LF设置为CRLF时读取LF作为终止符
- 如何加载混合记录类型固定宽度文件？并且文件还包含两个标头
- SSIS平面文件-CSV格式不适用于多行文件
- 如何跳过ssis平面文件源中的错误行
- SSIS ragged file not recognized CRLF
- SSIS reading LF as terminator when its set as CRLF
- How to load mixed record type fixed width file? And also file contain two header
- SSIS Flat File - CSV formatting not working for multi-line fileds
- how to skip a bad row in ssis flat file source
这篇关于SSIS是否可以支持加载每行中具有不同列长的文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
