The blurring line between Biztalk and SSIS

Question

I'm building an application that receive positional flat files from a legacy application and for each detail row I need to search inside a third application for some data and then fill up my database. And in case of any mal formed line in file, I need to stop de processing and log the line and position of malformed string.

At least for now, the files have, max. 50MB.

I'm very confuse who is best suitable for this scenario, Biztalk and SSIS have similar features and, as far I can see, both are suitable in this scenario. This is a task I could make a good use of Biztalk or I should go with a ETL solution (Integration Services) ?

Answer 1

I am normally recommend BizTalk left, right and centre, however in this case I would go with SSIS for two reasons:

1. On a file 50Mb plus you will get much better performance out of SSIS, no matter how many resources you throw at BizTalk given the way that BTS will process each record within the file. There are strategies here of course, but SSIS will win hands-down (Although I would imagine you web-service will probably be your bottleneck irrespective of which solution you choose); and

2. Unless you write a custom flat-file disassembler (which is almost rocket-science, BizTalk God territory), the standard disassembler will simply stop when it reaches a malformed line, logging the error into the Event Log and no further message processing will take place.

As an aside, I have been dropped into far too many projects where customers have a solution written in BizTalk where batch operations are being performed. The original development and testing was completed on flat-files c. 1Mb - 10Mb. Customers are then confused when a 50Mb - 100Mb+ files take so long to process!

Its much better to choose the right solution to the problem (IMHO, SSIS) at the beginning of the project, rather than crowbar a solution onto a product that isn't suitable.

Answer 2

I would probably do this in SSIS. It appears to be a ETL job. BizTalk might give you better flexibility given the source of the data long term but if as you say it's a web service this is something that can be accomplished in SSIS.

Generally speaking SSIS = batch process and direct data translations. BizTalk = Messaging / horizontal systems requests / responses that may or may not need to be synchronized.

But don't take my word for it. Include effort, software costs if they matter, and longevity of this process.