SSIS had its day, but in the big leagues it can no longer play
SSIS pulls data from various locations (mostly SQL Server instances but also some flat files and SFTP), mainly for transforming the data …
Overview
What is SSIS?
Microsoft's SQL Server Integration Services (SSIS) is a data integration solution.
Recent Reviews
Awards
Products that are considered exceptional by their customers based on a variety of criteria win TrustRadius awards. Learn more about the types of TrustRadius awards to make the best purchase decision. More about TrustRadius Awards
Popular Features
- Connect to traditional data sources (53)8.888%
- Simple transformations (53)8.585%
- Complex transformations (52)7.777%
- Testing and debugging (48)6.161%
Pricing
N/A
Unavailable
What is SSIS?
Microsoft's SQL Server Integration Services (SSIS) is a data integration solution.
Entry-level set up fee?
- No setup fee
Offerings
- Free Trial
- Free/Freemium Version
- Premium Consulting/Integration Services
Would you like us to let the vendor know that you want pricing?
26 people also want pricing
Alternatives Pricing
What is Clear Analytics?
Clear Analytics is a business intelligence solution that enables non technical end users to perform analytics by leveraging existing knowledge of Excel coupled with a built in query builder. Some key features include: Dynamic Data Refresh, Data Share and In-Excel Collaboration.
What is Vertify?
VertifyData is a cloud-based integration platform with core integration capacities, including a drag-and-drop interface and real-time synchronization. It also offers over 80 prebuilt connectors and templates, plus customizable integrations for scaling businesses.
Product Demos
Demonstration of Multicast transformation in SQL Server Integration Services (SSIS)
YouTube
SSIS Tutorial Part 78- What is Multicast Transformation in SSIS (Quick Demo)
YouTube
SSIS Tutorial Part 119-Execute SQL Task (Full Result Set) Demo in SSIS Package
YouTube
SSIS Tutorial Part 72- What is Conditional Split Transformation in SSIS (Quick Demo)
YouTube
SSIS Tutorial Part 02- How to Load Tab Delimited File To SQL Server Table in SSIS Package
YouTube
Features
Return to navigation
Product Details
- About
- Tech Details
- FAQs
What is SSIS?
SSIS Technical Details
| Operating Systems | Unspecified |
|---|---|
| Mobile Application | No |
Frequently Asked Questions
Microsoft's SQL Server Integration Services (SSIS) is a data integration solution.
Reviewers rate Connect to traditional data sources highest, with a score of 8.8.
The most common users of SSIS are from Enterprises (1,001+ employees).
Comparisons
Compare with
Reviews and Ratings
(259)Attribute Ratings
Reviews
(1-25 of 29)Companies can't remove reviews or game the system. Here's why
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.
- SSIS is good for small, simple data syncs.
- SSIS is good for moving data quickly into SQL Server (good batch performance).
- 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.
June 28, 2022
SSIS, the must have extension for SSMS
SQL Server Integration Service (SSIS) is a component of the Microsoft SQL Server database software that we use to execute a wide range of data migration tasks. SSIS is a fast & flexible data warehousing tool used for data extraction, loading, and transformation like cleaning, aggregating, merging data, etc. It makes it easy to move data from one database to another database. SSIS can extract data from a wide variety of sources like SQL Server databases, Excel files, Oracle and DB2 databases, etc.
- Offers real-time, message-based capabilities
- Tight integration with SQL Server and visual studio
- Ease and speed of implementation
- Standardized data integration
- Sometimes create issues in non-windows environments.
- SSIS lacks support for alternative data integration styles.
- Problematic integration with other products.
We use SQL Server Integration Services (SSIS) as part of our on-premise Data Warehouse architecture. We have an ELT pattern and SSIS is used almost exclusively for the Extract and Load steps. Almost all packages are generated using BIML (Business Intelligence Markup Language) as a means of templating and then scripting them.
- Easily connect with a wide variety of sources.
- Provide control and flow to job execution order.
- Able to extend functionality through scripting tasks.
- Not flexible when source/target tables and file formats change.
- Inflexible with regards to varying data types when Excel spreadsheets are used as a source or columns are added (as per previous item).
- Occasional issues around mixed development and production x86/x64 run times can be frustrating.
June 25, 2022
One of the best on-premise ETL tools
SSIS is an on-premise data transformation tool and the first major ETL tool we have used. We use it to ETL data from Workday, and other internal applications/databases to our internal databases. The product addresses the issue of getting data from CSV files from our SFTP and performing update/insert/delete operations on the data in our internal databases.
- Connect to various sources
- Basic transformations are included
- Connect to various destinations
- We still need plugins to connect to SFTP and other sources. Many vendors in the market give this by default
- It's an ETL tool and the ability to do ELT or ETLTL means spinning up more pipelines and packages
- It still has compatibility issues in Visual Studio 2017
June 24, 2022
Amazing data warehousing tool
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.
- 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.
- 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.
June 24, 2022
Good for startups or companies with limited data variability, Big data companies will need better tools
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.
- 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
- 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).
June 24, 2022
Tool for data integration and migration
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.
- 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.
- User-required automation needs much more scope.
- Exporting numerous tables in CSV format has to be done one by one by manual.
June 23, 2022
SQL Server Integration Services (SSIS) is a winner and helps solve many business problems effectively.
We used Integration Services to extract and transform data from a wide variety of sources such as XML data files, flat files, and relational data sources, and then load the data into one or more destinations. We also used the graphical Integration Services tools to create business solutions for the firm.
- Create Packages in SQL Server Data Tools
- Reuse Control Flow across Packages by Using Control Flow Package Parts
- Build Integration Services User Interface
- We can improve Integration Services error messages, including a list of most Integration Services errors and their description.
- Improvement to create custom event handlers for these events to extend package functionality.
- Better support for tools and wizards helping with Legacy Package Deployment.
June 23, 2022
Data processing with SSIS
Being part of the Healthcare department, we need to automate the data transfer from and to the company and hospitals. SQL Server Integration Services, allowed us, to create a robust platform, to process millions of records from each hospital everyday, the whole year, just taking care of small incidents, such formats, not related to the SSIS, itself. So, we relied on this process for keeping the data and the collection, working efficiently and providing outstanding service.
- Data transfer
- Automation
- Programability
- Scheduling
- Clarity on errors
- Support
December 08, 2021
Provides value to cost and excellent with MSBI Stack, But needs more features/updates
SQL Server Integration Services has been useful in implementing Extract, Load, and transform logic from various sources and destinations. we also use it to download data files from SFTP, FTP locations. We use it to refresh extract in tableau and run other 3rd party services via executing process tasks. It has been useful to address many other such challenges.
- FTP Downloads.
- Run 3rd party softwares.
- Intuitive UI.
- Need more connectors/sources/destination components.
- Ability to support not just Microsoft services but others too.
- More updates & new features.
We use SQL Server Integration Services to consolidate data from multiple vendor products. SSIS is the engine that gathers that data and stores it on our main SQL database instance for reporting purposes. It is being used by the enterprise in our organization. It created one pain of glass using SQL Reporting Services to show what systems are in what vendor products - such as agents installed yes/no? Last checking date/time, etc...
- If the job fails, we are alerted
- It has a easy to use GUI to build projects
- Does well with gathering data from different types of data sets
- Converting flat files of data requires a data transformation that is tedious
May 01, 2021
SQL Server Express + SSIS basics
We are using the SSIS as a major data export/import & converter between different data sources, including relatively old legacy stuff out from 90th. The beauty of SSIS is really advanced capabilities of data converts and mix up the data from different sources with all respect to keys and data relations. We use this mostly as an addition to the SQL Server Express edition (as a part of the import/export wizard) but on the sandbox I doing the tests of SQL Server 2016 Standard edition to mix up several RDBMS for data extraction into one data warehouse.
- Extraction and convert data from various sources
- Pipelining in the data extraction process
- Unions between different data sources akin one meta datasource
- Some labels in Visual Studio snap-in for MS SQL Server are collapsed on non-English (German & Russian tested) locales.
February 17, 2020
SSIS Just Works
SSIS is being used for data interchange. This includes using it for traditional ETL to a corporate data warehouse, as well as for the production or intake of flat files for interchange with external sources. Sources include traditional RDBMS, web services based cloud sources, and flat files. We are beginning the process of extending SSIS to the cloud in conjunction with Azure Data Factory.
- Process flow.
- Connection to a wide array of sources.
- Built-in upsert component.
- Better operability with source control systems.
We are currently using SSIS packages on two major projects:
1. To populate our data warehouse. Running SSIS packages from the server on a 10-minute schedule, we extract, transform and load the data into the warehouse to support all internal reporting and provide data as a service to our global partners.
2. Bulk data import to our CRM system. Building packages to run on-demand to bulk import structured data to our CRM instance.
In the past, we have used SSIS packages to complete a one-time migration from a legacy CRM system to the current CRM.
1. To populate our data warehouse. Running SSIS packages from the server on a 10-minute schedule, we extract, transform and load the data into the warehouse to support all internal reporting and provide data as a service to our global partners.
2. Bulk data import to our CRM system. Building packages to run on-demand to bulk import structured data to our CRM instance.
In the past, we have used SSIS packages to complete a one-time migration from a legacy CRM system to the current CRM.
- 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.
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).
- 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.
In my work in the health care field, we use SQL Server Integration Services as our default ETL tool of choice for pulling data into SQL Server from Teradata, Oracle, flat file and other SQL Server databases, and for creating data extracts to send to our clients, typically in the form of flat files.
- 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.
June 07, 2019
SSIS - Strong performer in the traditional data space, but can be improved in handling JSON objects
We implement data solutions for our clients. We use SSIS frequently as an ETL and Data Integration Tool for clients that have the Microsoft enterprise licensing as it offers decent base-level capabilities for traditional database management systems, and we can justify the tool selection since the technology cost-effective under MS licensing.
- 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.
January 15, 2019
SQL Server Integration Services: a great tool for small to medium sized implementations
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.
- 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
SSIS was being used as an ETL tool both by IT as well as business teams. It is now replaced by a competitor though. The tool was being used for basic extraction and loading purposes, with hardly any complex data transformation being done. Though there were use cases to exploit the tool, neither Microsoft nor in-house consultants really helped with the tool, with the result being it getting replaced.
- 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
April 06, 2018
A solid ETL tool. Data doesn't stay in one place, SSIS keeps it going where it needs to go.
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.
- 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.
February 15, 2018
Great ETL tool
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.
- 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.
- 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.
July 19, 2017
SSIS offers what I need for data processing.
In my current organization, SSIS is being used by the enterprise data warehouse team. This team uses SSIS to construct data warehouse, prepare data cubes, and fulfill ad-hoc data-related acquisitions.
- Presents the flow of data processing very well, making it easy to learn/understand SSIS packages.
- T-SQL and C# friendly.
- Comprehensive configuration, logging, and error handling.
- Some components are not working very well, including sorting, SCD, etc.
- Different components could have different syntax or data type definition.
- Not enough scripting learning materials.
March 29, 2017
SSIS Addict
Integration Services is the primary extraction, transformation, and loading tool we use to populate our SQL Server and Azure SQL DB and DW for our data and our clients' data. We do a majority of our logic for preparing both reporting and application data within SSIS components, scripts, or within T-SQL Stored Procedures executed within SSIS Control flows. It is only used within my group but my group is the only group directly populating our reporting databases.
- 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
I cannot say for the whole organization, but we use SSIS for just about all our automation processes. When managing a large data warehouse it is incredibly useful to automate the ETL process. We primarily use it for the data warehouse, but it's versatile enough to use for other automation tasks, reports, and notifications.
- 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.
June 04, 2016
SSIS - Our go to ETL Tool
SQL Server Integration Services is the go to tool for our Data Integration team for automating a number of disparate data imports. We deal with a number of file sources (Excel, CSV, TXT, etc) that need data transformed for import into our SQL databases. SSIS allows us to handle any number of file formats and types efficiently, and we can perform all data validations necessary to ensure utmost data integrity.
- Handling upsert functionality (i.e. performing lookups against existing data to determine new versus existing records).
- Automation of scheduled file imports. Essentially any consistent data source can be automated after a one-time SSIS package is set up.
- Ability to handle a wide variety of data sources.
- Excel data type handling - makes assumptions on a single data type where there might be multiple data types in a specific column.
- Better handling of data schema changes - If you have column changes or additions in your data source, it does not always implicitly make those changes across all data transformation tasks within the package.
- No reliable built-in functionality for SFTP connections.