Error using Sequence Container

Question

Error using Sequence Container

smart_analyst

Ten Centuries

Points: 1037
More actions
September 2, 2014 at 7:43 pm

#292826

Hi,
I created 4 Execute SQL task and each one is adding single row to the table. When I execute the package it is working fine. If I add these 4 tasks to Sequence container. The first Execute SQL task fails with the connection error. I checked the connection is fine and works fine without sequence container. I am using TransactionOption as Required for Sequence container.
Error-
[Execute SQL Task] Error: Failed to acquire connection "ABCDDB06\DEVDB007.dbBRIMS2". Connection may not be configured correctly or you may not have the right permissions on this connection.
Does anyone know why is the execution failing?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

Is MSDTC configured correctly?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

smart_analyst Ten Centuries Points: 1037 More actions · Answer 2

I have tried starting OFF & ON few times the service - Distributed Transaction Coordinator. Are you asking this?

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 3

Yes. But it needs to work over the different machines.

I have never configured it myself though, I try to avoid it at all costs.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

smart_analyst Ten Centuries Points: 1037 More actions · Answer 4

I tried again with new project and new package. All 4 Execute SQL package works outside Sequence container but, inside the 1st Exec SQL package fails.....

Does anyone else know? It looks like there should be a very simple solution for this.

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 5

smart_analyst (9/3/2014)
I tried again with new project and new package. All 4 Execute SQL package works outside Sequence container but, inside the 1st Exec SQL package fails.....
Does anyone else know? It looks like there should be a very simple solution for this.

The most simple solution is to turn off the transaction property.

If the transaction is needed, you need to dive deeper into MSDTC configuration.

Another option is to create the transaction yourself.

SSIS Transactions without MSDTC[/url]

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP