TrustRadius: an HG Insights company

SSIS

Score6.5 out of 10

259 Reviews and Ratings

Get a Demo

What is SSIS?

Microsoft's SQL Server Integration Services (SSIS) is a data integration solution.

Categories & Use Cases

Key Features

Learn about the best (and worst!) features SSIS has to offer, as determined by TrustRadius' reviewers.
Based on 259 ratings of SSIS's features
View all features

Top Performing Features

  • Connect to traditional data sources

    Ability to connect to traditional data sources like relational databases, flat files, XML files and packaged applications

    Category average: 8.8

  • Data model creation

    Ability to create and maintain data models using a graphical tool to define relationships between data

    Category average: 8.4

  • Simple transformations

    Simple data transformations are calculations, data type conversions, aggregations and search and replace operations

    Category average: 8.1

Areas for Improvement

  • Integration with MDM tools

    Integration with master data management tools to ensure data consistency across the organization

    Category average: 7.8

  • Connecto to Big Data and NoSQL

    Ability to connect to non-traditional data sources like Hadoop and other big data technologies, and NoSQL databases

    Category average: 7.8

  • Testing and debugging

    Tool to debug and tune for optimal performance

    Category average: 7

Most Frequent Users

Top 3 industries using SQL Server Integration Services
Based on HG Insights installation data
View all Reviews
#1 most frequent

Professional, Scientific, and Technical Services

20.2%

2,760 installations of 13,645

#2 most frequent

Manufacturing

9.5%

1,294 installations of 13,645

#3 most frequent

Health Care and Social Assistance

9.2%

1,256 installations of 13,645

Reviews

What users are saying about SSIS.
View all reviews

SQL Server Integration Services - still kicking after all these years

Incentivized
Verified User
Professional in Information Technology (501-1000 employees employees)

Use Cases and Deployment Scope

Data Engineering and integration. Move data between different on-premise servers no matter the platform. Since we have an Enterprise Agreement with Microsoft, it does not cost any extra to use SQL Server Integration Services unlike Azure Data Factory or Informatica. Wherever data transfer is involved, it is the de-facto standard in our company.

Pros

  • Easily connects to source and destination systems
  • GUI interface - so minimal learning curve
  • Script task feature enables extensibility and access to .NET framework

Cons

  • Zero enhancements by Microsoft in a very long time
  • Has fewer connectors compared to Power BI or ADF
  • Not ideal for handling huge datasets

Return on Investment

  • Saved tons of money over the years by not having to go out and buy another licensed product. Since we have been using it for 15 years, that would be approximately 15 times $25K
  • Saved a lot of development hours due to the minimal learning curve, even our interns are able to use it thereby freeing time for higher skilled employees to work on more complex problems
  • A big user community helps speed up trouble-shooting and support

Usability

Alternatives Considered

Azure Data Factory

Other Software Used

Microsoft Power BI, SQL Server Management Studio (SSMS), Microsoft SQL Server

Good for startups or companies with limited data variability, Big data companies will need better tools

Incentivized
Vishal ShahView profile
Data Engineer, Product Analytics in Product Management at Carvana (5001-10,000 employees employees)

Use Cases and Deployment Scope

SQL Server is the legacy Database and Data Warehouse in the company. SSIS is used for most ETLs where the data is sourced from / put into SQL Server, depending on the use case. It is an MS native and easy-to-use ETL tool that comes with the SQL Server Data Tools suite. I use the tool personally to deploy ETLs for business stakeholders as a request arises.

Pros

  • Standard ETL use cases for daily loads
  • Loading incoming data from Vendors which is placed on FTP and adding them to the SQL Warehouse
  • Creating outgoing data files and writing them to Vendor FTPs
  • Easy Active Directory integration for seamless connections to SQL Server
  • CI/CD by hosting the code on visualstudio.com

Cons

  • API connections are not a native functionality. We use Zappysys extensions but they work only in certain cases.
  • Dependency executions - no simple way to create a hierarchy/chain of executions. Ex: Define if the execution of the child process should be stopped if a parent fails or a certain condition is triggered in the parent process and then redirect to a different part of the chain (think like a flowchart for executions).

