SSIS is terrific for all your ETL needs.
May 26, 2016

SSIS is terrific for all your ETL needs.

Chris Morgan | TrustRadius Reviewer
Score 10 out of 10
Vetted Review
Verified User

Overall Satisfaction with SQL Server Integration Services

Our laboratory uses SSIS to transfer data between servers and databases, to data warehouses, reporting services and to and from file sources in a complex SQL Server environment that includes Merge Replication and SQL Server Reporting Services. Besides internal transfer, transformation, and manipulation of data, including archiving of data, we use SSIS to export laboratory results to external systems and import scheduling and sampling information from external systems.

Pros

  • SSIS can query, filter, and transfer data between databases on different servers without establishing explicit trust relationships between those servers.
  • SSIS can be used to refresh a reporting database from a transactional source database, transforming or flattening the data and tables as necessary to facilitate reporting. This can be done incrementally, or by emptying and refilling the reporting database from scratch.
  • SSIS is configured through graphical interfaces that make it relatively easy to see the flow of data including where problems occur.
  • SSIS has a number of tools that allow you to debug SSIS packages and track down problematic data or configurations.
  • SSIS allows you to program Script Tasks in C# and VB allowing extremely powerful functionality including looping and sending consolidated alerts.
  • SSIS allows you to control virtually every part of the SSIS package (connections, variables, etc.) using configuration files so you can have one package that can be used in several different places (such as dev, test, and production environments) only by editing the configuration file that the package uses when the job is scheduled.

Cons

  • One of the most frustrating things about SSIS, at least in its 2008R2 incarnation, is that of the annotations. Formatting annotations, getting them to wrap in the box, etc., is either extremely difficult or impossible without editing the XML of the SSIS package. You also CANNOT copy and paste annotations, so if you have several similar ones, you have to type every one of them from scratch. It makes you want to scream.
  • In the 2008R2 incarnation, setting up tasks to pivot data is very tedious and non-intuitive. I have heard that this improved in SSIS 2012, along with performance. We are trying to upgrade our SSIS and SQL server environment to 2012 or 2014 as soon as possible.
  • Sometimes datatype conversion doesn't work implicitly when it should and you have to do it either explicitly with a query or with a datatype conversion task.
  • SSIS has allowed us to meet a complex variety of ETL needs without having to acquire a profound knowledge of application programming and the learning curve is really not bad considering the power available. We heavily depend on SSIS every day.
  • SSIS or SQL Server doesn't play nice with Sybase databases, and it takes some work to make ETL go smoothly between Sybase and SQL server databases, particularly since SQL Server Management Studio can't query certain Sybase formats.
  • It is included with SQL Server, so it is a very powerful tool included at no additional cost. It has saved us a lot of money over possible alternatives.
If you are in a SQL Server environment, I really don't know why you wouldn't use SSIS since it is free with SQL Server and I don't know of any standalone tool that can match it. Redgate makes some great addon tools for SQL server that integrate with it to make it more powerful, versatile and easy to use.
Whenever you are moving data from one database to another, either on the same server or a different one, SSIS is a great tool. This is true for one-time transfers because it is so easy to set up and configure what you want to happen, and it is true for automated transfers that occur every day or every hour. If complex transformations of the data are necessary, including filtering, generation of new columns, merging datasets, whatever, SSIS is the best tool out there for ETL. If the task takes place within a database or between databases on the same server, and the task can be accomplished with a simple SQL script, it will often perform much faster than an SSIS package and require fewer server resources.

SSIS Feature Ratings

9.0
Connect to traditional data sources
10
Connecto to Big Data and NoSQL
8
10.0
Simple transformations
10
Complex transformations
10
8.4
Data model creation
8
Metadata management
9
Business rules and workflow
7
Collaboration
8
Testing and debugging
10
9.5
Integration with data quality tools
10
Integration with MDM tools
9

SQL Server Integration Services Support

The support, when necessary, is excellent. But beyond that, it is very rarely necessary because the user community is so large, vibrant and knowledgable, a simple Google query or forum question can answer almost everything you want to know. You can also get prewritten script tasks with a variety of functionality that saves a lot of time.
ProsCons
Quick Resolution
Good followup
Knowledgeable team
Problems get solved
Kept well informed
No escalation required
Support understands my problem
Support cares about my success
None

Comments

More Reviews of SSIS

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