SSIS - Our go to ETL Tool
June 04, 2016

SSIS - Our go to ETL Tool

Anonymous | TrustRadius Reviewer
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.

Pros

  • 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.

Cons

  • 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.
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.
SQL Server Integration Services is particularly well suited for automating oft-used data transformation processes. Any transformation tasks that are performed manually on a consistent data source should be able to be automated using SSIS. It's also very well suited for DB to DB data migration, whether it be one-time ad-hoc requests or common scheduled tasks (i.e. migrating from test to production databases).

SSIS Feature Ratings

7.5
Connect to traditional data sources
9
Connecto to Big Data and NoSQL
6
9.0
Simple transformations
10
Complex transformations
8
7.8
Data model creation
7
Metadata management
8
Business rules and workflow
8
Collaboration
7
Testing and debugging
9
8.0
Integration with data quality tools
8
Integration with MDM tools
Not Rated

Comments

More Reviews of SSIS

  1. Home
  2. Data Integration Tools
  3. SSIS Reviews
  4. User Review of SSIS