TrustRadius Insights for SQL Server Integration Services are summaries of user sentiment data from TrustRadius reviews and, when necessary, third party data sources.
Pros
Efficient Data Synchronization: Users have consistently praised SSIS for its ability to efficiently manage small-scale data synchronization tasks, enhancing productivity in data workflows. The tool's streamlined approach to syncing data has been noted as a time-saving benefit by many reviewers.
Rapid Data Transfer to SQL Server: Several reviewers have highlighted the tool's swift data transfer capabilities to SQL Server, particularly emphasizing its efficient handling of batch processes. This quick data movement feature not only accelerates tasks but also contributes to smoother operations within databases.
Real-Time Message-Based Functionality: Users highly value SSIS for its real-time message-based features and seamless integration with SQL Server and Visual Studio, facilitating agile data processing. The system's agility in handling real-time messages has been commended for improving responsiveness and connectivity across platforms.
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).
Likelihood to Recommend
Ideal for daily standard ETL use cases whether the data is sourced from / transferred to the native connectors (like SQL Server) or FTP. Best if the company uses MS suite of tools. There are better options in the market for chaining tasks where you want a custom flow of executions depending on the outcome of each process or if you want advanced functionality like API connections, etc.
The IT department at our organization uses SQL Server Integration Services. We use SSIS to perform extract, transform, and load (ETL) data operations. Our primary use is to move data from a source system or database, restructure the data to optimize it for reporting, and store it in a database instance used for reporting.
Pros
Handles multi-step, complex data moves.
Pulls from a variety of data sources.
Add-ons are readily available to extend their usefulness.
Integration with SQL Server and data tools.
Cons
The package publishing feature has gotten better over the years, but it could still be simplified.
Incorporating features from add-ons into the standard application would be helpful (mostly in relation to data sources).
Easier configurations for multiple publishing targets (dev/test/prod) with associated data connections.
Likelihood to Recommend
SSIS is well-suited for scheduled data loads, such as scraping web pages for currency rates or storm-related delivery delays and writing the results to an application database or copying transactional data from a source system, optimizing it for reporting, and writing to a reporting server. SSIS is also great in helping to combine data from disparate sources to build a deeper data analysis platform.
VU
Verified User
Supervisor in Information Technology (Chemicals company, 51-200 employees)
Even though we are a smaller company, we use this huge army Swiss knife to accomplish a number of technical IT tasks. First and foremost we use it to integrate systems like MS CRM and SAP, constantly enhancing the data flow and making tasks easier to accomplish for the users. Secondly, I personally use it clean up data and maintaining my BI ETL scripts running out 12-year-old data warehouse for our analysis cube. The actual tool is currently only in use by the IT department by using Visual Studio (BIDS).
Pros
Almost no programming is needed, like drawing simple flow diagrams.
If you want to be more advanced you can add some VB or C# programming if needed.
Microsoft tool using all the great connectors, using any data source
Easy to add a third part like CozyRoc
Cons
File handling
Integration with office tools could always be improved, MS, as usual, provides these 80% solutions to make room for third vendors.
Error message or event handling, better messages and link to processes.
Likelihood to Recommend
Any integration tasks in a Windows environment like AD or application integration, DB integration, etc. The drag and drop workflow and easy language, for example, derived data changes are so easy to make that it reminds of Excel functions. Full flexibility to draw up any workflow and easy troubleshooting using the data viewer.
VU
Verified User
Technician in Information Technology (Medical Devices company, 201-500 employees)
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.
Pros
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
Cons
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
Likelihood to Recommend
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.
VU
Verified User
Engineer in Information Technology (Automotive company, 10,001+ employees)
Like most businesses, we have various sources of data that management likes to be able to compare to each other. I use SSIS primarily to move data between our source systems and data marts and warehouses that our reporting software can be pointed at. I also use SSIS to deliver scheduled file exports to external customers or to import files into one of our critical systems for use. I even tend to use it for non-SQL related things such as file system and ftp tasks. If it needs to be extracted, transformed, or loaded somewhere, I use SSIS to do it.
Pros
There are many good workflow tools and ways to control the order in which things happen. In a short amount of time, you can quickly create a package that will move data from point A to point B and have it scheduled to run 4 times a day. Or if you need error handling or other business logic, you can spend more time and completely automate repetitive tasks. Robust? Check!
SSIS can consume multiple sources of data. From flat files, to Excel, to Oracle, or DB2...I've been able to access multiple data types and move them in and out of SQL databases with SSIS. We had one linux system that ran a Basis database system and there was a need to have something done, but no one could figure out how to make it work. I was able to use SSIS to import files and execute code on a server that had nothing to do with SQL server. So flexible? Check!
We already use SQL server almost exclusively for our enterprise database needs. The fact that we already have access to this tool at no additional cost to the business is a bonus. The fact that it is powerful, even better. Value? Check!
Cons
I know in my "pros" comments, I said it was nice because we already had access to SSIS by virtue of being able to install it on existing SQL servers with no additional license cost. But, if you rely heavily on SSIS, you will want to have it on its own server rather than letting it share resources with a very active SQL server. That means additional licenses. It can consume a lot of resources, depending on the amount of data you're pushing through SSIS at any given time.
Current versions of SSIS do a much better job of managing deployment of packages into production. It used to be an all-or-nothing proposition so if you had to make a small change to a project that had many packages in it, you'd have to redeploy the entire project which means lots of extra testing. The introduction of package level deployment was welcome.
SQL server and SSIS play very well together when they have enough resources. If you're using virtual servers and can add CPU/RAM/Space easily, then by all means, put them together and manage the resources so they stay out of each other's way. If you don't have the capability to do that, then you'd be better off having SSIS on a separate server. When everything is working well, it is amazing. But if you make SSIS and SQL fight over resources, it's not pretty (SQL wins that fight by the way in case you were wondering!)
If I'm being honest, I haven't had to point SSIS to a huge variety of source systems. It could be that SSIS doesn't play well with certain DBMS' (I've heard Sybase compatibility complaints before) and you'll need to do some research and testing before actually using it in production.
Likelihood to Recommend
If you need to move data around or direct the workflow of a process, SSIS can do it. It is a very capable piece of software that I use heavily every day. You do need to be careful because you can over-utilize it for simple things. If you just need to run a piece of SQL every hour to update some values, just use the Agent Scheduler, it's easier. But if you need to automate things in a repeatable and consistent manner, SSIS is a very good product.
In the beginning, we had hundreds of Stored Procedures, instead of SSIS packages. The Stored Procedures were poorly made by some users, only thinking on the resulting query and not the execution performance, plus the people doing data mining created tables for a report and then they didn't eliminate such tables that only had one use, also some of those tables kept growing without being needed any longer.
The implementation and onboarding of SSIS was made with the intention to correct some of these T-SQL coding issues. It is easier to understand a diagram than sheets of T-SQL code with good documentation. Besides the performance for bulk inserts was better with SSIS than normal inserts in stored procedures. We were able to divide and define a bit better the roles, between SQL developers, Data miners, and BI engineers.
Pros
Logging, this is essential when you do ETL. With SSIS you can run the package and see step by step the progress, how many tuples complied with the filters, like how many went left and how many were correct, or excluded.
Using regular expressions with C# direct code by adding Script Components it's easier with SSIS
Performance, it is difficult to demand good SQL code to every member of the BI team not everyone is specialized in T-SQL.
SSIS standardizes a bit more the code and allows users not completely familiar with SQL or even C# to achieve what they needed, the package still needs to go through a code review but it is quite easier to understand.
Cons
Be careful when you edit a package, if the version is above the SSMS you are using then it will not be compatible. You have to compile or edit the SSIS package in the same version of SSMS you are using.
To explain it a bit better if you have SQL 2014 in your laptop, pull a package for the DB server which is running SQL 2012, after you edit the package it will not be allowed in the SQL server.
Python, Perl scripts are still a high competition for SSIS, mostly because they are very easy to manipulate, if you need a change you can do it directly with notepad.
Plus Python now has an add-on called Pandas which is great for manipulating data.
Likelihood to Recommend
Extracting, transforming and loading data from multiple sources with different formatting is not that easy. SSIS provides different ways to connect or import from html, json, comma separated, xml, or other databases, which makes it a very diverse tool.
The only main competition I have noticed is the combo of Python, Pandas, and Jupyter; but for that other solution, you will need an experienced team in scripting. So at the end is choose what your team feels more comfortable.
We currently use SSIS for imports of purchase orders into our ERP - SYSPRO. These purchase orders come in various file formats: EDI, XML and excel spreadsheets. SSIS helps us aggregate these various files into a common import platform and apply business logic such as ship date calculations, SKU availability checks, customer hold checks etc.
Pros
Great for parsing data from various file formats into SQL server. As an example, we use it to extract data from XML, EDI and other flat files.
Great for applying custom business logic in the ETL process. These business logic could be built into functions, stored procedures and applied through the SSIS packages.
I like it's exception handling capabilities and how it's able to show the module that threw up the exception by highlighting it in red.
Works very well with Visual Studio and as a matter of fact, you can build all your SSIS packages right from SQL without even opening up SQL server or BIDS.
Cons
Not sure if it has JSON support but if it does, that would be awesome! Basically, the ability to consume data from a JSON data set.
In as much as Microsoft built it for the SQL database, it would be awesome if we could leverage SSIS for data ETL into other databases like MySQL and Oracle etc.
Add more color themes! The default color theme is old school and really sucks if you ask me.
Likelihood to Recommend
1. Great for ETL (Extract-Transform-Load) data operations. 2. Amazing if your primary database environment is SQL server. 3. Works great with Visual Studio and Microsoft even has it now on the Azure platform. 4. Works great with various file formats - XML, EDI, spreadsheets, flat files etc. 5. Works great in scenarios where it is necessary to apply business logic through stored procedures etc.
VU
Verified User
Professional in Information Technology (Sporting Goods company, 201-500 employees)
