Post reply

Anyone using an ETL tool besides DTS?

  • March 8, 2005 at 10:48 am

    #67078

    Anyone using an ETL tool besides DTS?  

    We use DTS for simple Database Transformations. 

    We use Informatica ETL PowerCenter for complex Transformations from our iSeries AS/400 to SQL Server Database. 

    I was wondering if anybody else is exporting data from the iSeries, and what ETL tool is used.

  • March 8, 2005 at 11:59 am

    #545978

    There are a few that work on the iseries.  The one we use is DataMirror TS.  We do Real-Time replication w/minimal transform from an AS400 OLTP system to a SQL server Datawarehouse and to a Real-Time ODS (Operational Data Store) that drives some other applications.

    Feel free to E-mail me at mike.biesanz@absg.com for specifics.  The jist of it:  Datamirror is pretty good at the "E" and the "L" of ETL.  The "T" in Datamirror is done using proprietary script language and has some hefty overhead to it on large databases or complex transforms.  We do most of the heavy lifting once it's on SQL server through a complex batch of DTS's.

    It works OK.  It's not cheap.  Don't care for the thier licensing scheme (I don't like to pay at all for Dev, Test, or passive cluster node licenses as a rule).

  • March 9, 2005 at 12:45 am

    #546049

    We are using the ETL tool from Cognos called "Decision Stream". It is quite expensive, but pretty good in tracking so called slowly changing dimension.

    It is easy to use and you do not need to be a SQL guru.

    Matthias

  • March 9, 2005 at 6:22 am

    #546122

    We also us DataMirror TS.  Very similar set-up to Mike's.  AS400 OLTP to Sql Datawarehouse.

    We do a lot of date transformations (char or num to datetime).  The product works well for our environment, and I used to be a big fan....however,

    I completely agree on the tier pricing.  We hate their tier pricing!!  It's a driving factor for a server consolidation project we are working on.  No price breaks for Test or Dev. 

  • March 9, 2005 at 7:17 am

    #546149

    I have used both Cognos Decisionstream and DTS to access the iSeries and create datamarts.  Using scripting and the datapump you can do just about anything you need to do in DTS except slowly changing dimensions.

    Good luck..

  • March 9, 2005 at 8:49 am

    #546166

    Vitria is another to potentially look at.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • March 11, 2005 at 8:59 am

    #546658

    Thanks for everyone's feedback...hopefully the new yukon DTS will be able to replace my current ETL software....

  • March 12, 2005 at 2:29 am

    #546761

    Richard,

    SSIS (the new DTS) is much more functionally rich than DTS. DTS doesn't really have any heavy lifting capabilities whereas SSIS most definately does and seeing as you are using Informatica I am guessing that that is what you are using it for.

    The seperation of data-flow and control-flow in SSIS is analogous to the seperation of mappings & workflow in Informatica so if you did switch it wouldn't be a huge leap for you. Watch out for a Microsoft whitepaper in the next couple of months that demonstrates SSIS's ability to operate in the same space as Informatica.

    I admit to being a little bit biased because I've done lots of work with SSIS already and am totally hooked on it. Check out my blog for lots of info on the subject!

     

    Jamie Thomson
    http://sqlblog.com/blogs/jamie_thomson

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply