Pros and Cons
- Process flow.
- Connection to a wide array of sources.
- Built-in upsert component.
- Better operability with source control systems.
Filter Ratings and Reviews
Showing 35 of 199 SSIS ratings and reviews.
Reviews (1-25 of 35)
Pros and Cons
- Data migration is pretty fast.
- SQL Server is highly compatible with the database.
- We receive errors in moving JSON data to the database. It does not work efficiently in JSON-related data.
- ODBC connections give a connection error after a certain period of time.
Pros and Cons
- Easy to manage projects and packages.
- Ideal for repetitive tasks.
- Can handle complex tasks consisting of multiple, diverse packages.
- Include SSIS in the default installation of SQL.
- The flexibility and different packages and options can make it confusing for first-time users.
- Recommendations in the selections could make it easier to build a solution.
October 12, 2019
SSIS enables you to build the data lake of your dreams!
Pros and Cons
- 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.
- 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.
July 30, 2019
SQL Server Integration Services, worth a look
Pros and Cons
- It sits inside of Visual Studio and SSMS so you have a consistent look and feel across toolsets
- Extensions. We use Pragmatic work transforms that seamlessly fit into SSIS to make certain tasks easier
- Performance. SSIS is not the fastest tool out there but it is more than enough for our needs and since it is bundled with SQL Server you get great value.
- Funny thing, working with Excel requires hacks and work arounds. Really wish Microsoft would fix this.
- More transformations to reduce the need of 3rd party tools. Tasks like SFTP would be nice.
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.
Pros and Cons
- SSIS works very well pulling well-defined data into SQL Server from a wide variety of data sources.
- It comes free with the SQL Server so it is hard not to consider using it providing you have a team who is trained and experienced using SSIS.
- When SSIS doesn't have exactly what you need you can use C# or VBA to extend its functionality.
- SSIS has been a bit neglected by Microsoft and new features are slow in coming.
- When importing data from flat files and Excel workbooks, changes in the data structure will cause the extracts to fail. Workarounds do exist but are not easily implemented. If your source data structure does not change or rarely changes, this negative is relatively insignificant.
- While add-on third-party SSIS tools exist, there are only a small number of vendors actively supporting SSIS and license fees for production server use can be significant especially in highly-scaled environments.
Pros and Cons
- 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
- 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.
June 07, 2019
SSIS - Strong performer in the traditional data space, but can be improved in handling JSON objects
Pros and Cons
- Very good for traditional RDBMS ETL and integration.
- Good developer community support online.
- Good at ingesting structured flat files (CSV, TXT, Excel).
- The tool struggles out of the box handling emerging datasets such as JSON feeds.
- Unstructured datasets can be challenging to work with.
- Some out of the box can be very resource heavy, and the UI is not very straight forward. Luckily there's a large community of SSIS users that can provide guidance.
December 07, 2018
SSIS for data integration and ETL!
Pros and Cons
- Source system connectivity (API's, SQL DB's, Olap Cubes, etc)
- UI which allows less technical people able to quickly and easily complete the tasks.
- Debug and quickly/easily troubleshoot logic and errors while running jobs or procedures.
- Version control sometimes seems to be an issue when many different sources are coming into play.
January 15, 2019
SQL Server Integration Services: a great tool for small to medium sized implementations
Pros and Cons
- 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
- 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
April 06, 2018
A solid ETL tool. Data doesn't stay in one place, SSIS keeps it going where it needs to go.
Pros and Cons
- 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!
- 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.
Pros and Cons
- 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.
- 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.
Pros and Cons
- Source systems connectivity (RDBMS, Flat Files etc)
- Embedding SQL and other code in case of complex business logic and data transformations
- Multitude of data transformation options
- Ease of use, easy to learn
- Skills availability in the market
- version control/configuration management
- Programmatic issues like NULL handling (it's RDBMS counterpart SQL Server database uses NULL differently)
- The source connectivity options should be enhanced
Pros and Cons
- Transform the data
- Lookups
- Performance
- Script components are always a problem
- Cannot debug properly inside of Script component
Pros and Cons
- 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
- https://sqldusty.com/2011/11/06/data-cleansing-with-regular-expressions-in-ssis/
- https://www.linkedin.com/pulse/using-regular-expression-file-filter-ssis-sean-werick/
- 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.
- 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.
Pros and Cons
- SSIS allows you to run many processes in parallel. Thus, you can run multiple data flows simultaneously to increase the throughput of the migration process.
- SSIS provides many tools for transforming data during the migration process.
- The one issue that I have with SSIS is that sometimes the business logic gets baked into the SSIS package. This can make it harder to debug. In some cases this makes sense if the source and destination is not a database. However, when using a database as a source I prefer to manipulate and transform the data via sql and then simply expose the dataset to SSIS after the data has been prepared. I find it easier to write and debug sql directly rather than working in SSIS. However, in cases when a database is not involved then putting the business logic in SSIS makes sense.
Pros and Cons
- Native data connections to SQL Server and Azure SQL DB and DW
- Flat file processing
- .NET C#/VB scripting
- Ease of use in designing and implementing control flows within conditional processing and looping
- Integration with Access/Excel should be more seamless and less problematic
- CASS certified address standardization
- Higher performing Slowly Changing Dimension functionality
- SFTP
- Incremental loading (deletion, upsert, etc.)
- PowerBI integration. I really really really want to be able to refresh reports via IS packages
- More Azure administration tasks
- Office365 and Sharepoint integration
August 05, 2016
Use it if you have a Microsoft Stack
Pros and Cons
- Clear GUI and ETL workflow. It's very easy to understand how the data is being managed. When pulling up a SSIS solution that someone else has created, it's very easy to see what's going on-- how the data is extracted, how it is transformed, and how it's being loaded.
- Deploying scripts. Once a proper package store is configured, you just need to hit deploy and it handles the rest. It's also flexible enough that you can still use SSIS packages without using an SSIS DB for version control by calling them through the file system. Or if you're one of those people who love batch scripts, you can also execute the packages through command line.
- SSIS Package Store. It's a great way to manage your versions and deployments. Bonus is that if you use a package store, it'll also give you error reports after the fact if a package fails for debugging. It'll tell you exactly what step failed and why.
- I think it handles undefined/dynamic data sources poorly. Considering that we use it primarily to ETL data from other systems across the whole organization to bring into our BU's data warehouse, we sometimes have issues when the source has changed. If someone adds a column without letting us know, we'll need to modify the SSIS packages.
- Sometimes the error codes are vague or cryptic. When debugging a SSIS package I have to google the code or error message and hope someone has a similar issue on stack overflow.
- SSIS really only works if you're already using a lot of Microsoft Products like Microsoft SQL Server or SQL Server Reporting Services. As mentioned in the name of the application, "integration services", it's designed to integrate your products together so that you can get the most out of it.
Pros and Cons
- SSIS Integrates very well with other Microsoft products including Excel and Access. Other ETL tools may have a difficult time integrating with Access, so we have observed SSIS to be superior in this regard.
- SSIS has the capacity to do a fast bulk load (BCP) with transformations, within the bulk load itself. This feature is not available when utilizing the BCP utility outside of SSIS or from other ETL tools. To be clear, the transformation is occurring within the BCP component itself. Other ETL tools will have to utilize a non-BCP load (slower) or do the ETL after the load. This is a great feature I have not seen replicated in other tools.
- SSIS integrates seamlessly with SQL Server RDBMS, including SQL Jobs and Stored Procedures.
- SSIS has nice support, tools, and wizards for fixed length file processing.
- SSIS IDE (SQL BIDS) is lacking, particularly when compared to Visual Studio for .NET development. It was carried over (at least in look and feel) from the legacy DTS tool. It could use a complete redesign from scratch. Considering how superior the VS .NET IDE is, the inferior SSIS BIDS IDE is unacceptable.
- SSIS is very Microsoft centric. This is a strength when dealing with pure MS technologies, but becomes a weakness when dealing with disparate, distributed systems, including cloud computing. Other ETL tools for example easily integrate with everything from AWS to Google Drive to Sales Force.
- SSIS deployment model is clunky and non-intuitive.
May 26, 2016
SSIS is terrific for all your ETL needs.
Pros and Cons
- SSIS can query, filter, and transfer data between databases on different servers without establishing explicit trust relationships between those servers.
- SSIS can be used to refresh a reporting database from a transactional source database, transforming or flattening the data and tables as necessary to facilitate reporting. This can be done incrementally, or by emptying and refilling the reporting database from scratch.
- SSIS is configured through graphical interfaces that make it relatively easy to see the flow of data including where problems occur.
- SSIS has a number of tools that allow you to debug SSIS packages and track down problematic data or configurations.
- SSIS allows you to program Script Tasks in C# and VB allowing extremely powerful functionality including looping and sending consolidated alerts.
- SSIS allows you to control virtually every part of the SSIS package (connections, variables, etc.) using configuration files so you can have one package that can be used in several different places (such as dev, test, and production environments) only by editing the configuration file that the package uses when the job is scheduled.
- One of the most frustrating things about SSIS, at least in its 2008R2 incarnation, is that of the annotations. Formatting annotations, getting them to wrap in the box, etc., is either extremely difficult or impossible without editing the XML of the SSIS package. You also CANNOT copy and paste annotations, so if you have several similar ones, you have to type every one of them from scratch. It makes you want to scream.
- In the 2008R2 incarnation, setting up tasks to pivot data is very tedious and non-intuitive. I have heard that this improved in SSIS 2012, along with performance. We are trying to upgrade our SSIS and SQL server environment to 2012 or 2014 as soon as possible.
- Sometimes datatype conversion doesn't work implicitly when it should and you have to do it either explicitly with a query or with a datatype conversion task.
July 15, 2016
SSIS fits perfectly in Microsoft environments
Pros and Cons
- Transform data
- Issue Logging
- Event Notification
- Needs more connections to environments such as Hyperion
- Not as robust as Informatica
- No built in metadata management
May 23, 2016
SSIS is great if when used in the right place
Pros and Cons
- Makes it very easy to move data form one system to another
- So many out of the box transformations
- The script task gives you so much flexibility as you can write C# code to do almost anything
- Easy deployment and configuration after the 2012 release
- Project deployment model to allow deployment of single packages
- Allowing to apply one transformation on all columns, like changing all column types from nvarchar to varchar instead of doing it one by one
- Mamed parameters from inline SQL statments using OLEDB command, currently they are called parameter 1, parameter 2...etc., which is confusing when you have many paramters
- Pass authorization tokens when calling remote processes like a console application, currently being called as anonymous
May 06, 2016
A Reliable Data Preparation Tool.
Pros and Cons
- Providing developers with a wide range of architectural options.
- Providing the ability to connect to a wide array of data sources.
- Proving many different deployment options.
- While SSIS does provide a plethora of architectural options, all of these options can at times be overwhelming. Some competing products offer a more straight forward and streamlined approach.
- SSIS does not currently provide any templates, although this is supposed to be addressed with the upcoming release of SQL Sever 2016
- Connecting to Oracle databases is not easy, SSIS still requires the installation of other tools.
April 15, 2016
A high-performance ETL product
Pros and Cons
- High data load speeds
- Many data processing modes
- Many data sources and destinations data possible
- For some tools you need to integrate third-party packages
- Depending on the version you must have the right visual studio version to develop and deploy
- Not really intuitive
SSIS Scorecard Summary
Feature Scorecard Summary
About SSIS
Microsoft's SQL Server Integration Services (SSIS) is a data integration solution.
Categories: Data Integration
SSIS Technical Details
| Operating Systems: | Unspecified |
|---|---|
| Mobile Application: | No |