sql server 2012 - Combine Multiple Record Types in SSIS Data Flow -

i can use integration services extract , transform data is, example, contained in single type of fixed format record in import flat file. can handle multiple types of fixed format records in single file, long able send them different destinations.

but if need associate 2 (or more) records input file each record going single destination? way know these records belong occur next each other in input file. data flow process 1 record after another. can't use ssis package variable count records, because package variables updated after data flow has finished.

here sample input file:

hdr00120140501       dtl001joan     0.00  dtl002     30.00 abc dtl001dave    11.00  dtl002     21.85 def dtl001bert    50.00  dtl002      0.00 ghi trl001               

i need combine data dtl001 , dtl002 detail lines single output record. in addition, need include information hdr001 header line (in case date value). destination table looks like:

create table testimport (   id int identity(1,1) not null,   importdate datetime null,   name char(4) null,   amount1 decimal(18, 2) null,   amount2 decimal(18, 2) null,   desc char(3) null ) 

the 6 input detail records, plus header, yield 3 records in destination table:

importdate name amount1 amount2 desc ---------- ---- ------- ------- ----- 2014-05-01 joan 0.00    30.00   abc 2014-05-01 dave 11.00   21.85   def 2014-05-01 bert 50.00   0.00    ghi 

this solved problem posted assist may encounter similar problem.

the detail records can combined using combination of script, conditional split, , merge join components.

the script component adds detail record count incremented each dtl001 record encounters. provides detail record count column can used sorting , joining dtl001 , dtl002 records next each other in input file.

the flat file connection manager , source used break each input record recordtype, recordsubtype, , recorddata columns. instance, first 3 records broken following pieces:

"hdr", "001", "20140501      " "dtl", "001", "joan     0.00 " "dtl", "002", "     30.00 abc" 

next, derived header columns component adds headerrecordcount, detailrecordcount, , importdate columns. these unpopulated, provide place script component store header , detail counts, information header needed each row of destination table. script handle files multiple sections, each section has new hdr001 header record:

using system; using system.data; using microsoft.sqlserver.dts.pipeline.wrapper; using microsoft.sqlserver.dts.runtime.wrapper;  [microsoft.sqlserver.dts.pipeline.ssisscriptcomponententrypointattribute] public class scriptmain : usercomponent {     static int _headerrecordcount = 0;     static int _detailrecordcount = 0;     static datetime _importdate = datetime.minvalue;     public override void input0_processinputrow(input0buffer row)     {         if (row.recordtype == "hdr" && row.recordsubtype == "001")         {             _headerrecordcount = _headerrecordcount + 1;             _importdate = datetime.parseexact(row.recorddata.substring(0, 8), "yyyymmdd", system.globalization.cultureinfo.invariantculture);         }         else if (row.recordtype == "dtl" && row.recordsubtype == "001")         {             _detailrecordcount = _detailrecordcount + 1;         }         row.headerrecordcount = _headerrecordcount;         row.detailrecordcount = _detailrecordcount;         row.importdate = _importdate;     } } 

the conditional split component sends dtl001 , dtl002 records down separate paths. split conditions are:

recordtype == "dtl" && recordsubtype == "001" recordtype == "dtl" && recordsubtype == "002" 

these paths each sorted on detailrecordcount column, , merge join combines them using column. of records 001 path passed through merge join output, , recorddata column 002 path passed separate output column (e.g., recorddata002).

now contents of both flat file records available in single data flow record. derived detail columns component used pull out required information, , resulting columns sent destination table.