SSIS Review

All the necessary types of enumerators are available, but writing custom components and sharing them across multiple ETL streams is tricky.


Valuable Features:

  • Compatibility with the rest of Microsoft BI stack
  • Scripting component which opens unlimited possibilities: by having the full .Net stack/libraries available, if you need a yellow rubber duck to quack every million rows, it will quack
  • Inter-stream dependency mechanism built directly into the tool; the whole ETL solution can be driven off it without having to employ third party software
  • Parallel processing of a data flow: let's say there's five steps in the flow and ech step consumes output from the preceding step; once step #1 is completed and its data passed to step #2, the engine will feed the next batch of rows to step #1. Once the first batch of rows reaches the last step, there are five concurrent steps working on five different data sets, maximizing utilization of server resources
  • All the necessary types of enumerators (files, xml nodes, rows and many more)

Improvements to My Organization:

  • SSIS packages are stored in XML-like format; we keep them in SVN which makes it very easy to track changes
  • Built-in dependency tool is really great; you can not only react to success/failure results, but you can evaluate any expression in the flow and execute (or bypass) any components based on the result of that expression. This gives you full control to build decision trees of any complexity

Room for Improvement:

  • Writing custom components and sharing them across multiple ETL streams is tricky and requires specialistic voodoo knowledge
  • Make the GUI less eye-candy and more responsive; especially the scripting component needs some TLC regarding UI responsiveness

Initial Setup:

It's easy to deploy, you just need a simple file-copy mechanism that is sufficient for most deployment scenarios.

Other Advice:

The good:

  • Straightforward, intuitive, quick to learn
  • Built-in debugger: variables, data viewers, breakpoints - everything you would expect from a modern software development tool
  • Sleek, snappy UI
  • Great flexibility with storing and deploying ETL components

The bad:

  • Third party plug-ins may become incompatible with future versions of SSIS
  • Upgrading is usually painful and time consuming despite what MS says
  • Compatibility issues may arise when used with non-Microsoft technologies
Disclosure: I am a real user, and this review is based on my own experience and opinions.
1 visitor found this review helpful
2 Comments
GaryMReal UserTOP 5POPULAR

Note regarding deployment...although file-copy is one way of running it that's not recommended. The tool comes with SSISDB database for deployment to SqlServer server that provides logging, reporting and storage on the server that includes versioning. It also integrates with Visual Studio Online with TFS for full cloud based management.

Good points about upgrading and third party plug-ins I'd missed in my review, SSIS The good, the bad and the ugly. Also regarding the slowness of the script component...I'm looking forward to 2016 version in hopes that some of the annoyances will be gone.

07 October 15
Piotr LipskiConsultantTOP 20

Thanks Gary. Yes, I am aware of the new SSISDB database and I am slowly migrating some of the projects to it. It is much more convenient, especially the way you can configure your solution on per-environment basis. Plus it is flexible, too. You can have just one instance of the SSISDB database for all your environments or one instance per environment, or even some hybrid mode. You can control and track progress of your executions using purely T-SQL, so lots of goodies. Haven't had a chance to play around with 2016 yet but things seem to be going in the right direction anyway.

07 October 15
Guest

Sign Up with Email