Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question
    
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. –  Mike Brown Feb 3 '11 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 '11 at 16:25

3 Answers 3

up vote 4 down vote accepted

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.

share|improve this answer

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.

share|improve this answer
    
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 '11 at 15:50
    
@WernerCD etltools.net/etl-tools-comparison.html –  chrisaycock Feb 3 '11 at 16:51

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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