Executing a whole container in one transaction

Hello all,

I am trying to build a package running a container that includes several tasks: 2 Execute SQL tasks and a data flow task. I would like to execute this container in a single transaction, which means that if any of the tasks fail, no changes are been made by the container execution.

To achieve this, I have followed the instructions of how to configure the DTC on the server and locally, and set the container's TransactionOption Property to "Required".

The problem I encounters is that when using a dataflow task in the container, the running process runs OK until it reaches the dataflow task, then it just gets stuck (marking the task in yellow). When removing the dataflow task, left with only Execute SQL tasks everything works fine.

Any idea?

Thanks,

Liran

does the dataflow task get "stuck" if you execute it by itself?

Note that some operation in dataflow - caching for lookup - may take a long time - so how long does it stay stuck ?
|||

Liran R wrote:

Hello all,

I am trying to build a package running a container that includes several tasks: 2 Execute SQL tasks and a data flow task. I would like to execute this container in a single transaction, which means that if any of the tasks fail, no changes are been made by the container execution.

To achieve this, I have followed the instructions of how to configure the DTC on the server and locally, and set the container's TransactionOption Property to "Required".

The problem I encounters is that when using a dataflow task in the container, the running process runs OK until it reaches the dataflow task, then it just gets stuck (marking the task in yellow). When removing the dataflow task, left with only Execute SQL tasks everything works fine.

Any idea?

Thanks,

Liran

You'll probably find that some operations are blocking others.

Are you using SQL Server? If so, execute sp_who2 and look in the Blk column for SPID=-2.

-Jamie

|||Liran,

Is the behaviour same when you set the TransactionOption to "Supported" or "Not Supported", i.e. when not using DTC?

Also, take a look at the data flow window for that DFT to see which tasks are in yellow.|||

Jamie Thomson wrote:

You'll probably find that some operations are blocking others.

Are you using SQL Server? If so, execute sp_who2 and look in the Blk column for SPID=-2.

-Jamie

Yes, there is indeed a blocking:

SPID: 67

Status: SUSPENDED

Login: liran

HostName: Liran-Computer

DBName: STG

Command: select * from [dbo].[STG_FACT_A]

Wait type: LCK_M_IS

BlkBy: 64

SPID: 64

Status: Sleeping

Login: LinkAdmin

HostName: DW

DBName: master

Command: sys.sp_getschemalock;1

So I understand process 64 is locking the schema preventing process 67 from running.

Why is it happening? why only when using dataflow?

This behavior only happens when the container is set to "Required" in the transactionOption, and stuck on the dataflow (yellow).

Thanks,

Liran