Data Integration

[MethadoneBoy]

Hi guys,

Hope I'm posting this in the correct forum. The business and development team here have recently been working on a project that reads in flat-text files (each file containing numerous financial trades/transactions), processes their contents and enters the information into a database. Most of the development work has been handled by a seperate branch of the organisation, our team has been more involved with the overall domain analysis of the project. The problem that I'm looking to circumvent is this -

On any given trading day, several different text files from various sources (banks, brokers, etc) will be placed on our secure directory for processing. Is there any existing application that can perform functions on multiple text files such as read their contents and then concatenate them into one large file? We had been investigating the possibility of using SQL Server Intergration Services for this purpose but this might not be possible.

Any help/advice would be appreciated,

Thanks

[RhinoBull]

It's simple enough to write "concatenation" yourself...
However, processing smaller files is usually faster and also safer. You can have a scheduled task firing every so often or say windows service running in the background...
Whatever the methodology you choose I wouldn't recommend combining files.

[MethadoneBoy]

Thanks for the response.

It's simple enough to write "concatenation" yourself...

Yes, we initially developed our own concatenation application but management declined it because of the sensitivity of the project and the potential impact in the event of an error (the project will automate 75% of the financial transactions that our company processes). They are, on the other hand, receptive to using something like SQL Server 2005's Integration Services because of the level of support available for the product and because of its success and widespread use.

However, processing smaller files is usually faster and also safer. You can have a scheduled task firing every so often or say windows service running in the background...

Again, agreed. But unfortunately our hands are tied with this one.

Whatever the methodology you choose I wouldn't recommend combining files.

May I ask why? The reason I ask is because we've not had to develop anything similar to this process before. What would be the potential pitfalls, in your view?

[RhinoBull]

Concatenating files is basically "looking for troubles" - new file due to million and one resaon can get corrupted during the "concatenation" process thus it's useless and may even become unreadable at some point.
Larger files takes longer to read and the more you work with it the more resources will be needed.
You management needs to be advised that best way is to work with opriginal file - not copy (unless of course it has to be reformated to some extent but still one at the time).