Hi All,

I need to write a Biztalk integration solution that takes an FTP file from A
location and updates a database.

Question:

I need too make many different record updates which I need to rollback if a
problem occurs.

I cannot find ANY SQL Adaptor examples the use atomic transaction scope.
Is SQL updates always return a response so I am not allowed by Biztalk to
put it inside a Atomic Transaction (i.e. you cannot have a required and
response port in that type on transaction scope).

How do you guy's normally deal with this problem?
I don't have to write compensation blocks, that would start to get very
complicated (might as well use DTS!).

Any advice will be greatly appreciated.

E.g. Samples, Real World techniques, etc

Regards,

Simon

[ Post a follow-up to this message ]

<ee@ee.demon.co.uk> wrote in message
news:444a369d$0$22990$c3e8da3@news.astraweb.com...
> Hi All,
>
> I need to write a Biztalk integration solution that takes an FTP file from
> A
> location and updates a database.
>
> Question:
>
> I need too make many different record updates which I need to rollback if
> a
> problem occurs.
>
> I cannot find ANY SQL Adaptor examples the use atomic transaction scope.
> Is SQL updates always return a response so I am not allowed by Biztalk to
> put it inside a Atomic Transaction (i.e. you cannot have a required and
> response port in that type on transaction scope).
>
> How do you guy's normally deal with this problem?
> I don't have to write compensation blocks, that would start to get very
> complicated (might as well use DTS!).
>
> Any advice will be greatly appreciated.
>
> E.g. Samples, Real World techniques, etc
>

ADO.NET.

For making syncronous writes to SQL Server (or any other RDBMS), ADO.NET is
always faster and often easier than the adapters.


David

[ Post a follow-up to this message ]

Hi All,

Thanks David. That makes sense to me.

It is a shame though. I though one of the advantages of using orchestrations
is that it provide you with a very visual diagram of the updates that occur
in response to processing a file item (for example).

DTS for example does give you a visual diagram of each of the actions that
occur.

As I am updating a number of records I need transactional control. So rather
than using Enterprise Services (COM) to control the transactions from within
a .Net component. I should probably put all of the transactions into a store
procedure and use SQL Server transaction control.

Of cause this is event less visible and more complicated to write and debug.

Is it possible to use the Biztalk transaction control in any way?

Thanks,

Simon

[ Post a follow-up to this message ]

<ee@ee.demon.co.uk> wrote in message
news:444a4379$0$6420$c3e8da3@news.astraweb.com...
> Hi All,
>
> Thanks David. That makes sense to me.
>
> It is a shame though. I though one of the advantages of using
> orchestrations
> is that it provide you with a very visual diagram of the updates that
> occur
> in response to processing a file item (for example).
>
> DTS for example does give you a visual diagram of each of the actions that
> occur.
>
> As I am updating a number of records I need transactional control. So
> rather
> than using Enterprise Services (COM) to control the transactions from
> within
> a .Net component. I should probably put all of the transactions into a
> store
> procedure and use SQL Server transaction control.
>
> Of cause this is event less visible and more complicated to write and
> debug.
>
> Is it possible to use the Biztalk transaction control in any way?
>


If you put it in an atomic scope BizTalk should help you out.  Not 100% sure
on the details, and it certianly differs between 2004 and 2006, so you'll
need to test it out.

David

[ Post a follow-up to this message ]

Hi,

> If you put it in an atomic scope BizTalk should help you out.  Not 100% su
re
> on the details, and it certianly differs between 2004 and 2006, so you'll
> need to test it out.

