Use it if you have a Microsoft Stack
August 05, 2016
Use it if you have a Microsoft Stack
Score 8 out of 10
Vetted Review
Verified User
Overall Satisfaction with SQL Server Integration Services
I cannot say for the whole organization, but we use SSIS for just about all our automation processes. When managing a large data warehouse it is incredibly useful to automate the ETL process. We primarily use it for the data warehouse, but it's versatile enough to use for other automation tasks, reports, and notifications.
- Clear GUI and ETL workflow. It's very easy to understand how the data is being managed. When pulling up a SSIS solution that someone else has created, it's very easy to see what's going on-- how the data is extracted, how it is transformed, and how it's being loaded.
- Deploying scripts. Once a proper package store is configured, you just need to hit deploy and it handles the rest. It's also flexible enough that you can still use SSIS packages without using an SSIS DB for version control by calling them through the file system. Or if you're one of those people who love batch scripts, you can also execute the packages through command line.
- SSIS Package Store. It's a great way to manage your versions and deployments. Bonus is that if you use a package store, it'll also give you error reports after the fact if a package fails for debugging. It'll tell you exactly what step failed and why.
- I think it handles undefined/dynamic data sources poorly. Considering that we use it primarily to ETL data from other systems across the whole organization to bring into our BU's data warehouse, we sometimes have issues when the source has changed. If someone adds a column without letting us know, we'll need to modify the SSIS packages.
- Sometimes the error codes are vague or cryptic. When debugging a SSIS package I have to google the code or error message and hope someone has a similar issue on stack overflow.
- SSIS really only works if you're already using a lot of Microsoft Products like Microsoft SQL Server or SQL Server Reporting Services. As mentioned in the name of the application, "integration services", it's designed to integrate your products together so that you can get the most out of it.
- Using SSIS has allowed us to free up a lot of time from our business users. We do a lot of predictive analytics here and we generate models/reports for various business units outside of our own. This involves ETL, running stored procedures, executing R scripts, and running other analytical tools over the data and generating data. By automating a lot of these tasks, these users are free to do more research and explore other opportunities for revenue/profit.
- SSIS is a pretty popular tool and it is not difficult to hire users that have experience. We don't need to spend a lot of time training new associates no how to use the product which saves us a lot of resources.
- Overall, we're able to achieve our business goals because it does what it's intended to do. We're able to integrate our tools and applications together to create new products and revenue streams.
- informatica and Talend
I've used several other ETL tools and they can all do the trick once you learn how to get around their shortfalls, including SSIS. I think the best reason to use SSIS is if you're already using Microsoft tools. They integrate well with each other and it's easy to understand how they interact with each other.