Data integration for everyone. A powerful tool that anyone can master.
July 18, 2019

Data integration for everyone. A powerful tool that anyone can master.

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

Overall Satisfaction with SQL Server Integration Services

We are currently using SSIS packages on two major projects:
1. To populate our data warehouse. Running SSIS packages from the server on a 10-minute schedule, we extract, transform and load the data into the warehouse to support all internal reporting and provide data as a service to our global partners.
2. Bulk data import to our CRM system. Building packages to run on-demand to bulk import structured data to our CRM instance.

In the past, we have used SSIS packages to complete a one-time migration from a legacy CRM system to the current CRM.

Pros

  • Ease of use - can be used with no prior experience in a relatively short amount of time.
  • Flexibility - provides multiple means of accomplishing tasks to be able to support virtually any scenario.
  • Performance - performs well with default configurations but allows the user to choose a multitude of options that can enhance performance.
  • Resilient - supports the configuration of error handling to prevent and identify breakages.
  • Complete suite of configurable tools.

Cons

  • Connection managers for online data sources can be tricky to configure.
  • Performance tuning is an art form and trialing different data flow task options can be cumbersome. SSIS can do a better job of providing performance data including historical for monitoring.
  • Mapping destination using OLE DB command is difficult as destination columns are unnamed.
  • Excel or flat file connections are limited by version and type.
  • Enabled migration of complex business/customer data with one person resource.
  • Launched automated ETL package within weeks with less than 2 person resource.
  • No production failures/downtime since launch.
When looking to evaluate different options, we looked first to the experience and software we had in-house that would accomplish the job. When assessing alternatives outside we were looking for the tool that would offer the most flexibility.

SSIS provided the most robust set of features with the smallest learning curve out of its major competitors and was the lowest cost to implement.
I haven't experienced any issues worth mentioning in terms of SSIS performance. But it is worth mentioning that there is a HUGE difference between tools and with different configurations within the app. I've seen a single data flow task go from over 30 minutes to less than one with the right configuration.

There should be more tools to enable process tuning to improve package performance.... although the software itself runs smoothly with very few interruptions.
While there is a learning curve, it is possible to successfully implement SSIS in anyone's business with little or no prior experience. The tool's native usability along with a wealth of online resources in a large community means that the user will never be stuck for long.

  • SSIS is particularly well suited for jobs that need to be consistent, repeatable, and error managed.
  • Ongoing extract, transform, load [ETL] jobs that are scheduled or manual.
  • One-time ETL with complex datasets.
  • Migrations of large datasets.

SSIS is not well suited for small or simple datasets that can be copied or exported safely to flat files for import. It is possible to do this but would generally take longer to build in SSIS unless there was a good reason to .remove manual handling of the data in transport or the action needed to be testable/repeatable.

SSIS Feature Ratings

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

Using SQL Server Integration Services

3 - Developer - deploys to server; maintains server instances
Business Analyst - develops, maintains and monitors SSIS packages
Co-Op - develops, maintains and monitors SSIS packages
3 - We have a total of three staff internally that manage all of the data integrations required for migrations, imports, ETL, data warehouse/data store maintenance.
  • ETL operations to maintain the data warehouse with near real time data.
  • Data migrations bringing all parts of the organization under one hood.
  • Complex bulk data import from global network of distributors.
  • In-place, complex data transformations - one time events that require the use of business logic to transform operational data .
  • Minimal use of development resources to maintain business intelligence infrastructure
  • Data archival / purging
  • Data cleansing
SSIS is responsible for running core business processed managing core business data. It can be managed, improved and expanded using minimal internal resources. It is also able to support all of our current data infrastructure.

Replacing SSIS would be time consuming and costly with no apparent ROI.

Using SQL Server Integration Services

ProsCons
Like to use
Relatively simple
Easy to use
Technical support not required
Well integrated
Consistent
Quick to learn
Convenient
Feel confident using
None
  • Control Flow and Data Flow views/screens/canvases are beautifully setup so that you can see top level and drill down to specifics.
  • Error and warning icons on control flow and data flow tasks along with clear icons show you where you still need to update/fix the process.
  • Tasks and connection validation on startup / update makes sure you are always aware of breaks/changes
  • Trying to improve performance / runtime can be painful because you have to keep track of what changes you made and the impact to performance. It's easy to get lost when making multiple changes what their impact on runtime is.
  • Requires visual studio

Comments

More Reviews of SSIS

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