none
The general state of SSIS as an Industry Standard RRS feed

  • General discussion

  • I wanted to solicit the feedback of the SSIS forum experts, as to their take on the state of SSIS as an industry standard application for processing files.  I've been using SSIS for close to a year, and have implemented some pretty complex solutions using it.  And from the beginning, I found myself annoyed by many of its quirks and limitations, some of them surrounding seemingly basic principles.  Even more annoying, is the fact that Microsoft seems to dismiss these limitations, as if they're not important.  With the release of SQL Server 2008, their obvious lack of attention to SSIS evident by it's minuscule enhancement list makes me feel that the support is not there -- by the industry, and therefore, by Microsoft.

    I care, because I've found SSIS making my life easier, overall, when it comes to processing data from and to files, and I can see its potential.  But on the other hand, I am concerned that I (and the company I work for) may be investing too much energy into a technology that does not seem to be gaining support by the industry -- and again, consequently, by Microsoft.

    Is SSIS going to continue to just be a back-burner solution, just used by a niche group of individuals, or will it eventually catch on and become more of an industry standard, and ultimately require Microsoft to put more of their own investment to improve and work out many of these obvious oversights?

    Thanks in advance for your feedback.

    Jerad
    Wednesday, November 26, 2008 7:45 PM

All replies

  • Jerad -

     

    Just to add my observations to this list:

     

    - Firstly, I would like to add that I have not seen a more active user community around any ETL tool than SSIS, and given it's age, I think that is absolutely amazing. It also makes getting support much easier than other tools -- and for a company to consider, look at TCO -- not to cheapen those of us who use Microsoft products, but it is much easier and cheaper to hire specialists with a Microsoft pedigree than Cognos, Oracle or Informatica, not too mention easier to train and develop in house talent.

     

    - Microsoft does see SSIS as a first tier solution and one that they will continue to actively support. For example, take a look at this blog post: http://blogs.msdn.com/sqlperf/archive/2008/02/27/etl-world-record.aspx, Microsoft is incredibly proud and behind this technology and product. I was at the BI conference last month in Seattle and the developers are very exicited about what is coming down the pipe.

     

    - SSIS can be frustrating with some of it's quirks -- having to wear two hats as a .Net (web and windows, so maybe three hats) plus BI and warehousing, I can see why a lot of .Net developers think that SSIS is an immature tool -- however, it has a great flexibility that makes it amazing, in some ways it feels like an open source community with all that you can do to it to enhance it yourself, third party plug-ins, custom extensions, etc. This alone makes SSIS a great tool to work with

     

    - Microsoft put a lot of effort in SQL 2008 in fixing some of the major gripes in the 2005 product -- SSRS for example is almost a completely new animal. Partitioning and federation are improved and so is scalable nodes -- while it does  seem SSIS got the short end of the stick in this release, it does have some important enhancements int he communication layer, C# support using VSTA and some new transforms -- so a lot of under the cover bits.

     

    Hope that adds some insight and gets close to what you were looking for in posting this question. 

     

    I think it would be interesting to hear what makes you think it is a back burner solution or what your perception is that made you ask this question in the first place -- would help direct the rest of the conversation so the SSIS fanatics here don't overwhelm you with all sorts of reasons to love it.

     

    Wednesday, November 26, 2008 8:06 PM
  • Thanks for your quick response, Sid.  Your comments are somewhat comforting, though I still look forward to seeing feedback from others, as well.

    In response to your question about what makes me think it is a back burner solution, I was going to list a few things originally, but I was hesitant, as I did not want the focus of the discussion to surround a specific set of quirks, but rather, the state of SSIS in general.  Also, I know that I cannot recollect all of the various limitations I've encountered.  But I will mention a couple, just to give you some examples. 

    Also, keep in mind that I am a C# developer, and I am using SSIS for SQL 2005, so a) I'm basing my impressions of SSIS 2008 on what I've read (though I have installed and played a little w/ the evaluation), and b) as you mentioned, coming from a .NET development background, it is definitely viewed by myself and my peers as being immature.

    The first, and biggest example would be the lack of an XML destination.  To my knowledge, this wasn't added to 2008 either.  And according to this suggestion, MS has already declared it as
    "not into [their] current priorities".  This comes off as a major indication that MS isn't concerned with SSIS becoming an industry standard, considering XML *is* an industry standard.  And for me, this example is almost enough on its own to justify my concerns.

    Another example would be the incompatibilities when using the FTP Task with non-Windows servers.  I'm not positive this was fixed in 2008, but I haven't seen any indications that it has been.  From the discussions I've seen on the topic (though I can't provide any evidence as in the last example), it seems again that MS is not making it a priority to fix this.

    Another is the lack of managing SQL-created identity columns.  I've had to go through some extremely overly complex hurdles to write data from files into related tables that are bound by identity keys.

    Another that I can't explain in detail, mainly because I've learned how to work around these, and I'm not sure I ever understood the problem, is the poor way SSIS manages data type conversions.  There seem to be 3 or 4 different sets of data types (SQL, .NET, SSIS), and SSIS seems to have a hard time moving data between the types (as mentioned in my next example).

    Another is the poor way SSIS handles SQL parameters in dynamic and static queries (whether in DF sources, destinations, lookups, or SQL commands).  Trying to set up parameters is very crude (you have to use numeric names, in some cases, text names in other cases), and it always seems to have a problem converting between data types.  This is extremely true when using GUIDs, which I finally just gave up and had SSIS always treat as strings.

    That's a few I can think of, off hand.  Again, many of these issues involve common practices, not just obscure practices that are only used on rare occasions.  And again, I really didn't want the focus of this discussion to be on specific issues, but rather, just the general state of SSIS in the industry, and Microsoft's attitude and plans for the technology.

    Thanks again for your valuable input.

    Jerad
    Wednesday, November 26, 2008 8:41 PM
  • Some of those issues mentioned can be very frustrating -- I think in particular some of the confusion around data types. From my perspective, this seems to center around the fact that SSIS has to work with N number of platforms, so from a .Net background, particularly C#, it appears that SSIS has lost its mind when it comes to some of this translation bits -- so while it is not immediately intuitive, I have appreciated it from a data quality standpoint -- when databases, files, etc come into play, everything from the code page in the database to the file format has to be accounted for, which most people are not used to dealing with -- reference Jamie's post here about something in this line: http://blogs.conchango.com/jamiethomson/archive/2006/06/30/SSIS_3A00_-But-it-used-to-work-in-DTS-_2800_4_2900_-_2D00_-Implicitly-Converting-Between-Data-Types.aspx. After a while, you come to appreciate that SSIS forces you to go through all the hoops, but I admit, it is incredibly tedious sometimes.

     

    Also, to second what I was saying about an incredibly active user community, check out these samples posted on Codeplex.com (which if you aren't familiar with, go there regularly):

    http://www.codeplex.com/SQLSrvIntegrationSrv. Kevin Idzi, a Microsoft developer, has posted some components for SSIS 2008 out here, one of which is an XML Destination. Sometimes when I see a requested feature from Microsoft get "sidelined", you can sometimes find a well supported extension posted on CodePlex (not always the case, but it has happened enough for me). Unfortunately, I haven't found an FTP extension yet, but that doesn't mean it is not out there.

     

    But, as you said, not to belabour specific issues, I would say that one of SSIS's greatest strength's and weakness are the user community.

     

    Well, I'm out, if your in the States, have a good Thanksgiving weekend!

    Wednesday, November 26, 2008 9:16 PM
  • I hear all that, Jared - and second most of it.

     

    I had a talk with some of the SSIS Team at that same conference Jared mentioned.  There are definitely some internals that need work - but overall I'm satisfied with the architecture.  What I really wanted to know was why the components and tasks didn't get built "better" or upgraded in 2008.  Same example you have with the Execute SQL Task and parameters - that's bush league.  Especially compared to the drag and drop nature of the column mapping interfaces for most other components.

     

    Makes you wonder "what the SSIS team works on all day" - although that's an exceedingly easy question to ask when you're not in their seat. Smile

     

    One of the reasons I can see for MS not "fleshing out" their stock toolsets is their desire to cultivate the 3rd party ecosystem.  There's no reason for a third party like CozyRoc to develop tasks and components if they're constantly at risk of having MS replace their functionality in the next release.  It's a tough position to be in for MS, certainly.

     

    If that's the case - I would really like MS to "fix"/improve the components that are already there - the parameter mapping for example.  To accompany that, they NEED to provide some more information on how to build "nice" components.  How to use those undocumented features like the expression evaluator, column mapping control, toolbox installer, etc...  Their CodePlex samples site would be a great place for that.

     

    It's good to know you're coming from a C# dev background - now all my answers to your questions will be of the form "use a Script component and..." Smile

     

    BTW - CozyRoc does have an FTP task - never used it, but you should check it out.

    Wednesday, November 26, 2008 10:01 PM
    Moderator
  • Hi

    I am a Program Manager on the SSIS team. Please send me an email and i will like to discuss this thread offline with you guys.

     

    Thanks

    Ram

    k.ramakrishnan@microsoft.com

    Sunday, November 30, 2008 8:57 PM
  •  Jerad Rose wrote:
    I wanted to solicit the feedback of the SSIS forum experts, as to their take on the state of SSIS as an industry standard application for processing files. 

    Jerad,

    Here are my views as a trainer and user of SSIS.

    While DTS (SQL Server 7.0/2000) was embraced by the casual SQL Server user as well as the heavy weights, Microsoft targeted SSIS (SQL Server 2005) as an ETL platform which can even compete with Informatica and other popular ETL tools in the enterprise environment. As such I do think Microsoft will expand the functionality and improve the development/deployment interface at a rapid pace.

    However, what would really make a big difference in the IT space is not adding feature x and feature y, rather defining  SSIS Light for the  SQL Server developer (reluctant ETL programmer) which would work in the design, debug, run and schedule mode directly in Management Studio and Visual Studio.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    • Edited by Kalman Toth Monday, October 1, 2012 6:24 AM
    Sunday, November 30, 2008 11:53 PM
  • I'm one of the development leads in SSIS and I can assure you that we not only intended to continue but also to increase our investment in Integration Services.  You bring up valid concerns over some basic functionality that could clearly be improved.  There are many potential areas to improve our product and we can’t cover everything and recognize that there are some pretty basic issues still to address in some areas.  We do value your feedback and this feedback does help guide our future investments.  (I have reactivated your XML Destination suggestion for consideration in a future release.) We are committed to the continued success of SQL Server Integration Services and our users who build solutions upon SSIS.   I’m truly excited about the plans we have for the next major release of Integration Services.

    Monday, December 1, 2008 6:32 PM
    Moderator
  • Just to as to what matt said .

     

    Investments around SSIS is not going away , it is an important product unit for Microsoft.

     

    There is a constant effort from the SSIS team to monitor forums and look out for areas where we can improve and make the product better. we also work with Large Customers , MVP's and Microsoft Field as sources of feedback to improve the product.

     

     Case in point with SSIS 2008 , we added new high speed connectors to Oracle and Teradata. Added a new SAP BW connector. These are definitely in the direction of making SSIS an Industry ETL tool.

     

    Keep the feedback coming

     

    Ram

    Monday, December 1, 2008 8:41 PM
  • (Well if nothing else, you'll know that the SSIS team does read the forum :-)

     

    I'm the manager of the SSIS team, and I thought I'd add my perspective.  As I've seen others mention on this thread already, we did make some substantial improvements in SQL 2008, but we also left a number of things undone --- in some cases things we really did and do want to fix (our support for XML generally is one of my personal pet peeves).

     

    But we had some choices to make, and one of the factors we used was whether customers or 3rd parties could work around the problem.   Many of the tasks were let be, because we do encourage an ecosystem to add to the set of available ones --- and we ourselves have followed this route by putting some things into open source at Codeplex (look here).  In the product we instead focused on areas like debugging and core performance, that only we could do.

     

    We also focus based on volume of customer requests.  As it happens, the volume of requests for XML is very low compared to the volume of requests we were hearing for efficient out-of-box database access and for SAP access.  It is through feedback like what we see in this forum, and on Customer Connect, that we get that sense of priority --- so keep it coming.

     

    In truth SQL 2008 was overall a relatively light release for SSIS --- there were other priorities that conflicted.   But I'm expecting the next major release of SQL Server (not the recently-announced Kilimanjaro, but the unnamed one after that) to be a really big release for us --- I think we'll demonstrate conclusively that we are committed to making an SSIS that can "provide customers the ability to easily develop, deploy and manage enterprise-quality solutions to a wide set of Data Integration scenarios" (our vision statement).

     

    denise

    Tuesday, December 2, 2008 3:26 AM
  • Thanks again for the great feedback, particularly those from the SSIS team.  It's good to hear that you guys are keeping up with the feedback, and your insight does help explain a lot of questions and concerns I (and apparently others) have.

    Looking back at my points (and thinking about some I didn't mention), outside of an XML destination, most of the issues/concerns seem to be related to a general lack of polish.  I've been a Microsoft developer for over 10 years (starting in days of VB5 and ASP), and I've noticed a general pattern of focusing on features over polish, so I'm definitely not pointing fingers at your team specifically.  For example, I've continually been annoyed when a service pack comes out, hoping to see some obvious issues and bugs worked out, only to see them still there.  And this isn't just my personal opinion, this is the general consensus I've gathered from my colleagues over the years.

    It would be nice to see a change in this pattern in SSIS, as it seems to be particularly true there.  Many things (such as awkward parameter mappings, issues w/ data type conversions, dealing w/ related tables joined via identity keys, etc.) are just the result of a lack of polish, IMHO.  And to your point, Denise, I think these are things that only the SSIS team can address, as opposed to things like an XML destination that can be addressed by the community and third parties.  But again
    , I must reiterate that we currently are not on SQL 2008, so my experiences are mostly based on SSIS 2005 and what I've read on SSIS 2008.  SSIS 2008 may have improved these things, but based on discussions and what I've read, they haven't been.

    SSIS is definitely a huge step ahead of DTS, but it still seems to be in its infancy, again due to this general lack of polish.  I'm a pretty big advocate of SSIS in my department, and see its potential.  But some of my peers and managers see some of the struggles I face and the workarounds I have to put in place (and in some cases, we have to abandon SSIS altogether), and it's becoming more difficult to sell it as the primary tool for ETL processes for our company.

    Thanks again for your feedback, and for listening to ours.

    Jerad
    Tuesday, December 2, 2008 2:57 PM
  • Jerad,

    I know this new development is probably too late for your situation. We have released CozyRoc Template Task , which provides generation of arbitrary type of text documents (including XML) without programming. If you have time to try, we will be happy to hear your feedback.
    SSIS Tasks Components Scripts | http://www.cozyroc.com/
    Wednesday, August 5, 2009 8:13 PM
  • Jerad,

    You really need to take a look at the industry standard ETL products (Cognos, Informatica, Datastage, Pentaho etc etc) to get a full appreciation of what a lame duck SSIS really is.  The only reason anybody would use it for anything, except maybe as part of a bad Software Engineer joke, is that it is cheap. 

     

    Tuesday, September 28, 2010 7:33 PM
  • You really need to take a look at the industry standard ETL products (Cognos, Informatica, Datastage, Pentaho etc etc) to get a full appreciation of what a lame duck SSIS really is.  The only reason anybody would use it for anything, except maybe as part of a bad Software Engineer joke, is that it is cheap. 


    Informatica and DataStage are much more mature and expensive products, so yes, SSIS doesn't do everything they can do. Although they have their own flaws which make them far from perfect, and often frustrating to develop in.

    The fact that you mention Cognos in the same scope leads me to believe you aren't an ETL expert - Data Manager is old and outdated, and only any use for building basic star schema warehouses.

    SSIS is easy to work with, cheap, extensible and very, very fast. For a lame duck, it can kick a fair bit of .... well, you can work that out.


    James Beresford @ www.bimonkey.com
    SSIS / MSBI Consultant in Sydney, Australia
    SSIS ETL Execution Control and Management Framework @ SSIS ETL Framework on Codeplex
    Thursday, September 30, 2010 1:40 AM
  • You really need to take a look at the industry standard ETL products (Cognos, Informatica, Datastage, Pentaho etc etc) to get a full appreciation of what a lame duck SSIS really is.  The only reason anybody would use it for anything, except maybe as part of a bad Software Engineer joke, is that it is cheap. 


    Informatica and DataStage are much more mature and expensive products, so yes, SSIS doesn't do everything they can do. Although they have their own flaws which make them far from perfect, and often frustrating to develop in.

    The fact that you mention Cognos in the same scope leads me to believe you aren't an ETL expert - Data Manager is old and outdated, and only any use for building basic star schema warehouses.

    SSIS is easy to work with, cheap, extensible and very, very fast. For a lame duck, it can kick a fair bit of .... well, you can work that out.


    James Beresford @ www.bimonkey.com
    SSIS / MSBI Consultant in Sydney, Australia
    SSIS ETL Execution Control and Management Framework @ SSIS ETL Framework on Codeplex
    Data Manager??? kidding right?? I bow to your advanced knowledge of various ETL tools.  And I will keep the extensible part in mind next time I wait for SSIS to re-validate a simple data stream or re-load my designer a few times after it has crashed after encountering  another inexplicable memory error.  SSIS is a product which was developed at great expense and finally pushed out the door after a full seven years of development.  It is flawed, incomplete and was designed with seemingly no regard for how any other ETL software works.  However, if somebody wants to pay me by the hour to play with it rather than using something which actually works, why I think that is just fine :)))
    Thursday, March 10, 2011 4:14 PM
  •   However, if somebody wants to pay me by the hour to play with it rather than using something which actually works, why I think that is just fine :)))


    Certainly, you can get a high-paying job with solid SSIS expertise.

    A bit of statistics from dice.com :

    SSIS jobs > 1500

    Data Manager jobs < 100

    Informatica jobs = 1500


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Edited by Kalman Toth Tuesday, October 23, 2012 11:06 PM
    Thursday, March 10, 2011 5:34 PM