在SSIS中将制表符倾斜的列视为批量插入 [英] Treating a tab-delimted column as a bulk insert in SSIS

问题描述

我正在导入具有以下格式的平面文件:

I am importing a flat file with the following format:

H(tab)OrderNumber(tab)CustomerNumber(tab)ERPMessage
D(tab)OrderNumber(tab)ItemNumber(tab)ItemDescription(tab)ItemPrice(tab)Qty
D(tab)OrderNumber(tab)ItemNumber(tab)ItemDescription(tab)ItemPrice(tab)Qty
.
.
.

我正在使用格式文件将文件批量加载到如下所示的登台表中:

I am BULK LOADing the file using a format file to a staging table that looks like this:

RecordType varchar(1)
RecordDetail varchar(MAX)

所以当它到达我的暂存表时，它看起来像这样:

so when it hits my staging table, it looks like this:

RecordType | RecordDetail
----------------------------------------------------------
H          | OrderNumber(tab)CustomerNumber(tab)ERPMessage
D          | OrderNumber(tab)ItemNumber(tab)ItemDescription(tab)ItemPrice(tab)Qty
D          | OrderNumber(tab)ItemNumber(tab)ItemDescription(tab)ItemPrice(tab)Qty

在控制流中，我根据文件从我的ForEach循环中加载文件的子文件夹的名称(即Sub_1，Sub_2等)来设置变量(strSubfolder).

In my control flow, I set a variable (strSubfolder) based on the name of the subfolder the file is loaded from in my ForEach loop (ie: Sub_1, Sub_2, etc).

在我的数据任务中，我读取临时表并根据RecordType执行条件拆分，并根据strSubfolder变量创建派生列.我需要做的是将RecordDetail字段解析为其各自的Header(H)和Detail(D)表，并将strSubfolder作为每个表的派生列包括在内:

In my data task, I read the staging table and peform a conditional split based on the RecordType, and creating derived columns based on the strSubfolder variable. What I need to be able to do is parse The RecordDetail field into its respective Header (H) and Detail(D) tables, and include the strSubfolder as a derived column to each table:

[Header table]
OrderNumber | SubFolder | CustomerNumber | ERPMessage
-----------------------------------------------------

[Detail table]
OrderNumber | SubFolder | ItemNumber | ItemDescription | ItemPrice | Qty
------------------------------------------------------------------------

如何解析登台表的RecordDetail字段，从本质上将其像其自己的BULK INSERT一样对待?我会以完全错误的方式进行此操作吗?

How do I parse the RecordDetail field of my staging table, essentially treating it like its own BULK INSERT? Am I going about this in the entirely wrong way?

推荐答案

我建议使用派生列填充这7列，您可以将TOKEN()函数与SSIS条件运算符? ::

I suggest using derived columns to populate these 7 columns, you can use TOKEN() function with SSIS conditional operators ? ::

订单号

TOKEN([RecordDetail],"\t",1)

客户编号

[RecordType] == "H" ? TOKEN([RecordDetail],"\t",2) : NULL(DT_WSTR,50)

ERPMessage

[RecordType] == "H" ? TOKEN([RecordDetail],"\t",3) : NULL(DT_WSTR,50)

ItemNumber

[RecordType] == "D" ? TOKEN([RecordDetail],"\t",2) : NULL(DT_WSTR,50)

ItemDescription

[RecordType] == "D" ? TOKEN([RecordDetail],"\t",3) : NULL(DT_WSTR,50)

ItemPrice

[RecordType] == "D" ? TOKEN([RecordDetail],"\t",4) : NULL(DT_WSTR,50)

数量

[RecordType] == "D" ? TOKEN([RecordDetail],"\t",5) : NULL(DT_WSTR,50)

这篇关于在SSIS中将制表符倾斜的列视为批量插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！