1

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

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).

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
------------------------------------------------------------------------

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?

Hadi
  • 36,233
  • 13
  • 65
  • 124
Kulstad
  • 79
  • 8

1 Answers1

1

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

OrderNumber

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

CustomerNumber

[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)

Qty

[RecordType] == "D" ? TOKEN([RecordDetail],"\t",5) : NULL(DT_WSTR,50)
Hadi
  • 36,233
  • 13
  • 65
  • 124
  • Thank you very much for this, it's exactly what I needed. I had to adjust my staging table RecordDetail field to 4000 (instead of MAX), as SSIS kept interpreting this field as DT_TEXT, and throwing errors on the TOKEN function "Error at Parse order file (Header, Detail) [Derived Column [55]]: The function "TOKEN" does not support the data type "DT_TEXT" for parameter number 1. The type of the parameter could not be implicitly cast into a compatible type for the function. To perform this operation, the operand needs to be explicitly cast with a cast operator." – Kulstad Aug 09 '19 at 20:33
  • Try using `(DT_WSTR,4000)[RecordDetail]` or `(DT_STR,1252,4000)[RecordDetail]` – Hadi Aug 09 '19 at 20:48
  • @Kulstad check the following link: https://stackoverflow.com/questions/57434730/ssis-conversion-text-stream-dt-text-to-dt-wstr – Hadi Aug 09 '19 at 20:49
  • I just ran across an issue where the TOKEN function is treating consecutive delimiters as a single delimiter. According to the accepted answer on [this page](https://stackoverflow.com/questions/12917758/why-does-ssis-token-function-fail-to-count-adjacent-column-delimiters), it is a "fault" in the underlying C++ function: strtok – Kulstad Aug 16 '19 at 17:25