在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屋!