1

Where I work, we use an in-house ETL solution that's homegrown and has been around for 5-10 years. I'm still new to my data analysis job, but I was wondering about the ETL tools that are out there. This is a new area for me.

My situation, and job, is basically

  • digging in a set of databases (DB2, SQL2005, Citrix, Ancient Cobol Database with a SQL Wrapper on top, MySQL, etc).
  • Gather the desired information.
  • combine the different datasets into one set.
  • output into a file of choice (CSV, Tab Separated, Pipe Separated, XLS, etc).
  • FTP to customer.

I guess what my real question is, given my job, what are some good ETL suites that I can look at and compare to my in-house tools? This is more to research some other options. Ultimately, I'd either suggest a new solution or get options/ideas to improve our current app.

2
  • What languages/platforms are you able to use for your system? There are a lot of ETL tools out there. But their utility all depends on what you want out of them. Feb 3, 2011 at 16:10
  • In house, we generally use MS: VB, SQLServer2005, TSql (the in-house app is built in VB .Net 2.0.)... The app, via XML driven ini-file, takes one or multiple SQL statements, against one or many sources, to generate a desired output file. I'm not wanting to limit to MS/VB, but am wanting to know what people have had good success with in similar situations.
    – WernerCD
    Feb 3, 2011 at 16:25

3 Answers 3

5

Pentaho Data Integration (aka Kettle) is an open-source ETL tool that is part of the larger Pentaho BI suite.

I've been using in almost the exact manner you described. I can put together a quick ad-hoc request in about 15 minutes or I can spend days and weeks putting together a very robust set of transformations to create a setup of daily jobs.

It has the advantage of supporting both a command-line interface as well as a GUI for developing the transformations and jobs. It can be run standalone or with a cluster of slave servers.

I've found it to be very flexible with a ton of community support and momentum.

Some commercial ETL suites to look at are IBM Infosphere Datastage and Informatica PowerCenter. I supported Datastage previously and Informatica is used for a lot of big BI projects.

2

There are tons of open-source and proprietary ETL tools. But the real question is why do you need to change your current system?

Are you doing too many things manually? Is the set-up really slow or error-prone? Will it not work on the new hardware your firm wants to migrate to? Those are all good reasons to investigate outside options.

But if just want to see what's available just to replace the current system with something whizbang, that's a hard-sell as a business proposition. Most managers are unwilling to change an old yet perfectly working system for a newer one as the costs and risks are rarely justified.

2
  • The in-house app is time-tested and works for our needs... So this is more research ATM. I'm too new to this and want to know if our app is awesome, substandard or just average. I saw the wiki article and wasn't sure where to start. With some things, there seems to be articles, comparisons, pro vs con all over the place (Drupal vs Wordpress, C# vs Java, etc), but i really hadn't seen the same for ETL. I'm just trying to see whats out there, and suggestions from guru's are what I'm after.
    – WernerCD
    Feb 3, 2011 at 15:50
  • 1
    @WernerCD etltools.net/etl-tools-comparison.html Feb 3, 2011 at 16:51
0

The three main commercial contenders are:-

Informatica PowerCenter -- probably the best of breed, very clean graphical interface and very consistent APIs.

IBMs InfoSphere DataStage -- the "granddaddy" of these tools. It has been around for a long time so its highly tuned and has lots of options. Conversely it suffers from inconsistent implementations and APIs as coding styles changed over the years. Also the excellent "parallel edition" is really another product bolted on.

AbIntio from the company of the same name -- Never worked with this personally and the company is incredibly secretive so its hard to get details until you are an actual customer. BUT I have it on good authority that this is the fastest of the tools and can move massive volumes of data in double quick time.

Be warned all of these are big ticket items.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.