roll back in for each container

TomWhite_jr

Hi,

I'm using data flow as flat file source -> derived column ->Data Conversion-> oledb destination

I have a fifty thousand record in a delimited file. while processing i got error in 45000th row. In database only 40000 records are there(why is there no all 45000 records). Is there any way to roll back all the 40000 records.

Jamie Thomson

TomWhite_jr wrote:

Hi, I'm using data flow as flat file source -> derived column ->Data Conversion-> oledb destination I have a fifty thousand record in a delimited file. while processing i got error in 45000th row. In database only 40000 records are there(why is there no all 45000 records). Is there any way to roll back all the 40000 records.

Tom,

The SSIS dataflow processes data in buffers. A buffer contains a fixed set of rows and usually is about 10000 rows (usually slightly less than that actually - for reasons I won't go into right now).

By default, the OLE DB Destination will commit rows a buffer at a time so if one row fails, all rows in that buffer fail. You can change this behaviour by setting "Rows Per Batch"=1. This will mean each row will get committed a row at a time so at the point of failure you will get 44999 rows in the destination. It will be slo-o-o-o-o-w though!

-Jamie

TomWhite_jr

thanks jamie..

Is there anyway to roll back all the records inserted from the flat file.

or can we trim all the inserted record from the delimited to avoid repeattion of the data in database while running the file again after correction

Tom

Larry Charlton

One thing we do regularly that has nothing to do with SSIS in particular is load unknown or untrusted data into a load table first. This allows us to validate that we can load it, ensure that the information completes transmission if comming over the wire, validate data, and more, all prior to ever affecting the state of the current production data.

jwelch

TomWhite_jr wrote:

thanks jamie.. Is there anyway to roll back all the records inserted from the flat file. or can we trim all the inserted record from the delimited to avoid repeattion of the data in database while running the file again after correction Tom

You could enable transactions on the dataflow, so that if if fails, all the records are rolled back. Or you redirect error rows to a new flat file, and then use that one when re-importing.

TomWhite_jr

hi

How can we select the error rows and copy to new flat file.

Tom

jwelch

On the destination, configure the Error Output to redirect rows on errors. Then connect the red output on the destination component to a flat file destination.

TomWhite_jr

Thanks.. jwelch