Blur line between Biztalk and SSIS

Question

I am creating an application that receives positional flat files from an outdated application, and for each row of details I need to search inside the third application for some data, and then populate my database. And in the case of any generated line in the file, I need to stop processing and register the line and the position of the distorted line.

At least at the moment the files have, max. 50 Mb

I am very embarrassed about who is best suited for this scenario, Biztalk and SSIS have similar features, and as far as I can see, both options are suitable for this scenario. Is this a task that I could make good use of Biztalk, or should I go with an ETL (Integration Services) solution?

Answer 1

I will probably do this in SSIS. This seems to be the work of ETL. BizTalk may give you better flexibility given the data source in the long run, but if, as you say, it is a web service, this is what can be done in SSIS.

Generally speaking, SSIS = batch process and direct data transfers. BizTalk = Messaging / horizontal messaging requests / responses that may or may not necessarily be synchronized.

But do not take my word for it. Include effort, software costs, if relevant, and the longevity of the process.

Answer 2

I usually recommend BizTalk left, right, and center, however in this case I would go with SSIS for two reasons:

• In a 50 MB plus file, you will get much better performance from SSIS, regardless of how much resources you throw up on BizTalk, given how BTS handles every record in the file. Of course, there are strategies here, but SSIS will win the distribution (although I would suggest that the web service is likely to be your bottleneck, no matter which solution you choose); and

• If you don’t write an arbitrary disassembler with a flat file (which is almost rocket science, territory of BizTalk God), the standard disassembler will simply stop when it reaches the wrong line, registering an error in the event log and not receiving further message processing will be performed.

As an aside, I ended up in too many projects where clients have a solution written in BizTalk where batch operations are performed. Initial development and testing were completed on flat c files. 1 MB - 10 MB. Then clients get confused when files of 50 MB - 100 MB + sizes take so long to process!

It is better to choose the correct solution to the problem (IMHO, SSIS) at the beginning of the project, and not break the solution into a product that does not fit.