I think if you are calling Enterprise Service components that can
participate in a transaction, they will participate in the transaction
created by an atomic scope, so this may give you what you are looking for. A
s
far as I know, there's no real change between 04 and 06 in the functionality
here, but it would be wise to test this (don't just take my word for it).

I tend to think that connecting to a database using .net code from an
orchestration it not always a good idea, it's a lot faster as David
mentioned, but it breaks the "Boundries are explicit" tenant of SOA, i feel
it's a better design to use adapters for all connections to external systems
,
though there are some cases where I break this rule.
 

This is the way that I would go if it's possible to solve the problim nicely
this way.


Regards,

Alan


--
The Bloggers Guide to BizTalk
http://geekswithblogs.com/asmith



"David Browne" wrote:
[vbcol=seagreen]
>
> <ee@ee.demon.co.uk> wrote in message
> news:444a4379$0$6420$c3e8da3@news.astraweb.com... 
>
>
> If you put it in an atomic scope BizTalk should help you out.  Not 100% su
re
> on the details, and it certianly differs between 2004 and 2006, so you'll
> need to test it out.
>
> David
>
>
>

[ Post a follow-up to this message ]

> If you put it in an atomic scope BizTalk should help you out.  Not 100%
> sure
> on the details, and it certianly differs between 2004 and 2006, so you'll
> need to test it out.
>
> David

But what about the retry mechanism etc?  As far as I understand it, if you
send the SQL Request in an atomic scope, the message will be submitted to
the messagebox and that will be part of the Atomic transaction for that
scope.  After you submit it to the messagebox, the adapter framework is
doing it's work.  And if the SQL server is offline, he will retry, so it can
take minutes before the message actually is committed to the LOB database.
You don't want to have this in one transaction, I guess?

I had a similar problem like yours and to workaround this transactional
problem, I wrote an adapter that accepts a message, containing different
stored procedure calls.  And all these different procedures were executed in
the same transaction.  This solution does not work if you need other things
like MSMQ etc in the same transaction.

Best regards,

Sam Vanhoutte
CODit Belgium

[ Post a follow-up to this message ]

"Alan Smith" <AlanSmith@discussions.microsoft.com> wrote in message
news:B2A3740D-CB30-4DBB-86FC-52DC3827AB0F@microsoft.com...
> Hi,
> 
>
> I think if you are calling Enterprise Service components that can
> participate in a transaction, they will participate in the transaction
> created by an atomic scope, so this may give you what you are looking for.
> As
> far as I know, there's no real change between 04 and 06 in the
> functionality
> here, but it would be wise to test this (don't just take my word for it).

I just wondered if BTS 2006 introduced support for System.Transactions
transactions.  It appears not, so Enterprise Services transactions will to
this.  However you can definitely just use a local database transaction and
throw an exception on failure.  If you need to account for the possibility
that the databse is not available then you can put an atomic scope in a loop
and a suspend in the catch.  Then failures will suspend service instances,
and you can retry them later.


>
> I tend to think that connecting to a database using .net code from an
> orchestration it not always a good idea, it's a lot faster as David
> mentioned, but it breaks the "Boundries are explicit" tenant of SOA, i
> feel
> it's a better design to use adapters for all connections to external
> systems,
> though there are some cases where I break this rule.
>

I agree.  However, if you require multiple transacted operations into a
system, that's a big clue that you should be shouldn't be accessing that
system through a service boundary.  You already have a tight coupling to the
system, so you might as well access it directly.

David

[ Post a follow-up to this message ]

"Sam Vanhoutte" <firstname@companyname.be> wrote in message
news:eISy7P4ZGHA.1220@TK2MSFTNGP02.phx.gbl...
> 
>
> But what about the retry mechanism etc?  As far as I understand it, if you
> send the SQL Request in an atomic scope, the message will be submitted to
> the messagebox and that will be part of the Atomic transaction for that
> scope.  After you submit it to the messagebox, the adapter framework is
> doing it's work.  And if the SQL server is offline, he will retry, so it
> can take minutes before the message actually is committed to the LOB
> database.

This is only an appropriate technique for a short-running transaction
against a reasonably available database.  If you need retry logic, you can
put the atomic scope in a loop, and suspend the instance on any exception.


David

[ Post a follow-up to this message ]