Issue with TransactionOption of SSIS

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » Issue with TransactionOption of SSIS

Issue with TransactionOption of SSIS

Rate Topic

Display Mode

Topic Options

Author

Message

Tarun Jaggi

Posted Monday, November 05, 2012 10:43 PM

SSC Eights!

Group: General Forum Members
Last Login: Monday, May 06, 2013 2:52 AM
Points: 973, Visits: 463

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

Post #1381374

Koen Verbeeck

Posted Monday, November 05, 2012 11:51 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 6:54 AM
Points: 9,364, Visits: 6,462

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

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1381397

Tarun Jaggi

Posted Tuesday, November 06, 2012 12:04 AM

SSC Eights!

Group: General Forum Members
Last Login: Monday, May 06, 2013 2:52 AM
Points: 973, Visits: 463

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.

Post #1381402

Koen Verbeeck

Posted Tuesday, November 06, 2012 12:07 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 6:54 AM
Points: 9,364, Visits: 6,462

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

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1381404

Tarun Jaggi

Posted Tuesday, November 06, 2012 12:14 AM

SSC Eights!

Group: General Forum Members
Last Login: Monday, May 06, 2013 2:52 AM
Points: 973, Visits: 463

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.

Post #1381410

« Prev Topic | Next Topic »

Permissions