SSIS 事务数据(不同的记录类型，一个文件) [英] SSIS transactional data (different record types, one file)

问题描述

有趣的是，我们正在评估 ETL 工具，用于预处理报表数据(例如水电费账单、银行对账单)以进行打印.

An interesting one, we're evaluating ETL tools for pre-processing statement data (e.g. utility bills, bank statements) for printing.

一些数据来自一个单一的平面文件，具有不同的记录类型.

Some of the data comes through in a single flat file, with different record types.

例如以01"作为第一个字段的记录类型将是地址数据.这将具有名称和地址字段.带有02"的记录类型将是带有余额和总计的汇总数据.记录类型03"将是报表中的一个行项目.

e.g. a record type with "01" as the first field will be address data. This will have name and address fields. A record type with "02" will be summary data, with balances and totals. Record type "03" will be a line item on the statement.

每条语句都会有一条01和02记录，还有多条03记录.我可以预先解析文件并将其拆分为 3 个文件以加载到表中，但这并不理想.

Each statement will have one 01 and 02 records, and multiple 03 records. I could pre-parse the file and split into 3 files for loading into a table, but this is less than ideal.

我们获取文件并对其进行一些操作(例如在地址记录中添加更多字段，并且可能进行一些总计/验证)，然后以几乎相同的格式发送文件(但是添加的额外字段)到我们的打印合成程序中.

We take the file and do a few manipulations on it (e.g. add in a couple more fields to the address record, and maybe do some totalling / validation), and then send the file in pretty much the same format (But with the extra fields added) to our print composition program.

您将如何在 SSIS 中执行此操作?

How would you do this in SSIS?

推荐答案

SSIS 中变体记录的一个大问题是您无法获得连接管理器帮助布局的任何好处，因为连接管理器可以只处理一个布局.

The big problem with variant records in SSIS is that you don't get any of the benefits of the connection manager helping with the layout, since the connection manager can only handle a single layout.

通常，您最终会得到一个 CRLF 终止的平面文件，其中包含两列:recordtype 和 recorddata.然后将条件拆分放入并解析不同路径上的每种类型的行.解析将必须拆分剩余的记录数据并将其放入列中并正常转换，无论是使用派生列转换还是脚本转换和潜在的转换转换.

So typically, you end up with a CRLF terminated flat file with two columns: recordtype and recorddata. Then you put the conditional split in and parse each type of row on different paths. The parsing will have to split up the remaining record data and put it in columns and convert as normal, either with a derived column transform or a script transform and potentially conversion transforms.

如果你有很多包要做，我会认真考虑编写一个自定义组件，它产生 3 个已经转换为目标类型的输出.

If you had a lot of packages to do, I would seriously consider writing a custom component which produced 3 outputs already converted to your destination types.

这篇关于SSIS 事务数据(不同的记录类型，一个文件)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！