Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

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: https://goo.gl/49rv0Q

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: https://goo.gl/wVJBv7

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Script to Remove Data from Two Joined Tables 1 19
Stored Proc - Rewrite 42 55
format dd/mm/yyyy parameter 16 29
SQL Recursion 6 16
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

792 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