Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Issue with TransactionOption of SSIS


Issue with TransactionOption of SSIS

Author
Message
Tarun Jaggi
Tarun Jaggi
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1323 Visits: 692
Hi All,

I created a SSIS package which contains two "Execute SQL Tasks" components - one inserts/updates the records in Table1 of DB1 and other inserts/updates the records in Table2 of DB2.

I have also enabled the Transaction by setting the TransactionOption = Required at the package level and TransactionOption = Supported at the component level. So either all operations (done by both EST) or none should commit.

In our dev and test env it is working fine. But on production (with cluster MSDTC), few times it throws an error:
Executing the query "EXEC [etl].[uspRptPopulateFactCourseUsage] @Linked..." failed with the following error: "The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Any suggestions would be useful!!
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16334 Visits: 13198
Are their problems with the query?
What happens if you run them in SSMS (if possible)? Do they succeed?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Tarun Jaggi
Tarun Jaggi
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1323 Visits: 692
No issue with the query.
As I mentioned this error doesn't comes very often. 10 in 160 (less than 10%) times this error comes.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16334 Visits: 13198
Tarun Jaggi (11/6/2012)
No issue with the query.
As I mentioned this error doesn't comes very often. 10 in 160 (less than 10%) times this error comes.


I see you use the parameter mapping if I'm not mistaken. Any possible issues with that? Usually this gives those types of errors.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Tarun Jaggi
Tarun Jaggi
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1323 Visits: 692
As both Execute SQL Tasks are pointing to different databases.
Those Execute SQL Tasks calls stored procedures (that populate the data into the corressponding tables).

Now what I want is lets say if the first Execute SQL Tasks (EST1) completes sucessfully and then the second Execute SQL Tasks (EST2) get failed then all the operations done by EST1 will also get rollbacked. For that I used TransactionProperty of SSIS which worked fine at dev and test env but on Prod (cluster MSDTC) it throws this MSDTC error few times.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search