Item: SQL Server Integration Services
Rating: 5
Author: Alex Bransky

Use Cases and Deployment Scope

SSIS pulls data from various locations (mostly SQL Server instances but also some flat files and SFTP), mainly for transforming the data and putting it in the data warehouse.

Pros and Cons

SSIS is good for small, simple data syncs.
SSIS is good for moving data quickly into SQL Server (good batch performance).

Very hard to diff for code review when changes are made.
Very buggy IDE (freezes in Visual Studio, doesn't always show what's in the underlying XML properly)
Lacking lots of features or more modern tools, like ability to monitor for changes to the source schema and apply them to the target.

Return on Investment

SSIS provided good value when there were few competitors, for somebody already using SQL Server Enterprise Edition.

Alternatives Considered

Fivetran, Stitch, from Talend and Etleap

Fivetran, Stitch, and Etleap are all 1000x more modern than SSIS and 100x less aggravating. While those tools are mainly used to sync data rather than transform it, the ELT model works much better than the ETL model in most situations.

Key Insights

Do you think SQL Server Integration Services delivers good value for the price?

Are you happy with SQL Server Integration Services's feature set?

Did SQL Server Integration Services live up to sales and marketing promises?

I wasn't involved with the selection/purchase process

Did implementation of SQL Server Integration Services go as expected?

Would you buy SQL Server Integration Services again?

Other Software Used

Snowflake, Fivetran, MuleSoft Anypoint Platform

Likelihood to Recommend

If you already have licenses for SQL Server and need to get data from A to B, especially if A and/or B is SQL Server, then SSIS could be a reasonable choice, especially if you can't use a cloud tool like Fivetran due to security concerns.

SSIS had its day, but in the big leagues it can no longer play

Overall Satisfaction with SQL Server Integration Services