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

execute long running transaction - fire and forget

How can I execute a long running transaction using something similar to the fire and forget pattern?

I intend to start the execution of a very long stored proc from within IIS. I would like to execute a sql script that will start the job and return immediately so that it doesn't hold an IIS thread.

You can create an on-demand SQLAgent job that calls your SP and fire the job. See BOL for more details on how to create a SQLAgent job.

|||

Hi

Try something like that.

Philippe

Protected Sub RunJob()

' Dim rowCount As Integer

Dim previousConnectionState As ConnectionState

Dim conn As New SqlConnection("server=datamart;integrated security=true;" + "database=MSDB")

Dim cmd As New SqlCommand("msdb.dbo.sp_start_job @.job_name ='Distributor NPD Stocking data and cube'", conn)

previousConnectionState = conn.State

Try

If conn.State = ConnectionState.Closed Then

conn.Open()

End If

cmd.ExecuteNonQuery()

Finally

If previousConnectionState = ConnectionState.Closed Then

conn.Close()

End If

End Try

End Sub

execute long running transaction - fire and forget

How can I execute a long running transaction using something similar to the fire and forget pattern?

I intend to start the execution of a very long stored proc from within IIS. I would like to execute a sql script that will start the job and return immediately so that it doesn't hold an IIS thread.

You can create an on-demand SQLAgent job that calls your SP and fire the job. See BOL for more details on how to create a SQLAgent job.

|||

Hi

Try something like that.

Philippe

Protected Sub RunJob()

' Dim rowCount As Integer

Dim previousConnectionState As ConnectionState

Dim conn As New SqlConnection("server=datamart;integrated security=true;" + "database=MSDB")

Dim cmd As New SqlCommand("msdb.dbo.sp_start_job @.job_name ='Distributor NPD Stocking data and cube'", conn)

previousConnectionState = conn.State

Try

If conn.State = ConnectionState.Closed Then

conn.Open()

End If

cmd.ExecuteNonQuery()

Finally

If previousConnectionState = ConnectionState.Closed Then

conn.Close()

End If

End Try

End Sub

Execute Command No transaction

How would I execute a stored procedure and turn off the transaction.
We do not care on our end if the data makes it over. So we dont need
transaciton.
Any Idea?
ThanksChris Calzaretta wrote:
> How would I execute a stored procedure and turn off the transaction.
> We do not care on our end if the data makes it over. So we dont need
> transaciton.
> Any Idea?
> Thanks
Relational databases are transactional. There is nothing you can do to
prevent a transaction when modifying data. If you don't require keeping
old transactions around in the transaction log, you can use the simple
recovery model. That will require you perform full database backups and
you can only recover a database as recent as the last full backup. In
simple recovery, SQL Server truncates (removes) committed transactions
from the log on a regular basis (every checkpoint). That doesn't mean
the transaction log won't grow, however. A large transaction could still
cause the physical transaction log file(s) to grow. Truncating the log
does not return the file to a smaller size. You would need to use DBCC
SHRINKFILE to do that if the need arises.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||So, why even run the stored procedure. If you don't care if the data makes
it over, then you shouldn't even care if you do any data modifications It
will greatly improve performance, that is for sure :)
Seriously, what do you mean transactions. Just ignore errors in your
application is most likely all you need to do. Run the proc, if it fails,
just go on. I would seriously look at logging errors to the event log,
because if it fails everytime (for some reason) you probably will want it to
be fixed, otherwise see my first comment and remove the smiley.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Chris Calzaretta" <ChrisCalzaretta@.discussions.microsoft.com> wrote in
message news:A7657CF8-757F-445D-9077-A2F550FB592A@.microsoft.com...
> How would I execute a stored procedure and turn off the transaction.
> We do not care on our end if the data makes it over. So we dont need
> transaciton.
> Any Idea?
> Thanks