Item: SQL Server Integration Services
Rating: 8
Author: Verified User

Use Cases and Deployment Scope

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 and Cons

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

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.

Return on Investment

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.

Alternatives Considered

Denodo

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.

Other Software Used

Microsoft Power BI, Microsoft SQL Server, Denodo

Performance

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.

Usability

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).

Likelihood to Recommend

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 - Strong performer in the traditional data space, but can be improved in handling JSON objects

Overall Satisfaction with SQL Server Integration Services