Is it possible to force an error in an Integration Services data flow to demonstrate its rollback?

Question

I have been tasked with demoing how Integration Services handles an error during a data flow to show that no data makes it into the destination. This is an existing package and I want to limit the code changes to the package as much as possible (since this is most likely a one time deal).

The scenario that is trying to be understood is a "systemic" failure - the source file disappears midstream, or the file server loses power, etc.

I know I can make this happen by having the Error Output of the source set to Failure and introducing bad data but I would like to do something lighter than that.

I suppose I could add a Script Transform task and look for a certain value and throw an error but I was hoping someone has come up with something easier / more elegant.

Thanks,

Matt

Answer 1

mess up the file that you are trying to import by pasting some bad data or saving it in another format like UTF-8 or something like that

Answer 2

We always have a task at the end that closes the dataflow in our meta data tables. To test errors, I simply remove the ? that is the variable for the stored proc it runs. Easy to do and easy to fix back the way it was and it doesn't mess up anything datawise as our error trapping then closes the the data flow with an error. You could do something similar by adding a task to call a stored proc with an input variable but assign no parameters to it so it will fail. Then once the test is done, simply disable that task.

Answer 3

Hi,

Could we try with transaction level property of the package. On failure of the data flow it will revert all the data from the target. On successful dataflow only it will committ the data to target otherwise it will roll back the data from target.

thanks prav

Answer 4

Data will make it to the destination if it is not running as a transaction. If you want to prevent populating partial data you have to use transactions. Then there is an option to set the end result of a control flow item as "failed" irrespective of the actual result but this is not available in data flow items. You will have to either produce an actual error in the data or code in a situation that will create an error. There is no other way...