TrustRadius Insights for SQL Server Integration Services (SSIS) 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.
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.
Likelihood to Recommend
If you already have licenses for SQL Server and need to get data from A to B, especially if A and/or B is SQL Server, then SSIS could be a reasonable choice, especially if you can't use a cloud tool like Fivetran due to security concerns.
In our organization, we have two Microsoft windows servers. We integrate their data sync via SQL Server Integration Services. It's easy for us to migrate our data from one server to another server. The debugging capabilities are great, particularly during data flow execution. We can look into the data and see what's going on in the pipeline.
Pros
Encrypt files with SSIS and send them to various network locations this way we solve complex business problems.
We can migrate DTS packages to SSIS while choosing to run DTS packages using DTS runtime or incorporate DTS packages into SSIS this way we migrate DTS packages to SQL Server Integration Services.
We can transform data to make sure it complies with the rules of the database they are migrating to other servers with Integrations Services.
Cons
User-required automation needs much more scope.
Exporting numerous tables in CSV format has to be done one by one by manual.
Likelihood to Recommend
Helpful in connecting to various data sources and loading the combined data. More cost-efficient than other ETL tools of the same stature. Reduces the need for coding scripts.
It is mainly used by technical analysts with the purpose of delivering data integration and reporting projects to customers. It addresses the need for complex multi-source data ingestion, data manipulation, and integration to a MSSQL database.
Pros
Visual programming makes configuration easy and accessible
The ability to code also allows users to implement complex logic for data manipulation and etc.
Easy integration with MSSQL Database instances.
Cons
Component properties are not very well defined, which makes the learning curve harder
As control flow and data flow often looking similar visually at first glace, it takes awhile to differentiate which one you are working on as users need to look at the tiny symbol and text on the tab to do so. A more straightforward color-coded or larger visual cue to differentiate between the two would make this easier.
Likelihood to Recommend
SSIS is suitable for MSSQL related projects/works due to how well it integrates and performs data manipulation. It is suitable for moderate data input ingestion rates but not suitable for projects where a high volume of data is required to be ingested and processed rapidly.
VU
Verified User
Analyst in Engineering (Information Services company, 11-50 employees)
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.
