What's the best tool for data integration

Question

Hello. I'm looking the best tool for data integration. I need the following features:

• Customized loading/matching and clearing of data from different sources (including MSSQL Server, PostgreSQL, WebServices, Excel, text files in various formats). The receiver of data is MSSQL Server 2008.

• Ability to configure rules of data convertation externally (e.g. config files or visual tools)

• Support for Unicode, logging, multithreading and fault tolerance
• Scalability (very important)
• Ability to process large volumes of data (more that 100MB per day)

I look at SQL Server Integration Service 2008, but I'm not sure that it fits these criteria. What do you think?

Answer 1

It looks like Integration Services (SSIS) should handle your requirements. It should definitely be high on your list because it has good integration with SQL Server and is extremely cost effective compared to most alternatives.

As far as scalability is concerned, your data sounds very small (100MB per day is not much these days) so it's well within the capabilities of SSIS, even for complex data flows. For fault tolerance, SSIS has restartability features out of the box but if high availability is important to you then you may want to consider clustering / mirroring.

Answer 2

I only know SSIS from first hand experience so I can't say how it compares to other solutions.

But I'd say it's a good solution for all the points you ask.

The only thing that is a bit tricky:

don't know Ability to configure rules of data convertation externally (e.g. config files or visual tools)

Not sure I get this right. You can store configuration parameters for SSIS in external files or even in an SQL table. But you would still need to specify the kinds of rules inside the package. Unless you write your own script component (inside of which you can of course interpret the formating rules you store externally)