DTS Text File Destination - export different sorts of data into one file?

Hi,
I need to create a csv file from a DTS package containing header and detail row's which contain a different number of columns. The header must be followed by the order lines for that order. The detail lines hold no order number to match back to the header. Example below

The data is coming from 2 tables an order header and order details. If I use 2 separate Transform Data Tasks for the header and the details, I cannot find a way to merge the files together in a way where the details lines follow the header.

If I use 1 Transform Data Task I have additional blank columns added onto the end of the details lines to for the additional columns that are required in the header only.

I have tried Activex (sample below)on the header specific columns, example below, but cannot find a DTSTransformStat_ that will allow the row to be inserted and ignore the columns.

Can anyone suggested the best way of achieving this?

Function Main()
      DTSDestination("F4") = DTSSource("F4")
   
if isnull(DTSSource("F4").Value) then
 main = DTSTransformStat_SkipInsert
else
 Main = DTSTransformStat_Ok
end if
End Function

File Sample

"H","ORDER","NODUPE","XXX01","","","","","311470","0091372","240570","","RWIL100","","","","","","","","","",""
"L","BLFLWPET2","","336","","","","","0190153","","   1",""
"L","WLINSQQCSDBLIL","","81","","","","","0196173","","   2",""
"H","ORDER","NODUPE","XXX01","","","","","311471","0084567","240570","","RWIL200","","","","","","","","","",""
"L","BLFLWPET2","","445","","","","","0190153","","   1",""
"L","WLINSQQCSDBLIL","","82","","","","","0196173","","   2",""



Start Free Trial