Hi all,

We need a biztalk solution to process various EDI files received and download the information in Oracle Database. Also the EDI files has to be generated based on the information from the Oracle database. The volume of files expected to be processed per day will be huge (in lakhs). Whether polling the data from stored procedures to generate EDI files and performing composite operation to insert records in database will be a better option or defining an XML format which will be used to communicate between the biztalk and oracle database will be better??? Also advice if there is any better way than the above two me

You have to be clearer with your question.

process various EDI files received and download the information in Oracle Database

And

Also the EDI files has to be generated based on the information from the Oracle database

You receive EDI files store it in Oracel db. Another process to generate EDI file from Oracle db.

Whether polling the data from stored procedures to generate EDI files and performing composite operation to insert records in database will be a better option or defining an XML format which will be used to communicate between the biztalk and oracle database will be better Sorry Im Confuesd.

You want to compare which is the better option..

You want to retrieve the data for EDI from db and insert the EDI into DB.

Or

Define XML to communicate between BizTalk and DB.

Its all depends on where is the source of the data for EDI files exist. Either from DB for other source, there is no performance impact. BizTalk can handle both..

Most of the times for EDI files, requirement is to break file at multiple levels and load into several different tables on target side - also known as Normalized load.

Best is to use wcf- custom adapter to load data (yes it will deal with xml). Read more about wcf-custom adapter configuration wizard , it will generate schemas for you when you call appropriate stored procs.

Stored procs have to be created on SQL side.

If you don't intend to break EDI file at multiple levels in BizTalk, you can also send whole xml (blob of xml corresponsing to each EDI file) to SQL and on SQL side break xml using Xpaths , table-type variable etc and load data. Here of course you have to be good with SQL. This completes loading (not downloading as you said)  EDI file into Oracle.

Regarding polling, use a similar approach in reverse direction, you will create a wfc-custom receive port which polls data every (defined time interval) seconds and generates corresponding xml for that. After that you can use BizTalk maps to merge this polled data with other xmls etc and generate your EDI output xml and pass it through EDI send pipeline to generate EDI output files.

Thanks!

Please vote up or mark as answer, if it helped you!

• Edited by Prabhdeep Gill Thursday, February 19, 2015 11:20 PM grammar

Most of the times for EDI files, requirement is to break file at multiple levels and load into several different tables on target side - also known as Normalized load.

Best is to use wcf- custom adapter to load data (yes it will deal with xml). Read more about wcf-custom adapter configuration wizard , it will generate schemas for you when you call appropriate stored procs.

Stored procs have to be created on SQL side.

If you don't intend to break EDI file at multiple levels in BizTalk, you can also send whole xml (blob of xml corresponsing to each EDI file) to SQL and on SQL side break xml using Xpaths , table-type variable etc and load data. Here of course you have to be good with SQL. This completes loading (not downloading as you said)  EDI file into Oracle.

Regarding polling, use a similar approach in reverse direction, you will create a wfc-custom receive port which polls data every (defined time interval) seconds and generates corresponding xml for that. After that you can use BizTalk maps to merge this polled data with other xmls etc and generate your EDI output xml and pass it through EDI send pipeline to generate EDI output files.

Thanks!

Please vote up or mark as answer, if it helped you!

• Edited by Prabhdeep Gill Thursday, February 19, 2015 11:20 PM grammar

Most of the times for EDI files, requirement is to break file at multiple levels and load into several different tables on target side - also known as Normalized load.

Best is to use wcf- custom adapter to load data (yes it will deal with xml). Read more about wcf-custom adapter configuration wizard , it will generate schemas for you when you call appropriate stored procs.

Stored procs have to be created on SQL side.

If you don't intend to break EDI file at multiple levels in BizTalk, you can also send whole xml (blob of xml corresponsing to each EDI file) to SQL and on SQL side break xml using Xpaths , table-type variable etc and load data. Here of course you have to be good with SQL. This completes loading (not downloading as you said)  EDI file into Oracle.

Regarding polling, use a similar approach in reverse direction, you will create a wfc-custom receive port which polls data every (defined time interval) seconds and generates corresponding xml for that. After that you can use BizTalk maps to merge this polled data with other xmls etc and generate your EDI output xml and pass it through EDI send pipeline to generate EDI output files.

Thanks!

Please vote up or mark as answer, if it helped you!

• Edited by Prabhdeep Gill Thursday, February 19, 2015 11:20 PM grammar

Hi Ashwin,

Yes i need two process. One to generate EDI file from Oracle DB and another to download EDI files into Oracle DB. I have identified two possible ways of handling the process in Biztalk

1) Directly picking the data from stored procedures in Oracle DB with the WCF adapter.

2) Defining an XML format which will be used for communicating between Biztalk and Oracle DB.

As per our forecast for our requirement the volume data will be huge (in lakhs). Also there are going to be multiple stored procedures (around 25 for each EDI) to be used for generating an EDI file.

I need to know the advantages and disadvantages of both the methods for this scenario.

If I understood your requirement correctly,

You need to extract EDI from Oracle db by executing around 25 stored procedures and also in another process you need to send message into Oracle db and needs to execute 25 stored procedures to send data into relevant tables.

IMO, I would use a separate table for BizTalks inbound and outbound in Oracle. And have an internal batch process within Oracle.

So for your outbound process (from Oracle to BizTalk) where you want to generate the EDI from Oracle for BizTalk:

Let the batch process populate this BizTalk specific table with data/records whenever they want to send the message to BizTalk with dataset required for EDI. Let this batch process to execute the relevant stored procedures (25) to populate the data into this BizTalk specific table.

BizTalk can poll for this table alone and whenever the data/record is there for BizTalk to process, BizTalk can process and generate the EDI file from that table.

So for your inbound process ( from BizTalk to Oracle) where you want to send the EDI from BizTalk into Oracle dbs:

Let BizTalk send the message out to one (or few tables) in Oracle and let the batch process execute the multiple stored procedure to insert the data/record from BizTalk specific table to other relevant tables.

Using this batch process within Oracle, you mitigate the data-cleansing task to database and whereas BizTalk can only be used for data integration across systems. Since you also expect high-load, this design will be better as BizTalk is employed just for handling the high-volume message rather than high-data cleansing task. This design also has the advantage of less maintenance issue as executing the multiple stored procedures happens within database end, hence less transaction related issues which you can normally expected in database related processes.

 

Regards,

M.R.Ashwin Prabhu

Hi Ashwin,

The data from multiple tables cannot be clubbed into single table in oracle using batch since the data in mulitple tables may vary. i.e a container record can have multiple reference numbers, cargo, location details etc. So a single cant be used.