batch processing - SSIS Process records between header and trailer in flat file -

batch processing - SSIS Process records between header and trailer in flat file -

i have ssis bundle uses conditional split batch header or trailer in line delimited flat file, take first line, , grab error code it. if error code > 0, write out of normal records in between batch header , trailer study error code. otherwise write out normal records errors codes. here's illustration looks like:

//no batch level error 00000bh 00123nrnormalrecorddata 00000nrnormalrecorddatanoerror 00000bt

which like:

╔═══════════╦══════════════════╗ ║ error ║ record info ║ ╠═══════════╬══════════════════╣ ║ 123 ║ normalrecorddata ║ ╚═══════════╩══════════════════╝

and:

//batch level error 05555bh 00000nrnormalrecorddata 00000nrnormalrecorddata 00000bt

which like:

╔═══════╦═════════════════════════╗ ║ error ║ record info ║ ╠═══════╬═════════════════════════╣ ║ 5555 ║ normalrecorddata ║ ║ ║ ║ ║ 5555 ║ normalrecorddata ║ ╚═══════╩═════════════════════════╝

my problem is multiple batches screws (there utilize ever 1 batch). want following

//multi batch 00000bh 00123nrnormalrecorderror 00000nrnormalrecord 00000bt 00000bh 00000srsecondaryrecordtype //want ignore batches no nr normal records 00000bt 05555bh 00000nrnormalrecord 00000nrnormalrecord 00000bt

due saving batch level error variable , checking if it's null when write records out study incorrectly like:

╔═══════╦═════════════════════╗ ║ error ║ record info ║ ╠═══════╬═════════════════════╣ ║ 5555 ║ normalrecorderror ║ ║ 5555 ║ secondaryrecordtype ║ ║ 5555 ║ normalrecord ║ ║ 5555 ║ normalrecord ║ ║ 5555 ║ normalrecord ║ ╚═══════╩═════════════════════╝

when want like:

╔═══════╦═══════════════════╗ ║ error ║ record info ║ ╠═══════╬═══════════════════╣ ║ 123 ║ normalrecorderror ║ ║ 5555 ║ normalrecord ║ ║ 5555 ║ normalrecord ║ ╚═══════╩═══════════════════╝

this because logic looks little like:

store batch level error normal records error, unless batch level error > 0 them write rows batch level error if there 1 or write normal rows error (this case work intended, because batch level variable not populated)

my first thought conditional split. however, allow me status @ row level, need context of rows have come prior.

how tackle this?

you utilize script component transformation parse column , add together rows based on conditions. header error can stored in variable declared outside input0_processinputrow method. here steps used:

i used info above create single column flatfile column name data add script component transformation check info input column add new output called recordoutput add columns out put: error int, recorddata string

the code:

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 { int error; public override void input0_processinputrow(input0buffer row) { //gets row type string rowtype = row.data.substring(5, 2); //only sets error variable if header record if (rowtype == "bh") { error = convert.toint32(row.data.substring(0, 5)); } //only adds record nr rows if (rowtype == "nr" && (error > 0 || convert.toint32(row.data.substring(0, 5)) > 0)) { recordoutputbuffer.addrow(); if (error > 0) { recordoutputbuffer.error = error; } else { recordoutputbuffer.error = convert.toint32(row.data.substring(0, 5)); } recordoutputbuffer.recorddata = row.data.substring(7, row.data.length - 7); } } }

here component looks like:

here results:

ssis batch-processing flat-file ssis-2012