SSIS - Strong performer in the traditional data space, but can be improved in handling JSON objects
Updated June 07, 2019

SSIS - Strong performer in the traditional data space, but can be improved in handling JSON objects

Anonymous | TrustRadius Reviewer
Score 8 out of 10
Vetted Review
Verified User

Overall Satisfaction with SQL Server Integration Services

We implement data solutions for our clients. We use SSIS frequently as an ETL and Data Integration Tool for clients that have the Microsoft enterprise licensing as it offers decent base-level capabilities for traditional database management systems, and we can justify the tool selection since the technology cost-effective under MS licensing.

Pros

  • Very good for traditional RDBMS ETL and integration.
  • Good developer community support online.
  • Good at ingesting structured flat files (CSV, TXT, Excel).

Cons

  • The tool struggles out of the box handling emerging datasets such as JSON feeds.
  • Unstructured datasets can be challenging to work with.
  • Some out of the box can be very resource heavy, and the UI is not very straight forward. Luckily there's a large community of SSIS users that can provide guidance.
  • We have delivered numerous projects to our clients using SSIS where the client has been very happy with the delivery of our solution.
  • The big struggle with SSIS is having multiple devs on a single SSIS package, this can create merge nightmares from a source control standpoint.
  • Overall SSIS is a good option for clients who have Microsoft enterprise licensing. It provides a good set of capabilities for RDBMS systems, but you will need more custom dev if you are ingesting emerging feeds such as JSON objects.
SSIS and Denodo differ in their approaches to ETL and Data integrations. SSIS is more affordable from a cost and licensing perspective (if you have Microsoft licensing), but Denodo is no slouch. If you go with Denodo, you are not creating data, there are pros and cons to that. With SSIS, lots of times you are persisting information in order to perform ETL. Again, there are pros and cons to this, but both require proper implementation and data architecture.
Performance is relative to how you implement your solutions. Proper architecture and design are crucial regardless of what tool you select for your data solutions. That being said, SSIS performs relatively well from a performance standpoint.
SSIS has a drag and drop based developer interface, so it is relatively straight forward to get started. You can start to get into the weeds pretty quickly as your solution becomes more complex. However, most of the base functions are right in front of you for a developer. You can also set project and solution level parameters, so when you deploy to new environments, you don't have to jump into each package to change your variables and settings. (For example, default directory to ingest flat files).
SSIS is very well suited if your project(s) involves ETL or data integration of traditional relational database systems.

SSIS may not be the best tool if you are using it for ETL and data integrations for JSON and XML feeds. The native tree parser is not very good.

SSIS Feature Ratings

Connect to traditional data sources
9
Connecto to Big Data and NoSQL
Not Rated
Simple transformations
10
Complex transformations
6
Data model creation
9
Metadata management
7
Business rules and workflow
7
Collaboration
9
Testing and debugging
7
Integration with data quality tools
Not Rated
Integration with MDM tools
Not Rated

Comments

More Reviews of SSIS