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

Use Cases and Deployment Scope

In my work in the health care field, we use SQL Server Integration Services as our default ETL tool of choice for pulling data into SQL Server from Teradata, Oracle, flat file and other SQL Server databases, and for creating data extracts to send to our clients, typically in the form of flat files.

Pros and Cons

SSIS works very well pulling well-defined data into SQL Server from a wide variety of data sources.
It comes free with the SQL Server so it is hard not to consider using it providing you have a team who is trained and experienced using SSIS.
When SSIS doesn't have exactly what you need you can use C# or VBA to extend its functionality.

SSIS has been a bit neglected by Microsoft and new features are slow in coming.
When importing data from flat files and Excel workbooks, changes in the data structure will cause the extracts to fail. Workarounds do exist but are not easily implemented. If your source data structure does not change or rarely changes, this negative is relatively insignificant.
While add-on third-party SSIS tools exist, there are only a small number of vendors actively supporting SSIS and license fees for production server use can be significant especially in highly-scaled environments.

Return on Investment

Because SSIS comes "free" with SQL Server, there is no additional one-time or recurring software license fee to pay.
SSIS has a fairly shallow learning curve so any Microsoft-oriented software development team can adopt it and put it into production service with very little training time needed.
Packages created in SSIS can easily be transitioned from development to production use either via direct deployment to SQL Server or via a deployment management system.

Alternatives Considered

IBM InfoSphere DataStage and Talend Open Studio

SSIS is a very basic, developer-oriented ETL tool and while it lacks many of the nice UX features of its competitors it is a powerful tool that comes as a part of SQL Server and, in the hands of experienced developers with domain knowledge, can meet most organizations' ETL needs without the need to purchase and maintain additional software packages in developer and production environments.

Other Software Used

Microsoft SQL Server, Visual Studio IDE, Oracle Database, Teradata Database

Performance

SSIS packages tend to run, anecdotally, slower than expected in large volume data processing environments and is sometimes better used to prototype high-performance workloads than the execute them. This is a classic case of "your mileage may vary" in a sense that for most workloads I have experienced SSIS has proven more than fast enough to get the job done. In one case, one where live web server data was being streamed into SQL Server in a web-scale ad serving system, SSIS was clearly not fast enough to keep up at any scale--and this was a very rare case.

Usability

SSIS is a great tool for most ETL needs. It has the 90% (or more) use cases covered and even in many of the use cases where it is not ideal SSIS can be extended via a .NET language to do the job well in a supportable way for almost any performance workload.

Likelihood to Recommend

SQL Server Integration Services is extremely well built for creating packages to run ETL operations in environments where the structure of the source and/or destination data never or rarely changes, however, it tends to be difficult to maintain packages in production environments where the structure of the data changes frequently.

SQL Server Integration Services: A Great "Free" ETL Tool for Most Use Cases

Overall Satisfaction with SQL Server Integration Services