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.
More SSIS reviews from users
...who work at a Financial Services Firm
...at Large Enterprises
...who compared it with Informatica PowerCenter
Free Report: SSIS Reviews and More
Learn what your peers think about SSIS. Get advice and tips from experienced pros sharing their opinions. Updated: December 2020.
Download now
456,495 professionals have used our research since 2012.
Add a Comment
Guest
2 Comments
author avatarGaryM
Top 5PopularReal User

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.

Reply Like (1)
author avatarit_user102120 (Business Intelligence Consultant at a tech services company with 51-200 employees)
Consultant

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.

Reply Like (0)
Author
Highlights
Built in reports show package execution and messages. Logging can also be customized so only what is needed is logged. There is also an excellent logging replacement called BiXpress that provides both historical and real-time monitoring which is more efficient and much more robust than the built-in logging capabilities. And none of this requires custom coding to make it useful unlike many other ETL tools.
The most valuables features are the relatively short learning curve, and the automation capabilities provided through the BIML add-in for SSDT.
It's already very user-friendly and has a good dashboard.
There are many good features in this solution including the data fields, database integration, support for SQL views, and the lookups for matching information.
I have used most of the standard SQL features, but the ones that stand out are the Data Flows and Bulk Import.
The most valuable features for our company are the flexibility and the quick turn around time in producing simple ETL solutions.
It is easily scheduled and integrates well with SQL Server and SQL Server Agent jobs.
See more »
Product Categories
Data Integration Tools
Buyer's Guide
Download our free SSIS Report and get advice and tips from experienced pros sharing their opinions.
Download Now
Quick Links
Learn More: Questions: