SQL Server Integration Services: a great tool for small to medium sized implementations
January 15, 2019

SQL Server Integration Services: a great tool for small to medium sized implementations

Anonymous | TrustRadius Reviewer
Score 6 out of 10
Vetted Review
Verified User

Overall Satisfaction with SQL Server Integration Services

SSIS is used within my organization to move data from one data source to another, performing data translations, transformations, lookups and calculations during the data movement. This process often includes very complex data transformation processes including the use of APIs, external references and various class libraries. SSIS is currently used in various areas across the entire organization to solve SQL server-based data transformation issues.
  • It handles SQL Server databases flawlessly
  • It provides a robust developer interface
  • It allows a developer to encapsulate complex scripts directly within an SSIS project or reuse scripts across projects
  • It interfaces quite well with a large number of available libraries
  • SSIS memory usage can be quite high particularly when SSI and SQL server are on the same machine
  • SSIS is not available on any environment other than Microsoft Windows
  • SSIS does not function with any database engine back-end other than Microsoft SQL Server
  • SQL Server Integration Services has had a positive impact on our organization as it has allowed us to integrate data from external data sources via CSV and flat files
  • SQL Server Integration Services has a waning positive impact on our organization as we are moving more toward Big Data and Oracle data
  • Given the initial investment in SQL Server Integration Services, the ROI has been positive as it has enabled business-critical ETL for the past 5 years
SQL Server Integration Services does a good job for our SQL Server environments and was selected for that reason. For a SQL Server-only implementations, I would recommend SQL Server Integration Services. When we compared SSIS to other ETL providers against SQL Server, SSIS was the logical choice. It is well integrated not only with the database but with Visual Studio and the entire Microsoft development suite.
SQL Server Integration Services performance is dependent directly upon the resources provided to the system. In our environment, we allocated 6 nodes of 4 CPUs, 64GB each, running in parallel. Unfortunately, we had to ramp-up to such a robust environment to get the performance to where we needed it. Most of the reports are completed in a reasonable timeframe. However, in the case of slow running reports, it is often difficult if not impossible to cancel the report without killing the report instance or stopping the service.
SQL Server Integration Services is a relatively nice tool but is simply not the ETL for a global, large-scale organization. With developing requirements such as NoSQL data, cloud-based tools, and extraordinarily large databases, SSIS is no longer our tool of choice.
Microsoft SQL Server Integration Services is suited for development by those who are NOT very experienced developers. End-users with some database experience may find the development environment easy to use allowing development of basic ETL. Experienced developers will likely feel restricted by the "Microsoft-only" interface. Additionally, many larger organizations that have made a significant investment in databases other than SQL Servers will be unable to use SSIS against those database servers.

SSIS Feature Ratings

Connect to traditional data sources
9
Connecto to Big Data and NoSQL
3
Simple transformations
10
Complex transformations
6
Data model creation
9
Metadata management
8
Business rules and workflow
9
Collaboration
1
Testing and debugging
4
Integration with MDM tools
5