SSIS - Our go to ETL Tool
June 04, 2016
SSIS - Our go to ETL Tool
Score 9 out of 10
Vetted Review
Verified User
Overall Satisfaction with SQL Server Integration Services
SQL Server Integration Services is the go to tool for our Data Integration team for automating a number of disparate data imports. We deal with a number of file sources (Excel, CSV, TXT, etc) that need data transformed for import into our SQL databases. SSIS allows us to handle any number of file formats and types efficiently, and we can perform all data validations necessary to ensure utmost data integrity.
- Handling upsert functionality (i.e. performing lookups against existing data to determine new versus existing records).
- Automation of scheduled file imports. Essentially any consistent data source can be automated after a one-time SSIS package is set up.
- Ability to handle a wide variety of data sources.
- Excel data type handling - makes assumptions on a single data type where there might be multiple data types in a specific column.
- Better handling of data schema changes - If you have column changes or additions in your data source, it does not always implicitly make those changes across all data transformation tasks within the package.
- No reliable built-in functionality for SFTP connections.
- SSIS has been a huge time savings where previously manual data scrubbing and/or import tasks have been automated.
- It has allowed us to schedule jobs that previously required human involvement. Now everything runs seamlessly through SQL jobs.
- It provides us with a wider customer base as we are pigeon-holed into working with a small number of data source options. We can work with an infinite amount of data sources due to our utilization of SSIS.
- Clover ETL
While Clover provides a number of the same data transformation techniques, it is not nearly as robust as SSIS. It also requires knowledge of the specific scripting language while SSIS allows the user to choose from a number of commonly-used scripting languages (i.e. VB, C#, etc.). If you are using SQL Server, integrating with SSIS is a no-brainer.