Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Jul 2009
    Posts
    10

    Unanswered: Best ETL solution for MS SQL Server

    Hi All

    I'm looking at different ways of implementing a simple, but extensible ETL application in SQL Server and was wondering what people have previously used. I've played with SSIS and, though it does a lot of what I'm after, it doesn't seem particularly extendable (e.g. allowing for quite sophisticated transformation processes) and it's also not as easy to use as it could be!

    Has anyone used any other tools? Either Open Source or otherwise? And what are the things I should be looking for in a package (i.e. why are certain packages better than others)? I know there are some quite expensive platforms out there, and I'm hoping I don't need to go for any of these (i.e. are there any packages which provide most of the key functionality)?

    Thanks for your help..

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    SSIS is tough? nah. it's just like everything else. you ought to read the manual and play with it a little. now bcp can be a tough etl tool to get used too.

    I have been doing database work for a decade and I have never even seen anyone use a 3rd party etl tool. I can not even name one.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    The big players in Integration include Informatica, IBM DataStage and SAP. When you consider that you can easily pay $100,000 or more for any of those products then SSIS looks like a pretty good deal. If your target database is SQL Server then SSIS is almost certainly a good choice.

    I'd be interested to know what you mean by "sophisticated transformation processes". SSIS is a pretty good execution platform for a lot of things and I've seen some complex transforms done with it. Do you have some specific problem or limitation in mind?

    One thing SSIS does lack is a good framework that unifies metadata, orchestration, error handling and logging. You need to develop that yourself. But once you've done that, if you make it flexible enough then you can use your framework again and again. That's what many people do.

  4. #4
    Join Date
    Jul 2009
    Posts
    10
    Thanks for the replies - by "sophisticated transformation processes" I really mean being able to run Java/.NET code on a current result set - necessary when something just can't be done in SQL. At a previous employer we had a sophisticated tool that allowed this (written from scratch), but I certainly don't want to repeat that bit of development work!

    So, perhaps something that allows the user to call SQL and .NET code from, say, an XML script of some sort - this would be very useful for me..

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Why would you want to run Java/.net code during Extract and Load? Transform should all occur in the database AFTER the date is loaded into a staging table.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by benrees
    Thanks for the replies - by "sophisticated transformation processes" I really mean being able to run Java/.NET code on a current result set - necessary when something just can't be done in SQL.
    For Example?

    bcp and TSQL ARE my "ETL" tools
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'll use DTS/SSIS for E and L, but never for T. Sprocs only, please.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman
    I'll use DTS/SSIS for E and L, but never for T. Sprocs only, please.

    Never run into any hiccups?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ....and when did ETL become a buzz word?

    late 90's?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by benrees
    So, perhaps something that allows the user to call SQL and .NET code from, say, an XML script of some sort - this would be very useful for me..
    SSIS can do that. Script component in the data flow or the control flow. XSLT is supported also.

  11. #11
    Join Date
    Jul 2009
    Posts
    10
    Quote Originally Posted by dportas
    The big players in Integration include Informatica, IBM DataStage and SAP. When you consider that you can easily pay $100,000 or more for any of those products then SSIS looks like a pretty good deal. If your target database is SQL Server then SSIS is almost certainly a good choice.

    I'd be interested to know what you mean by "sophisticated transformation processes". SSIS is a pretty good execution platform for a lot of things and I've seen some complex transforms done with it. Do you have some specific problem or limitation in mind?

    One thing SSIS does lack is a good framework that unifies metadata, orchestration, error handling and logging. You need to develop that yourself. But once you've done that, if you make it flexible enough then you can use your framework again and again. That's what many people do.
    On your last point - isn't this at least part of what the Business Intelligence Dev Studio does? I.e. provide a framework for the ETL process, allowing you orchestrate the whole process? This is a key part of what I'm looking for - a way of not only creating quite complex processes/scripts, but also a method to manage and orchestrate them. Perhaps BIDS will do the trick...

  12. #12
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    BIDS is just a development tool. It doesn't do anything for you in a production environment.

    What I mean is that SSIS doesn't provide any built-in metadata repository, no simple way to manage dependencies between separate streams of processing, track history or load-balance between servers.

    SSIS does provide an excellent execution framework for individual streams of processing of all kinds. You can build the rest yourself but I recommend you do take the time to create those generic framework components such as a metadata database for logging events, errors and outcomes.

  13. #13
    Join Date
    Jul 2009
    Posts
    10
    Quote Originally Posted by dportas
    BIDS is just a development tool. It doesn't do anything for you in a production environment.

    What I mean is that SSIS doesn't provide any built-in metadata repository, no simple way to manage dependencies between separate streams of processing, track history or load-balance between servers.

    SSIS does provide an excellent execution framework for individual streams of processing of all kinds. You can build the rest yourself but I recommend you do take the time to create those generic framework components such as a metadata database for logging events, errors and outcomes.
    So if I develop some processes (DTSX packages etc) in BIDS, am I supposed to use SQL Agents to deploy these? I've had a quick look at this, and these seem a little.. unstructured. Is that what you're saying - that SSIS provides a mechanism for running autonomous streams, but there's not real method for orchestrating the whole process (unless SQL Agents are supposed to do this)?

  14. #14
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    why don't go on msdn and do a few tutorials in SSIS. I am at a loss for what you mean by orchestrate. SSIS is a big pretty graphical tool that you can set up to easily make concurrent processes with within the same package. Some of your comments come off like you have not even tried out the thing you are trying to replace.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Brett Kaiser
    Never run into any hiccups?
    Nope. ETL development is about 30% of my business, and I have a pretty robust and repeatable methodology worked out.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •