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.
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.