0

I am using SSIS 2008.

Let a person be defined by PersonID, a number. In my SSIS package, for each person, I first load some info into Table1 (with execute sql task) and then some info into Table2 (with execute sql task). I want this entire process to be considered as a transaction. That is, the transaction is complete only when information for A person is loaded both into Table1 and Table2. If we could not load info into Table2, then info already loaded in Table1 should not be committed.

How do I do this ?

Thanks.

Steam
  • 9,368
  • 27
  • 83
  • 122
  • 3
    Place both of your loads into a sequence container set transaction option to required in the properties tab. Done. – Zane Nov 08 '13 at 20:11
  • This article expands on @Zane's comment: http://www.mssqltips.com/sqlservertip/1585/how-to-use-transactions-in-sql-server-integration-services-ssis/ – mr.Reband Nov 08 '13 at 20:12
  • Thanks @mr.Reband I would have done a better answer but I can't post images at work... – Zane Nov 08 '13 at 20:15
  • @mr.Reband - Thanks. The essence of that article is given by Zane. That is what i was really looking for. – Steam Nov 08 '13 at 20:26
  • @Zane - I suggest that you put your comment as an answer. I will select it. Thanks. – Steam Nov 08 '13 at 20:27

1 Answers1

4

Place both of your loads into a sequence container set transaction option to required in the properties tab. Done. Included is an article in case you need additional details.

Zane
  • 4,129
  • 1
  • 21
  • 35
  • Thanks. But none of my connections work now. Can't save the password problem. I put passwords, tested connections and then encrypt all with password. Still cannot use my connections. Why is this happening ? :( – Steam Nov 08 '13 at 21:48
  • Do you mean your connection strings? – Zane Nov 08 '13 at 21:50
  • You may want to take some screenshots and post some more details about your package I will be able to provide better assistance then. – Zane Nov 08 '13 at 21:51
  • Yes, all the stuff you see in an OLE DB connections editor - servername, username, password, save password etc. – Steam Nov 08 '13 at 21:52
  • WARNING !!! There is a problem in using this method - See here - http://stackoverflow.com/questions/19869276/cannot-connect-to-database-after-putting-tasks-in-sequence-container – Steam Nov 09 '13 at 22:25
  • Well there is less of a problem using this method as there is a problem with your particular set up of connection strings. – Zane Nov 12 '13 at 15:57