CODEWITHSUNDEEP
transactionsssissql-server-2008-r2bids
Ramon Gonzalez
Ramon Gonzalez

Reputation: 251

Handling an SSIS Transaction

I have the following scenario on SQL Server 2008R2:

This process is to be done with transactions.

Below is what I'm thinking of doing in this case: SSIS Package Outline[1]

In regards to the above, the transfer of data from Server A to Server B is a simple data flow from source to destination. I would have to set the option to "RetainSameConnection" = true on Server B in this case.

My questions are as follows:

I know there is also MS DTC handling of transactions available for SSIS of which I am doing data gathering to present to Operations but do want to have another possible solution since this process could be prioritize to "now" very quickly.

Thank you all in advance.

Upvotes: 2

Views: 910

Answers (1)

Samuel Vanga
Samuel Vanga

Reputation: 501

Answers to your questions in order: Yes, Yes, Yes, and no. You're not missing anything.

Word of advice: set retaisameconnection to true on all the connection managers; it's good for performance. Make sure you name your transactions like begin tran A. Don't go anywhere near using SSIS built-in transactions - it's slow and complex.

Upvotes: 1

Related Questions