Is SSIS a dinosaur in Modern ETL?

  • KJKKPSI

    SSCommitted

    Points: 1514

    Hello forum,

    Technical leadership within my company are pushing a move away from SSIS and the Microsoft tool sets and into other tool sets such as Talend for our ETL\ELT development. The reasoning behind the push is the argument that SSIS limits us to the Microsoft stack and requires developers to support and maintain, whereas a move to Talend would allow non-developer staff to handle integration task more easily due to it's drag and drop interface.  Also it's been stated that this tool will allow us to better adjust to any move to diversify our data architecture in the future.

    So, I am curious if anyone else in the market have done the same, or perhaps used a similar tool to Talend in place of or together with SSIS for your ETL needs?

    This push is a hard pill to swallow to be honest as it involves resetting much of my teams' core software skill set to zero in favor of an open-source product that up until this push I had never seen reference to prior, and in the demo provided really didn't show me it's value over SSIS for the price.

    Is SSIS really the dinosaur that I'm being lead to believe in the modern ETL world? Are we really that handcuffed that bringing Google Cloud, AWS, or anyone cloud provider outside of Azure is enough to make this switch worth it?

    Thank you

  • Martin Schoombee

    SSCoach

    Points: 19017

    The notion that opensource software (or any ETL software for that matter) does not require developers to support and maintain is marketing BS. I'd argue that while opensource software is sometimes more cost effective, you usually pay for it in development time (and dollars).

    Addressing SSIS specifically, I think it comes down to what your source(s) and destination(s) in your ETL process are. If you're importing text files only, then it really doesn't matter what ETL tool you use as they will all pretty much give you the same functionality. But if you're extracting data from something like Oracle and your destination is SAP and you'd prefer not to use orchestration (i.e importing the data without much transformation), other tools may give you more of what you need.

    Ecosystem is an important consideration, as Microsoft tools naturally integrate better with other Microsoft tools. So I guess the question is whether your company intends to move everything to opensource, and if they are then it could (but not necessarily will) be a good idea to consider other ETL tools. It all comes down to your current methodology, required functionality and other elements within the architecture...you definitely should not look at the ETL tool of choice in isolation. My number one comment to customers in this type of a scenario is: "Pick the right(best) tool for the job".

  • Matt Miller (4)

    SSC Guru

    Points: 124184

    I'd also take issue with the oversimplification that "SSIS only deals with the Microsoft stack".  there are a number of built-in connectors, and support for OLEDB connectors and other such add-ons that give access to just about any tech stack, flat or semi-structure files, etc..... Besides it has been ported to run on Linux as well as I recall for the last few years.

    Not to say the SSIS is without its faults (no tool is), but that particular characterization really isn't accurate from what I have seen.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Martin Schoombee

    SSCoach

    Points: 19017

    Matt Miller (4) wrote:

    I'd also take issue with the oversimplification that "SSIS only deals with the Microsoft stack"...

    Completely agreed. This line of thinking is really archaic and stems from many years ago when Microsoft tools didn't play well with others outside its ecosystem. We're in a different world now, and that is no longer a valid argument.

  • Jeff Moden

    SSC Guru

    Points: 994682

    KJKKPSI wrote:

    whereas a move to Talend would allow non-developer staff to handle integration task more easily due to it's drag and drop interface.

    THAT RIGHT THERE is a very strong reason to NOT aquire such a tool.  It's bad enough that, when it comes to data, many Developers don't actually have a clue especially about the ramifications of what they're doing to the underlying box.  Putting such a took in the hands of people that are even more ignorant in that area is a series of accidents and bad data just waiting to happen.

    I also find that demos are meant to be "sensational".  And equally good demo can be and had been done for SSIS many times.  Remember that everything is "perfect" in Demos.  Of course, you stated...

    KJKKPSI wrote:

    the demo provided really didn't show me it's value over SSIS for the price

    ... and so you're already well aware of one of my favorite quotes:

    "Change is inevitable... change for the better is not".

    I would challenge the makers of Talend... have them set you up so that you and your people can do a head-to-head comparison because "One good experiment is worth a thousand expert opinions".

    As for SSIS...I don't care for it either (I don't actually care for any of this drag'n'drop stuff because it turns out that's usually not all that's necessary) but it's free and, in the right hands, will get the job done well.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • ZZartin

    SSC-Dedicated

    Points: 30356

    So you have two points.

     

    First letting non developers do ETL, while companies will love to pitch you all day about how easy their drag and drop tool is for non developers(SSIS is also a drag and drop tool so....) they are simply wrong.  Talend is if anything less non developer friendly than SSIS.

     

    Second, yeah SSIS generally works really well in the MS stack, it gets a little worse when you have to work outside the MS stack.  And there is some valid argument that if you are working with a number of diverse data sources you might be better off with another tool.  Some other tools also do have better change control and deployment methodologies than SSIS which might be changing as MS seems to be adopting GIT and other technology into SQL Server.  Then again the question is do you actually need that?  If you aren't working outside the MS stack, and your ETL is relatively straight forward or doesn't change very often do you get anything out of actually moving to another platform on the theoretical that you might need it some day?

  • Chris Harshman

    SSC-Forever

    Points: 41764

    I'd have to agree with Jeff's comments about the "non-developer staff" doing ETL.  While there are typically a small handful of true power users who understand what they need to do and the implications of doing it wrong, most business people don't.  I think of all the bad Access databases and Excel spreadsheets I've seen over the years, and how much time coworkers and myself put into fixing those or even replacing them.

  • KJKKPSI

    SSCommitted

    Points: 1514

    Thank you all for the responses. I am a big supporter of staying within the MS stack and taking the natural migration to Azure, but there is a major push to "consider' an AWS platform as part of our cloud migration paths going forward. Our company has historically been very static with our technologies, to a considerable fault. We are at a breaking point so to speak and change is coming and necessary. At this point the decision is made and we are moving forward.

    This post was more so to understand if my core knowledge base in SSIS as an ETL developer, is based on something that the industry is truly leaving behind.

    It's obvious that it's not, but at the same time it's also clear that I need exposure to more technologies outside of the Microsoft realm.

  • David Burrows

    SSC Guru

    Points: 64521

    Jeff Moden wrote:

    As for SSIS...I don't care for it either (I don't actually care for any of this drag'n'drop stuff because it turns out that's usually not all that's necessary) but it's free and, in the right hands, will get the job done well.

    +1

    I have been removing or replacing a small number of SSIS packages as I found that in most cases became top heavy for the type of processing required. Also since moving to SQL 2016 I don't care much for the changes in storing packages and found it cumbersome and mostly not being able to connect to SSIS via SSMS.

    The only exception is for daily transfer of 40GB in 140 KISAM files from a third party system and found SSIS the fastest method.

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Jeff Moden

    SSC Guru

    Points: 994682

    Jeff Moden wrote:

    The only exception is for daily transfer of 40GB in 140 KISAM files from a third party system and found SSIS the fastest method.

    I don't know what a KISAM file is but wouldn't mind having a go at it for the sake of learning something new.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 994682

    KJKKPSI wrote:

    Thank you all for the responses. I am a big supporter of staying within the MS stack and taking the natural migration to Azure, but there is a major push to "consider' an AWS platform as part of our cloud migration paths going forward. Our company has historically been very static with our technologies, to a considerable fault. We are at a breaking point so to speak and change is coming and necessary. At this point the decision is made and we are moving forward. This post was more so to understand if my core knowledge base in SSIS as an ETL developer, is based on something that the industry is truly leaving behind. It's obvious that it's not, but at the same time it's also clear that I need exposure to more technologies outside of the Microsoft realm.

    I guess I'd have to ask what the niche is that they're trying to fill here is.  I get that it's ETL but what kind of files are are they trying to handle?  And, yes... I understand it's a "done deal" but curiosity abounds. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • David Burrows

    SSC Guru

    Points: 64521

    David Burrows wrote:

    Jeff Moden wrote:

    The only exception is for daily transfer of 40GB in 140 KISAM files from a third party system and found SSIS the fastest method.

    I don't know what a KISAM file is but wouldn't mind having a go at it for the sake of learning something new.  

    They are indexed files in a Kerridge system (now ADP) and, AFAIK, based on ISAM hence the third party reference.

    I use a bespoke ADP ODBC driver to a access the files.

    https://en.wikipedia.org/wiki/ISAM

    Far away is close at hand in the images of elsewhere.
    Anon.

  • AlphaTangoWhiskey

    SSChampion

    Points: 10786

    I guess I'm focused on this statement:

    a move to Talend would allow non-developer staff to handle integration task more easily due to it's drag and drop interface

    I'm an experienced SSIS developer and recently worked with another dev (about 3 years exp) troubleshooting an SSIS issue. Took us a while to nail down the issue. A non-dev would not have a prayer to solving it or even know where to start. I'm not sure what Talend offers, i have no experience with it, but i know its either super simple and will not fill your needs or non-devs wont have a chance.

    Furthermore one of the best tools for documentation and troubleshooting is <insert search engine here> and i know from experience dealing with an unpopular system is frustrating when it comes to finding answers. On that same note if you're into third party components SSIS has a ton available or you can build your own.

    I'm definitely biased, but SSIS is not a dinosaur by any means. Back in 2005 yeah maybe, but not today.

  • KJKKPSI

    SSCommitted

    Points: 1514

    There is nothing special about what we do and  we are not breaking molds. The goal as I understand it is to diversify our architecture and the argument is that other products that are not so tightly coupled to a platform (SSIS to MS for instance) are a better choices that will allow us to lift and shift between architectures (Azure, AWS, Google, Etc) without the need for complete redevelopment in the future.

  • KJKKPSI

    SSCommitted

    Points: 1514

    This confused me as well when I first heard and honestly enraged me a bit as well.

    But to be honest...it's been cleared up somewhat since. The goal is not to give it to non-technical but instead to give it to a new DevOps group,  which currently consists of non SQL staff...so I imagine there will be new talent being engaged. But it still gives someone else the ability to put data into my db without our knowledge and I question as to what happens when it all goes pear shaped.

    Our team has up until now wore every hat in the db arena. Admin, Dev, etl, etc. So this is very much like losing a limb to us.

     

Viewing 15 posts - 1 through 15 (of 17 total)

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