Item: SQL Server Integration Services
Rating: 9
Author: Steven Gockley, MBA, MCSA

Use Cases and Deployment Scope

We use SSIS in a number or different use cases. 1. Traditional ETL for our data warehouse. 2. Automate reporting. We have a number of reports that go out to thousands of customers daily. We have implemented SSIS to run these. 3. Application integration. With SSIS we are able to quickly integrate from our order entry and operations software into our ERP and Financial software.

Pros and Cons

It sits inside of Visual Studio and SSMS so you have a consistent look and feel across toolsets
Extensions. We use Pragmatic work transforms that seamlessly fit into SSIS to make certain tasks easier
Performance. SSIS is not the fastest tool out there but it is more than enough for our needs and since it is bundled with SQL Server you get great value.

Funny thing, working with Excel requires hacks and work arounds. Really wish Microsoft would fix this.
More transformations to reduce the need of 3rd party tools. Tasks like SFTP would be nice.

Return on Investment

We partner with other companies to help provide value to their customers. We had a number of them that requested reporting sent to their customers monthly. Our traditional Devs spec'd they would need up to a week toget that coded, tested and released. With SQL Server Integration Services and SQL Server Reporting Services we had the first iteration out in a day. This sends out over 3,000 reports, using our Partners Branding we get these out in less than 1 hour saving development resources.
In a growing company from Startup to Enterprise, integrating our home grown order entry into our ERP system was proving difficult by hand. Once I implemented SQL Server Integration Services we had it done in less than a month. This reduced our project implementation time by over 1 month which saved consulting time and expenses.
We use SQL Server Integration Services to backup our production boxes, cleanse and change the data to comply with different countries regulatory requirements, and restore that data to our Development and Test environments allowing our developers to have the most recent data set possible.

Alternatives Considered

SAP Data Services, Informatica Enterprise Data Integration and Syncsort Connect ETL (formerly DMX)

These are all great products and, honestly, can move data faster. They include more enterprise features and have some great qualities about each. However, they all cost a lot depending on the implementation you need. With SQL Server Integration Services, you do not have any added cost and, for most cases, it is more than enough of a tool. If you are a shop that has specific needs and budget other tools will certainly be worth a look but, I would always try with SSIS first. It really is a great tool.

Other Software Used

Microsoft SQL Server

Performance

Raw performance is great. At times, depending on the machine you are using for development, the IDE can have issues. Deploying projects is very easy and the tool set they give you to monitor jobs out of the box is decent. If you do very much with it you will have to write into your projects performance tracking though.

Usability

If you use any of the Microsoft tools, it is easy to get using and understand since it uses Visual Studio and SSMS to work with. There are extensions you can get and some of those can be difficult to work with but usually they slide in nicely. The one thing to understand though is there is some installation and configuration on the SQL Server side that can take some time.

Likelihood to Recommend

If you are a SQL Server shop, it is almost not even a question. The tool slides in nicely and is fairly easy to get going and implement. I have used it for Data Warehousing, Report Automation, DBA Task Automation, System Integrations and even Web Testing. It is extremely flexible and anyone working heavily with SQL Server should really look at using it.

SQL Server Integration Services, worth a look

Overall Satisfaction with SQL Server Integration Services