Hi,

I have a requirement in which I have to receive records from a flat file and insert it in BizTalk. The problem is that the file will be very large (across half a million or more records). What is the ideal way to accomplish this in BizTalk.

Thanks.

The best way to use BizTalk in this case would be to NOT USE IT and use SSIS for doing the data upload in SQL Server.

Regards.

• Proposed as answer by M.R.ASHWINPRABHUMicrosoft community contributor 1 hour 52 minutes ago
• Unproposed as answer by M.R.ASHWINPRABHUMicrosoft community contributor 42 minutes ago

Hi,

You can use Biztalk for ETL but performance wise SSIS is much better(especially for large messages). BizTalk is more suited for integrating applications and real time communication.

See also the following link if you want to process large messages with BizTalk.

How BizTalk Server Processes Large Messages
http://msdn.microsoft.com/en-us/library/aa560481.aspx

  

Kind regards,

Tomasso Groenendijk

Blog  |  Twitter
MCTS BizTalk Server 2006, 2010

If this answers your question please mark it accordingly

• Proposed as answer by M.R.ASHWINPRABHUMicrosoft community contributor 1 hour 52 minutes ago
• Unproposed as answer by M.R.ASHWINPRABHUMicrosoft community contributor 42 minutes ago

But, we need to use BizTalk only in this case as there many other operations also that we are doing. Any suggestions.

Hi Dipti,

SSIS is the better option in the case of importing large data to SQL Server. But if you still want to implement this scenario in BizTalk, youre ready and aware of performance degradation you would get using BizTalk compared to SQL SSIS.

In BizTalk to handle this requirement, you have two options:

Common ways:

• Split the message at receive port  level into multiple messages and insert the multiple instance into SQL.
• Split the message at orchestration  by calling the Flat File Disassembler pipeline component. Loop through the multiple messages and insert the multiple messages into SQL. This would reduce the number message instance published into message box db.

Either way, with millions of record in hand, these way would have high performance impact, so processing would be very slow.

Batch import Better way:

In this batch import scenario, this doesnt involve any orchestration but is a pure messaging scenario and you can do all the transformation on the ports(BizTalk Server 2006 onwards, large messages will be mapped by the new large message transformation engine, which buffers message data to the file system, keeping the memory consumption flat). Hence the better performance.

Refer this article for the details on this batch import method. You can also find sample solution using batch import.

What are some of the other operations that you're doing for which you are mandating that BizTalk be used?

Regards.