How can I perform one transaction per input row?

Question

I have a dataflow that has an oledb data source. I then have a script component that performs three web service calls, this has two outputs that are (in effect) synchronous to the input row (one output row per input row), and one output that is asynchronous (outputs many rows for each input row). Each output goes to a sql update command. There is also an error log output (also asynch).

I wish to wrap the outputs in a transaction somehow, so that for each input row, if the row is not completely processed and updated, then none of the updates for that input row succeed.

From what I can tell, the way this works is that the scripts process each input row and output everything to a buffer before moving on to the update commands. I want, instead to perform all updates on a per-row basis, committing changes when each input row has been processed and updated.

How can I structure my dataflow to achieve this?

I'm thinking perhaps of executing the source, then running the records through a foreach container, but i'm unsure of how I would chain multiple DataFlows together that way via a ForEach container.

score 0 · Answer 1 · answered Mar 27 '13 at 21:41

I suspect the only way is to put everything, including the updates, in a single script. SSIS does not expose enough of its dataflow buffering strategy to give you that fine a level of control. In fact, it goes to great lengths to speed things up within a pipeline, which is just the opposite of the throttling strategy you're looking for. (See Data Flow Performance Features on MSDN for a discussion of what tuning features are available.)

Alternatively, you could build a completely custom destination pipeline component. Given how painful debugging script components is in SSIS 2008, that might even be a preferable approach.

Thanks for the info, however, I decided to change my strategy and moved everything into one script with several outputs. I completely changed my question, and I think you were answering my previous question. — Erik Funkenbusch, Mar 27 '13 at 22:03

score 0 · Accepted Answer · answered Mar 28 '13 at 20:15

The solution here is that I create a Data Flow task which selects the users into a recordset, which is placed in a variable.

I then use a For Each container to read the recordset, and put the username in a variable. In side the For Each, I have a dataflow that has an OLEDB source that uses the variable as a parameter to select the users again (this results in a lookup for each iteration of the dataset, but that's ok).

This feeds into my script.

Additionally, I place Execute SQL Tasks before and after the package in the for each loop so I don't have to use DTC transactions, and can process each input row as a separate transaction.

How can I perform one transaction per input row?

2 Answers2