Item: SQL Server Integration Services
Rating: 10
Author: Verified User

Use Cases and Deployment Scope

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 and Cons

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.

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.

Return on Investment

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.

Alternatives Considered

Workato, Matillion, Microsoft SQL Server and Centerprise Data Integrator

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.

Other Software Used

Microsoft Dynamics 365 (formerly Microsoft Dynamics CRM), Workato, ClickDimensions, Microsoft Power BI, Microsoft Project, OneNote

Performance

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.

Usability

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.

Likelihood to Recommend

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.

Users and Roles

3 - Developer - deploys to server; maintains server instances
Business Analyst - develops, maintains and monitors SSIS packages
Co-Op - develops, maintains and monitors SSIS packages

Support Headcount Required

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.

Business Processes Supported

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.

Innovative Uses

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

Future Planned Uses

Data archival / purging
Data cleansing

Likelihood to Renew

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.

Usability Pros and Cons

Pros	Cons
Like to use Relatively simple Easy to use Technical support not required Well integrated Consistent Quick to learn Convenient Feel confident using	None

Easy Tasks

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

Difficult Tasks

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

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

Overall Satisfaction with SQL Server Integration Services