Choice of ETL tool. Datastage, Informatica or SSIS?

Posted on 2008-10-06
1 Endorsement
Last Modified: 2016-04-18

I work in one of the largest financial institutions in Denmark, in BI/Datawarehouse development. Currently we use Datastage XE as ETL tool. The licenses are soon to expire. I participiate in an evalution process, where the options are to upgrade the Datastage to EE or change to Informatica 8.6 or Microsoft SQL Server Integration Services. Are there anyone out there that have had the oportunity to work with (or evaluated) two (or all) of these, and could give me some key points for consideration?

Our Databases:
DB2 on mainframe (Business systems and EDW/Datamarts)
Oracle (Business systems and Datamarts

Current ETL tools:
Datastage XE (Mainframe and Server)
Informatica 7 (limited use - for Siebel only)

Front end
Hyperion + others

- Our EDW is a 2nd generation EDW (Bill Inmon) where information from various companies (our own banks, property loans (direct loans and through partner banks), insurance, property trade, currency and stock trade etc.) is or will be stored.
- Between the EDW and the Datamarts is a legalization layer to separate information where legal requirements dictates it. Also, between the EDW and the Datamarts is a physical Distribution layer.
- We also use LAMP (Linux-Apache-My SQL-PHP) We use Oracle instead of My SQL

Key points:
- Datastage XE wiil no longer be developed/supported by IBM. Porting to Datastage EE means rewriting all ETL jobs. Therefore, we expect to have to rewrite no matter what tool we select.
- Due to our type of business, the requirements for data quality  is high. Built in Data quality handling in the ETL tool would be an advantage.
- The tool have to be able to read/write DB2, Oracle and Essbase.

I would appreciate it
- if you have worked with (or evaluated) two (or all) of these, and could give me some key points for consideration.
- have practical experience in reading/writing to DB2 and/or Oracle and/or Essbase

Thanks a lot for your time

Kind regards
Øyvind Strøm
Question by:OyvindS

Assisted Solution

grzessio earned 100 total points
ID: 22648210
1) I wouldn`t even consider ssis (you have no microsoft databases).
2) maybe Oracle Data Integrator would be good choice (almost all of your systems is oracles).
LVL 37

Accepted Solution

momi_sabag earned 200 total points
ID: 22648304
i had work with informatica vesion 7 to load data into db2 and into oracle and i can tell you it was a very good tool.
it is very good in terms of performance.
i wouldn't use SSIS since it requires a SQL Server installation and can only run on a windows server
i believe informatica is a good choice for your need
informatica also enables you to do modular development (you can reuse process that you develop so it can shorten your development time)

Assisted Solution

asksigh earned 200 total points
ID: 22648471
if you are a large enterprise you should conider ab-initio.  It's expensive but it is truely parallel and fast.  I worked with a client who replaced datastage with ab-initio.  Development becomes GUI based.  It was a stable and dependable service.

Expert Comment

ID: 35353385
Based upon an evaluation of DataStage, Informatica, IWay, Business Objects Data Integrator SQL Server Integration Services and Oracle Data Integrator, I would say that the easiest tool to use in the Business Objects Data Integrator.  Even the most inexperienced user can develop ETL flows that work.  Informatica works with the broadest base of data types and SSIS provides the biggest bang for the buck.  DataStage is just too slow and could not keep up with our daily workload. With IWay you have to buy too many connectors for most environments.  Oracle DI is just too limited in data targets.  Also tried Ketl and Talend which work well for conversions, but I would not trust them for large volume production.

Each product has differing sets of transforms and you need to pick between them to get the tools set that fits your technology set, data quality profile and need for debugging and automation.  Informatica ran a benchmark that they published and quickly withdrew that showed SSIS as the speed leader.

Expert Comment

ID: 41554664
You might find real user reviews for DataStage, Informatica, SSIS and all the other major data integration tools on IT Central Station to be helpful:

Users interested in ETL tools also read reviews for Oracle Data Integrator. This Hyperion Applications Manager writes in her ODI review, "This is the only ETL tool I have used and never found a need to look for another. It met all our needs." You can read the rest of her review here:

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question