Item: SQL Server Integration Services
Rating: 10
Author: Elena Goryainova

Use Cases and Deployment Scope

SSIS is a powerful tool to perform various ETL-like functions between homogenous and heterogeneous sources of data. It was widely used in all organizations I worked since it provides an easy way to create data transformations.

Pros and Cons

Easy connection configuration
Powerful wizard for data mapping
Native exception handling
User-friendly interface
Easy to learn
Package can be deployed via Visual Studio

Requires programming experience for custom tasks
Shell version of VS used for SSIS package development doesn't support C# as scripting language (needs at least professional edition)
Some tasks are hard to debug, aren't they?

Return on Investment

SSIS provided good value for the money
SSIS comes as a feature of SQL Server Standard/Enterprise etc. editions so we don't have to spend extra money for other ETL tools

Alternatives Considered

I have used Informatica in the past and it doesn't really makes it easy for ETL. If you have some experience with SSIS, you can easily implement the same within SSIS and save money as Informatica license is much more expensive than the time spent for the development of custom ETL using SSIS.

Other Software Used

SQL Server Business Intelligence Manager, Solarwinds Database Performance Analyzer for SQL Server, SolarWinds Server & Application Monitor, Workfront

Likelihood to Recommend

I definitely recommend it. The only thing is that you have to be skilled to design a good package architecture otherwise support may be hard especially during migrations to the newer versions of SSIS engine (had that problem in the past).

SSIS is ETL's best buddy for skilled SQL Developers

Overall Satisfaction with SQL Server Integration Services