|
Home > Archive > BizTalk Server Applications Integration > September 2005 > Best suggestion for huge file to update SQL table
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Best suggestion for huge file to update SQL table
|
|
| Stephane Paquet 2005-09-28, 8:51 pm |
| I have these huge files (2-5Gb) that i need to update to a table in SQL.
Initially this was using a DTS package and running fine. Now, trying to
integrate that into Biztalk 2004 and i've tried the SQL Updategram which
kills the server, i've also explored the debatching of the initial file
using and enveloppe schema and sending to a file drop for later pickup of
individual files so that update of SQL is done in smaller batches (based on
the pickup file batch size) but, just splitting of the file takes forever as
well... I am about to test using an Atomic scope nodelist to try it out....
Anyone has advice on the best and most performant approach, to stay away
from DTS....
Thanks for your help...
| |
| Jeff Lynch 2005-09-29, 5:58 pm |
| I would NOT use BizTalk to accomplish this size SQL update. This is what DTS
was designed for (using bulk copy) and the BTS2004 pipelines and SQL Adapter
will most likely "choke" on the size of the XML. In November when SQL 2005
is released you will be able to use the new SQL Integration Services (DTS on
steroids) to accomplish this in a much more "BizTalk" like manner.
BizTalk is best suited for transactional data not huge SQL loads.
--
Jeff Lynch
"A BizTalk Enthusiast"
http://codebetter.com/blogs/jeff.lynch
"Stephane Paquet" <Stephane.Paquet@cactuscommerce.com> wrote in message
news:e0Fh2cJxFHA.2924@TK2MSFTNGP15.phx.gbl...
>I have these huge files (2-5Gb) that i need to update to a table in SQL.
>Initially this was using a DTS package and running fine. Now, trying to
>integrate that into Biztalk 2004 and i've tried the SQL Updategram which
>kills the server, i've also explored the debatching of the initial file
>using and enveloppe schema and sending to a file drop for later pickup of
>individual files so that update of SQL is done in smaller batches (based on
>the pickup file batch size) but, just splitting of the file takes forever
>as well... I am about to test using an Atomic scope nodelist to try it
>out....
>
> Anyone has advice on the best and most performant approach, to stay away
> from DTS....
>
> Thanks for your help...
>
|
|
|
|
|