Is BizTalk feasible for this?

Question

Hello All,

My team is planning to use Biztalk server 2006. The task is as follows:-

Run a scheduled job every night which fetches from multiple databases a set of data (basically the same select query with a where clause run for about 20000 Id's) and writes it into an xml.

Presently we have c# winform which does the same for one or multiple Id on user input. But the customer does not want user input and instead wants to run a job in the night, so that in the morning the xml contains the latest data from all the databases.

We thought Biztalk is good as we will require good fault handling and no loss of data. And also bcos its a good experience for us as we have never used biztalk. (we have a license already for another project in the same dept)

Please advice, is biztalk the solution?

Answer 1

As per Mitch, it would be a stretch to go and buy, install and learn BizTalk to use it to poll databases and extract XML files.

However, if you already have BizTalk in your enterprise, and a policy to use it for all Integration, and that you regard your XML extraction as integration, well, OK.

It is pretty simple:

• Use the appropriate adapter to poll your data (e.g. SQL Adapter if your RDBMS's are SQL, DB2 on the HIS pack, etc). For MSSQL FOR XML AUTO is the easiest way to get it into any sort of XML
• You'll need to define schemas for input and output XML
• Create a Map (or maps) to convert the SQL result set into the required file format.
• Use a simple file adapter as the send port to do the XML write.

Use the Receive Port's Schedule / Service Window to restrict the timing to once per night.

One gotcha : when polling the SQL database, block concurrent access to the data (e.g. use an UPDLOCK) or similar to ensure that if the job is fired concurrently that it won't duplicate the file output.

Answer 2

In the winforms app you already the C# code to do the transfer. In this case there does not appear to be any business value in moving this one process to Biztalk.

You could move this code to a windows service app, that would automatically run each night.

To make it more robust, I would first copy all data to a temporary table, then process it one row at a time, making sure that everyting is saved after each row. Then if something crashes it will start where it left off last time.