Making Transactions Work - Without Blocking

I'm having a very difficult time getting any type of transactions to work without encountering blocking.

Here's what I have. I created a package using the Import data option on a database to start with. That gave me the following:

On the control flow tab there is a Preparation SQL task which truncates a table followed by a Data Flow Task that then copies from an oracle database into that same table.

The two are connected by the green success arrow.

For obvious reasons, if the import from oracle fails, I would like the truncate of the table to be rolled back so that the table in question is returned to its previous state.

I have tried the following:

Setting the transactions setting on the data flow panel to "supported" and changing it on the Control Flow tab to required.

Setting both to required.

Setting the data flow setting to "supported", then adding a sequence container onto the control flow panel, moved both the preparation and data flow tasks into it and then set the sequence container to "required".

In all cases I have installed the package onto the server and then scheduled a job in SQL Server agent to run the package.

No matter what way I do it, the preparation sql task apparently opens a transaction and then the following data flow task starts a different one and is blocked by the first one. If you check a log file I set it to generate it clearly gets stuck after the sql preparation job when trying to start the data flow task. Checking sql server itself (Activity monitor), the job is stuck because it is blocked by process "-2".

So I'm lost as to how to make one single transaction be used for the entire package and get the behavior i need....

Thx.

R-

Robert,

I've had EXACTLY the same problem and to say I was annoyed is an understatement.

