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

Executing a task with in script task

How can I execute a sql task from a script task. Both these tasks are part of the same package. The script task is actually part of the error handler. The execute sql task is part of control flow in the package.

Thanks

Why would you want to do this, as opposed to putting an Execute SQL Task in your error handler?|||Well, I dont know if I put a task specific error handler, will the package level error handler will fire or not. ( I want both error handlers to be fired). I'm trying it out now, let me see how it goes.|||Yes, the package error handler should fire as well. Events propagate up to the parent.

Execute Package Task Not Working

I am trying to run two execute package tasks. I want the second task NOT to
execute if the first one fails, but I cant seem to get this working. I have
put the workflow in place and check fail package on first error in every
place I can think of, yet the second package always executes regardless if
the first one fails. HELP!!!!Do you have an error? What does the package log say?
Anith

Execute Package Task

Hi,
I have a DTS package that runs numerous tasks simultaneously for time and
performance reasons, but I would like to add an "Execute Package Task" to
this package so that another package will run, but only after all tasks have
finished in this package. Is there a way to do this?
Thanks!Hi Patrice,
Try connect all your tasks to a dummy task using workflow properties.
Finally connect your final "Execute Package Task" to the dummy task.
Dennis.
"Patrice" wrote:

> Hi,
> I have a DTS package that runs numerous tasks simultaneously for time and
> performance reasons, but I would like to add an "Execute Package Task" to
> this package so that another package will run, but only after all tasks ha
ve
> finished in this package. Is there a way to do this?
> Thanks!

Execute Package Task

I am using many Execute Package Tasks in a DTS packages to call a number of other DTS packages. The problem is that when I change one of the called DTS packages the execute package task does not run the updated packages. It looks like the execute package task references a Package ID guid instead of the name. Is there a way to make it so a change to a called DTS package will reflect in all references to it

Hi James,

I'd like to know whether you're using the DTS in SQL Server 2000 or SSIS in SQL Server 2005.

If the DTS package is on the same machine as it is running, it has to be fine. The execute package task references the package according to its name.

If you're working on SSIS in SQL Server 2005, please confirm if you have uploaded it to the server. As when you have modified it, it still remains on the client side.

|||

Kevin, thanks for the response. I am using DTS and the package is on the same server. Though it is definatley referencing it by PackageID. If I use the DTSRun utility I can reference it by name. However, when use execute package inside another package it references it by PackageID

Execute package results different to step by step execution - uses raw file

I have a package that has multiple data flow tasks. At the end of a task, key data is written into a raw file (file name stored in a variable) that is used as a data source for the next task. Each task requires a success from the preceding task.

Here's the rub:

If I execute the entire package, the results of the package (number of records of certain tasks) differs significantly from when I execute each step in the package in turn (many more records e.g. 5 vs 350).

I get the feeling that the Raw file is read into memory before it is flushed by the previous task, or that the next task begins preparation tasks too early.

Any help is greatly appreciated.

I am running on Server 2003 64 (although the same thing happens when deployed on a Server 2003 32 machine)

Thanks

B.

Hi Brian,

Interesting.

A workaround question/suggestion: Would staging the data between tasks in a database staging table work for you?

Just curious,
Andy

|||

Hi Andy,

I was hoping someone has encountered this and has a quick fix - that said....

Short answer - yes - would probably work, but I would prefer not to (I don't really like using temporary tables if I can avoid them).

The data that is being stored in the files are new guids and their relationship to old composite ids, generated during the processing of the records. I guess that storing that information in a staging database is a possibility, but I would hate to have to re-work that entire block of code.

Ideally, if it can't be stored in raw files, then connection specific temporary tables would have been my next option - but those are not really available either.

Thanks,

B.

Execute Mutiple Tasks In Debug Mode

Maybe I'm missing something, but I can't find how to run multiple tasks in sequence while in Visual Studio debug mode. In DTS design mode I grew accustomed to right-clicking tasks one-at-a time, but in SSIS I find the additional step of having to exit Debugging mode after every task gets old after a while.

There must be a way to start execution at a certain task and have the package continue all the way to some other specified task. It would also be nice to have every task in a Group execute in sequence and stop (even if connections continue beyond the group). I could even settle for repeatedly clicking the Continue button in Debug mode, but it's always grayed out when the current task is finished!

Can this be achieved by setting breakpoints?

I have previously requested "Execute from here" functionality in the control flow. You can vote for this request here: http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=a2548612-b602-42ab-9ff9-563d927674f8

Adding a comment would help as well.

In the meantime, you can place all required tasks into a sequence container, right click on the sequence container and select "Execute task". All tasks in the container will be executed.

-Jamie

|||

Sequence container appears to be what I was looking for. Thanks. It seems obvious now that you've pointed it out, but features are easy to miss with so much new functionality.

I added some comments on your request.