TrustRadius: an HG Insights company

SSIS

Score7.6 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.9

  • Simple transformations

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

    Category average: 8.9

  • Data model creation

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

    Category average: 8.4

Areas for Improvement

  • 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.6

  • Complex transformations

    Complex data transformations are data normalization, advanced data parsing, etc.

    Category average: 7.4

  • Integration with MDM tools

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

    Category average: 7.2

Reviews

What users are saying about SSIS.
View all reviews

SQL Server Integration Services

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

Use Cases and Deployment Scope

In my organization, I use SQL Server Integration Services to automate data flow into different systems used by different departments. I create reports of our daily/weekly data for the upper management to review. Those reports are made with the help of SQL Server Integration Services. I fetch all the data for the report using SQL Server Integration Services. The reports are generated automatically now, I just have to send it to the upper management.

Pros

  • SQL Server Integration Services helps in fetching the data from our cloud Genesys application to create/generate reports. The reports are on point with accurate data. It is so efficient and time saving for me.
  • We use SQL Server Integration Services to keep the employee data records updated. It automatically enters all the employee data such as recent pay slips, recent performance reviews everything to the appropriate entry. Manual work has been very less since we started using SQL Server Integration Services.
  • We also collect IT data using SQL Server Integration Services. It gets us detailed data of our systems which would so if there are any technical issues/glitches in the system. We can easily use that to resolve the issue rather than wasting time on manually searching where the issues started and what is causing the technical issue.

Cons

  • I think the response time of real time data is very slow. It is difficult to use SQL Server Integration Services in a fast faced environment where we have to work on lot of real time data.
  • SQL Server Integration Services issues are very hard to resolve. If there is a technical issue, the errors are not worded properly, It doesn't give a straight forward issue. They are very generic and it takes a lot of time to find what the error is and to fix it.

Return on Investment

  • Time efficiency is the biggest positive impact, I think. It helps with large complex data. It saves a lot of time. Generating reports are super easy with it.
  • Data efficiency - The data is also accurate. There are hardly any errors in the data.
  • Negative impact - Doesn't work well with real time data.
  • Its technical issues are very complex, it takes lot of time to find the problem, fix it and making sure that the entire system is working well.

Usability

Alternatives Considered

Azure Data Factory

Other Software Used

Jira Service Management, Freshservice, ConnectWise Cloud Backup, SAP SuccessFactors HCM, Workday Workforce Management, Greenhouse, iCIMS Talent Cloud, SmartRecruiters, Visier People

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

Easy to implement needs improvement

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

Use Cases and Deployment Scope

We use this to run several ETLs to update the data warehouse on a daily basis.

Pros

  • Easy interface to create data connections and data transfer object
  • functionality to use multiple programming languages when an existing object is not sufficient
  • functionality to call api
  • easy to understand

Cons

  • Reports that show the ETL running, also used for finding failures need drastic improvement
  • The report should be able to show what objects are currently running and how they are performing. They will be helpful to troubleshoot when the ETLs are running slow.
  • The reports currently only show tasks that have completed, not the ones running now. Add the running now tasks and their performance will greatly enhance the product.

Return on Investment

  • It is one of the cheapest data integration tools in the market and working on great business solutions. If enhanced, it can be even better.

Usability

Alternatives Considered

Informatica PowerCenter (legacy) and CloverDX

Other Software Used

Google BigQuery, CloverDX, Microsoft SQL Server, SQL Server Management Studio (SSMS), Databricks Data Intelligence Platform

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.