In my experience this is not a great way of performing this sort of task.
If you are only shifting data from oracle to SQL and there is minimal mapping and no further interaction with the data I would strongly recommend a more suited technology such as SSIS.
I have had a very similar problem with significantly smaller file sizes (100MB) that I had to pick up a batch file and stick it in sql.
Almost every angle I turned to I got an out of memory exception or very erratic behaviour from BizTalk (a symptom of the low memory i think)
I tried all sorts of tricks, including the aforementioned xquery and xml parameters. While useful in principle, the practicality of doing this means your original message will need to be converted to an escaped string and placed in the attribute that represents that stored procedure parameter. If you are dealing with a big message this is not going to please BizTalk at all.
Maybe the new WCF sql adapter may offer more resilience, but I would count on it :-)
So the only place this leaves you (and is the route I followed in the end) is to experiment with the solutions suggested where you
1. read the data from oracle in smaller chubnks and push those through to sql
Not sure how the oracle adapter works, but you could get a race condition where the sql write is not complete by the next oracle read making the pressure build in biztalk and on the target DB.
2. read all the data from oracle and debatch in a pipeline This assumes you can read in all this data from oracle without issue. If you can you can either split the batch in a pipeline and have multiple orchestrations pushing data to sql, or process the batch sequentially or semi sequentially where you control the flow of data to the sql server.
Your SQL box will also be being kicked to kingdom come and other services running on the biztalk \ sql platform may experience performance issues.
Hope this helps, and I feel your pain!
Dino