You won't like the answer - I don't think this can be solved. The -2 SPID is MSDTC (documented here http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_wa-wz_3v8v.asp and here http://msdn2.microsoft.com/ms173730.aspx). It happens because the 2 tasks run under differrent connections. If they were the same connection it wouldn't be a problem.

I've mentioned a workaround here: http://blogs.conchango.com/jamiethomson/archive/2005/08/20/2048.aspx but it can only really be used in specific situations.

-Jamie

|||

Thanks for the information. Glad to know someone else has run into this also.

This really sucks. Deleting or turncating a table and then refreshing it with new data is a very common action. And it hink 100$ of the time you would want to be able to roll back the complete thing in the event of an error.

Maybe MS can explain to us how they anticpate you are supposed to perform this type of function?

|||

Thanks.

I did try that actually but received the error:

INCOMPATIBLE TRANSACTION CONTEXT

when it tried to do the dataflow task (step 2).

Making an invoice

Hello!
I'm trying to make an Invoice in RS but find it difficult, could I get some
help with the following:
If the list of items in the invoice is larger than one page I have to show
the rest on the next page, the
standard in our country is to have a bank giro at the bottom of an invoice,
about 1/5 of the invoice
would be a bank giro like this:
--
| |
| header |
--
| |
| invoice |
| items |
| table |
| |
--
| |
| bank giro |
--
And the paper in the printer I'm writing to allways have allocated space for
the bank giro so the
list being printed on the second page should not go below that allocated
space. And the information
to be printed to the bank giro allocated space should only be on the first
page, on the second page
(or third, fourth etc) it should be nothing there.
The problems are:
1. If I use a footer to the bank giro my only choice is
"printonlastpage=false" but then, if there are 3 or
more pages it would be printed on page 1, page 2 etc but not on the last
page.
2. If I use a textbox below the table containing the invoice items I cannot
find a method to only put the
textbox only on the first page, it will only be on the last page, after the
invoiceitems table on the last page.
(it should be possible to say that the table continues to the next page but
the text box should not...)
3. For both above I can't find a method to "break" the invoiceitems table
before it reaches the bank giro
allocated space.
Hope for some help!Thank you!
This will set me on track. BTW: if i set the datasource to select top 3 *
from customers then the footer disappear...
Marius
"Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message
news:%23BL78GMWEHA.4056@.TK2MSFTNGP11.phx.gbl...
> Attached is a sample report that should model what you are trying to
> achieve. This report will only print a page footer ("bank giro") on the
> first page of the report.
> Key Areas:
> * The Page is set to print on first page
> * All items in the page footer are contained in a rectangle.
> * The visibility property on the rectangle is set to an expression:
> =iif(ReportItems!DoubleSecretTextbox.Value is Nothing, true, false)
> * The DoubleSecretTextbox contains a single character, is sized to 0,0,
and
> has its
> foreground and background set to white. It is placed to the right of
the
> table.
> When this report is rendered the textbox will not be visible but it does
> exist. Hence on page 1 the rectangle expression does not evaluate to
> Nothing. However on subsequent pages the textbox does not exist: the page
> footer is not rendered.
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Marius Trælnes" <marius.traelnesnospam@.nospamc2i.net> wrote in message
> news:OsfkaTHWEHA.3476@.tk2msftngp13.phx.gbl...
> > Hello!
> >
> > I'm trying to make an Invoice in RS but find it difficult, could I get
> some
> > help with the following:
> >
> > If the list of items in the invoice is larger than one page I have to
show
> > the rest on the next page, the
> > standard in our country is to have a bank giro at the bottom of an
> invoice,
> > about 1/5 of the invoice
> > would be a bank giro like this:
> >
> > --
> > | |
> > | header |
> > --
> > | |
> > | invoice |
> > | items |
> > | table |
> > | |
> > --
> > | |
> > | bank giro |
> > --
> >
> > And the paper in the printer I'm writing to allways have allocated space
> for
> > the bank giro so the
> > list being printed on the second page should not go below that allocated
> > space. And the information
> > to be printed to the bank giro allocated space should only be on the
first
> > page, on the second page
> > (or third, fourth etc) it should be nothing there.
> >
> > The problems are:
> >
> > 1. If I use a footer to the bank giro my only choice is
> > "printonlastpage=false" but then, if there are 3 or
> > more pages it would be printed on page 1, page 2 etc but not on the last
> > page.
> > 2. If I use a textbox below the table containing the invoice items I
> cannot
> > find a method to only put the
> > textbox only on the first page, it will only be on the last page, after
> the
> > invoiceitems table on the last page.
> > (it should be possible to say that the table continues to the next page
> but
> > the text box should not...)
> > 3. For both above I can't find a method to "break" the invoiceitems
table
> > before it reaches the bank giro
> > allocated space.
> >
> > Hope for some help!
> >
> >
>
>

Maintenance Plan Tasks In SSIS(Sql 2005)

I am finding difficult to set Expression Property "SelectedDatabases" in Check Databases Integrity Task in SSIS.

I keep getting error:

TITLE: Expression Builder

Expression cannot be evaluated.

ADDITIONAL INFORMATION:

The data type of variable "User::varDataset" is not supported in an expression.

Reading the variable "User::varDataset" failed with error code 0xC00470D0.

(Microsoft.DataTransformationServices.Controls)

I need the flexibilty to be able to control the Task behaviour during runtime through Variables.

Any suggestions .... or even a different approach will be helpful..

cheers

aigbor

Hi Aigbor

I encountered a similar problem when using the Shrink Database Task and decided to use Execute SQL Task with an expression instead.

You might find the execute sql task with the DBCC CHECKDB command to be more useful.

Maintenance Plan Tasks In SSIS(Sql 2005)

I am finding difficult to set Expression Property "SelectedDatabases" in Check Databases Integrity Task in SSIS.

I keep getting error:

TITLE: Expression Builder

Expression cannot be evaluated.

ADDITIONAL INFORMATION:

The data type of variable "User::varDataset" is not supported in an expression.

Reading the variable "User::varDataset" failed with error code 0xC00470D0.

(Microsoft.DataTransformationServices.Controls)

I need the flexibilty to be able to control the Task behaviour during runtime through Variables.

Any suggestions .... or even a different approach will be helpful..

cheers

aigbor

Hi Aigbor

I encountered a similar problem when using the Shrink Database Task and decided to use Execute SQL Task with an expression instead.

You might find the execute sql task with the DBCC CHECKDB command to be more useful.