Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error using Sequence Container Expand / Collapse
Author
Message
Posted Tuesday, September 2, 2014 7:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 2, 2014 5:41 PM
Points: 23, Visits: 109
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?
Post #1609866
Posted Wednesday, September 3, 2014 12:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:20 AM
Points: 13,238, Visits: 11,014
Is MSDTC configured correctly?



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 #1609879
Posted Wednesday, September 3, 2014 12:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 2, 2014 5:41 PM
Points: 23, Visits: 109
I have tried starting OFF & ON few times the service - Distributed Transaction Coordinator. Are you asking this?
Post #1609886
Posted Wednesday, September 3, 2014 1:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:20 AM
Points: 13,238, Visits: 11,014
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.




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 #1609889
Posted Wednesday, September 3, 2014 6:07 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 2, 2014 5:41 PM
Points: 23, Visits: 109
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.
Post #1610321
Posted Thursday, September 4, 2014 12:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:20 AM
Points: 13,238, Visits: 11,014
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




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 #1610381
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse