David,

Matt David had a look at your package and gave the following comments:

Kirk,

I ran this project and as far as I can tell everything worked.  I loaded with patient no dob.  I ran it the first time and all 19 were inserted with no errors.  On his example one of his components went red.  That means that the pipeline shut down prematurely and we make no guarantee that all data was inserted/updated correctly.  If a component fails then the pipeline fails.  Row redirection would mean that the task would not turn red for a redirectable error. The pipeline never finished so all results should be intermediary.  The only thing I initially found strange was that some rows were inserted.  I then realized that he was using an oledb command not an oledb dest to insert the rows so since the dataflow itself wasn’t transactioned then each insert is separate and that is why some rows were there.  Please note that I could not get this failure to occur but this could be because I had to make some changes to get the package to run so perhaps I affected the result as well.  In any event since he had a red component the pipeline failed and therefore the data is not lost it just wasn’t written due to the failure.  This is the expected behavior in this scenario.  If he enabled transaction then all the data should be rolled back.

Given this input, you might want to check a few things.

Is the dataflow task in a transaction? You can check that by selecting the Dataflow task in the control flow view and looking at the transaction option property. Does it say suppported or required?

Are you using an OLEDB Command destination? That component does an operation on each row as it goes through, so you may be causing a failure in the component itself.

Check to make sure that failed rows get redirected and don't fail the component on the component that's turning red.

When you get the error that causes the transform to turn red, go to the output window (on the main menu select View | Windows | Output Window) or in the progress tab. You should get more information about the error. If you can also turn on logging to get the full log, that would help.

It sounds as though there may be some problems with the package. Also, you're struggling with understanding the problem, regardless of the ultimate outcome of the issue, this isn't good. A better error would help, or making the error easier to find perhaps. These are some areas on which we're focusing for future releases.

Please let us know if any of the above observations or suggestions resolved the problem.

Thanks,

K