We receive a xml file containing a full set of a list of companies and their
phone numbers etc. What I would like to do with this data is to delete all
data in the tables and insert the latest full set of data. What I don't want
is to delete all data and then having some error so only part or nothing is
inserted because the information is crucial to other critical applications.
They can only handle a small delays so the file has to be split up into
smaller messages (one per company). Is there some way nice way to accomplish
this? We have schemas so the document is automatically split up into smaller
parts to be able to insert. Is it possible to use the orchestration to:
receive file
(begin transaction)
execute delete SP
divide file into smaller data parts which are inserted into DB (SP)
(commit transaction)

The problem for me seems to be how to divide the file in the orchestrion and
be able to hold the divided messages in the transaction? Would this be
possible? How? Will this lock the whole database?

Or is it safer to insert the data into a temp DB and then execute some DTS
at given times?

Best regards
Niklas E

[ Post a follow-up to this message ]

Do it all in one shot.

See Matt Miner's post below

http://www.m3technologypartners.com...72-d79cba73dcc2

Matt

"Niklas E" wrote:

> We receive a xml file containing a full set of a list of companies and the
ir
> phone numbers etc. What I would like to do with this data is to delete all
> data in the tables and insert the latest full set of data. What I don't wa
nt
> is to delete all data and then having some error so only part or nothing i
s
> inserted because the information is crucial to other critical applications
.
> They can only handle a small delays so the file has to be split up into
> smaller messages (one per company). Is there some way nice way to accompli
sh
> this? We have schemas so the document is automatically split up into small
er
> parts to be able to insert. Is it possible to use the orchestration to:
> receive file
> (begin transaction)
> execute delete SP
> divide file into smaller data parts which are inserted into DB (SP)
> (commit transaction)
>
> The problem for me seems to be how to divide the file in the orchestrion a
nd
> be able to hold the divided messages in the transaction? Would this be
> possible? How? Will this lock the whole database?
>
> Or is it safer to insert the data into a temp DB and then execute some DTS
> at given times?
>
> Best regards
> Niklas E
>
>
>

[ Post a follow-up to this message ]

I really recommend this approach. We use it on most data loading tasks.
Others we use the updategram but it can be really tricky to use if you want
to both update and create new rows in the same transaction.

"Matt Meleski" <MattMeleski@discussions.microsoft.com> wrote in message
news:35792926-F5FD-49C2-8454-9E7896B30468@microsoft.com...
> Do it all in one shot.
>
> See Matt Miner's post below
>
>
http://www.m3technologypartners.com...a73dcc2
[vbcol=seagreen]
>
> Matt
>
> "Niklas E" wrote:
> 
their[vbcol=seagreen] 
all[vbcol=seagreen] 
want[vbcol=seagreen] 
is[vbcol=seagreen] 
applications.[vbcol=seagreen] 
accomplish[vbcol=seagreen] 
smaller[vbcol=seagreen] 
and[vbcol=seagreen] 
DTS[vbcol=seagreen] 

[ Post a follow-up to this message ]

Thanks alot. I will try that 

Best regards
Niklas E


"Xerox" <anon@anon.com> wrote in message
news:ONo$pM05EHA.2568@TK2MSFTNGP11.phx.gbl...
>I really recommend this approach. We use it on most data loading tasks.
> Others we use the updategram but it can be really tricky to use if you
> want
> to both update and create new rows in the same transaction.
>
> "Matt Meleski" <MattMeleski@discussions.microsoft.com> wrote in message
> news:35792926-F5FD-49C2-8454-9E7896B30468@microsoft.com... 
> http://www.m3technologypartners.com...72-d79cba73dcc2 
> their 
> all 
> want 
> is 
> applications. 
> accomplish 
> smaller 
> and 
> DTS 
>
>

[ Post a follow-up to this message ]