Data Integration Tools » SSIS Reviews » Review

SSIS Review

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

Download Case Study

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.

Oct 04 2015

1 visitor found this review helpful

Next Review: SSIS 2016 - The good…

2 Comments

GaryM

View Profile |

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 Lipski

View Profile |

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