Return on Investment

  • Good ROI for companies using the MS suite of tools (SQL Server / SSDT) without having to purchase additional tools.
  • SQL Server is expensive, so running very complex processes using SSIS will become quickly very expensive.

Alternatives Considered

Alteryx and Informatica PowerCenter

Other Software Used

Databricks Lakehouse Platform (Unified Analytics Platform), Snowflake, IntelliJ IDEA, Alteryx, Microsoft SQL Server

SSIS, free but not intuitive

Incentivized
Sean Ford
Database Engineer in Information Technology at Shelter Insurance (1001-5000 employees employees)

Use Cases and Deployment Scope

SQL Server Integration Services is an extremely powerful tool that we're using exclusively for SQL Server inventory and other jobs. While it is very powerful, it's also not exactly intuitive in my opinion. I had not used this before my current job, and it was already setup when I joined. I've had to edit some of those jobs a handful of times and I just have not yet been able to make it 'click' in how it works. I seem to think oppositely of how this was designed (potentially just the jobs I've had to interact with), but almost everything is counter-intuitive.

Pros

  • Runs flawlessly after setup properly
  • Powerful, as mentioned
  • Free

Cons

  • Confusing operation, even with its gui
  • Requires special install and setup
  • Requires the Visual studio integration services install (separate interface from SSMS)
  • Could be more easily integrated into SSMS, but may also be used for other products that I'm not aware of

Return on Investment

  • Without this, we would have to manually update a spreadsheet of our SQL Server inventory
  • We would also have poor alerting; if an instance was down we wouldn't know until it was reported by a user
  • We only have one other person who uses SQL Server Integration Services , he's the expert. It would fall to me without him and I would not enjoy being responsible for it.

SSIS had its day, but in the big leagues it can no longer play

Incentivized
Alex BranskyView profile
Principal Data Engineer in Engineering at Ad Hoc LLC (201-500 employees employees)

Use Cases and Deployment Scope

SSIS pulls data from various locations (mostly SQL Server instances but also some flat files and SFTP), mainly for transforming the data and putting it in the data warehouse.

Pros

  • SSIS is good for small, simple data syncs.
  • SSIS is good for moving data quickly into SQL Server (good batch performance).

Cons

  • Very hard to diff for code review when changes are made.
  • Very buggy IDE (freezes in Visual Studio, doesn't always show what's in the underlying XML properly)
  • Lacking lots of features or more modern tools, like ability to monitor for changes to the source schema and apply them to the target.

Return on Investment

  • SSIS provided good value when there were few competitors, for somebody already using SQL Server Enterprise Edition.

Alternatives Considered

Fivetran, Stitch, from Talend and Etleap

Other Software Used

Snowflake, Fivetran, MuleSoft Anypoint Platform

Amazing data warehousing tool

Incentivized
Robbie ScottView profile
Security Software Engineer in Information Technology at Tata Consultancy Services (10,001+ employees employees)

Use Cases and Deployment Scope

We are currently using SQL Server as our central relational database. Getting data from multiple sources to our data depository is smooth. We use SQL Server Integration Services to copy and download large files as well as to draw out and modify data from several sources of data like XML data files, DB2 databases, and relational data sources and then load the data into a single destination.

Pros

  • I am able to use the GIS tools to easily generate solutions without coding.
  • Built in tasks that speed up web developing.
  • SSIS enables automated data preparations.
  • Amazing integration with Microsoft tools.
  • Creation of ETL mapping.

Cons

  • It depends so much on the Microsoft environment and has integration issues with other formats like JSON and Excel.
  • Error messages are unclear, this makes us spend a lot of time figuring out what has gone wrong making the debugging process hectic.

Return on Investment

  • We can easily merge data on a variety of databases.
  • Loading and migrating data is fast.
  • It offers a simple system for analyzing raw data.

Alternatives Considered

AWS Glue

Other Software Used

Azure Virtual WAN, Azure Bot Service (Microsoft Bot Framework)