1

This might be a dumb question, but I have a data flow task, OLE DB Source and Flat File Destination.

In my OLE DB Source I'd like to have an UPDATE statement, and then a SELECT. When I do that, I don't seem to get any rows written to my Flat File. I'm doing this, by the way, to help ensure that my update does not happen if my select fails. (I was going to put the two statements between a BEGIN TRAN and COMMIT.)

Unfortunately I can't use SSIS transactions because the server the SSIS is running on doesn't have the DTC service running.

Brad Fox
  • 685
  • 6
  • 19
J Brun
  • 1,246
  • 1
  • 12
  • 18
  • Please give some detail of what sort of update statement you're talking about. What exactly doesn't work? – John Saunders Apr 14 '12 at 17:43
  • Sorry I was not clear. I want the Select/File operation and the Update operation to be part of one atomic transaction. So, if the update fails, the file is not created, and vice versa. I'm assuming one can not do that – J Brun Apr 20 '12 at 20:02

2 Answers2

0

Assuming your desired sequence is SELECT -> UPDATE -> WRITE_TO_FLAT_FILE, use your OLE DB Source to do the select, then an OLE DB Command to do the update. Finally, use your flat file destination to write the data out.

In the OLE DB Source, set the Error Output to either 'fail component' or 'redirect rows'; either one will allow you to avoid the subsequent update command.

Geoff
  • 8,551
  • 1
  • 43
  • 50
0

you have to do your update on your control flow.

Add a execute SQL task with the update command, drag the green arrow from the task to the data flow component and in the data flow you will have your oleDBSource and your flat file destination

Diego
  • 34,802
  • 21
  • 91
  • 134