Overview
What is SSIS?
Microsoft's SQL Server Integration Services (SSIS) is a data integration solution.
SSIS, the must have extension for SSMS
Rapid and low cost integration with various sources for a DW
One of the best on-premise ETL tools
Amazing data warehousing tool
Good for startups or companies with limited data variability, Big data companies will need better tools
Data integration and migration are made easier with this tool.
Trust Microsoft to vanish your data integration troubles
Tool for data integration and migration
SQL Server Integration Services (SSIS) is a winner and helps solve many business problems effectively.
Worth of money
Data processing with SSIS
SSIS, free but not intuitive
Provides value to cost and excellent with MSBI Stack, But needs more features/updates
Great product to consolidate data for SQL Reporting purposes
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
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)
SSIS Tutorial Part 78- What is Multicast Transformation in SSIS (Quick Demo)
SSIS Tutorial Part 119-Execute SQL Task (Full Result Set) Demo in SSIS Package
SSIS Tutorial Part 72- What is Conditional Split Transformation in SSIS (Quick Demo)
SSIS Tutorial Part 02- How to Load Tab Delimited File To SQL Server Table in SSIS Package
Features
Data Source Connection
Ability to connect to multiple data sources
- 8.8Connect to traditional data sources(53) Ratings
Ability to connect to traditional data sources like relational databases, flat files, XML files and packaged applications
- 6.2Connecto to Big Data and NoSQL(40) Ratings
Ability to connect to non-traditional data sources like Hadoop and other big data technologies, and NoSQL databases
Data Transformations
Data transformations include calculations, search and replace, data normalization and data parsing
- 8.5Simple transformations(53) Ratings
Simple data transformations are calculations, data type conversions, aggregations and search and replace operations
- 7.7Complex transformations(52) Ratings
Complex data transformations are data normalization, advanced data parsing, etc.
Data Modeling
A data model is a diagram or flowchart that illustrates the relationships between data
- 8.6Data model creation(27) Ratings
Ability to create and maintain data models using a graphical tool to define relationships between data
- 7.1Metadata management(33) Ratings
Automated discovery of metadata with ability to synchronize and share metadata with other tools like Master Data Management
- 8.1Business rules and workflow(42) Ratings
Ability to define and manage business rules and workflows
- 7.3Collaboration(38) Ratings
Collaboration is enabled by a shared repository of project information and metadata
- 6.1Testing and debugging(48) Ratings
Tool to debug and tune for optimal performance
Data Governance
Data governance is the practise of implementing policies defining effective use of an organization's data assets
- 7.4Integration with data quality tools(36) Ratings
Integration with tools for cleansing, parsing and normalizing data according to business rules
- 6.5Integration with MDM tools(36) Ratings
Integration with master data management tools to ensure data consistency across the organization
Product Details
- About
- Tech Details
- FAQs
What is SSIS?
SSIS Technical Details
Operating Systems | Unspecified |
---|---|
Mobile Application | No |
Frequently Asked Questions
Comparisons
Compare with
Reviews and Ratings
(259)Attribute Ratings
Reviews
(1-25 of 26)- 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.
Good for startups or companies with limited data variability, Big data companies will need better tools
- 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).
Tool for data integration and migration
- 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.
SQL Server Integration Services (SSIS) is a winner and helps solve many business problems effectively.
- 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.
Provides value to cost and excellent with MSBI Stack, But needs more features/updates
- 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.
SQL Server Express + SSIS basics
- 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.
Less appropriate: big key-value data storages processed slowly, and hard to make data mining through uniting non-RDBMS and RDBMS data sources naive way. The data from non-SQL databases should be prepared accordingly to be represented in a table-like way if possible.
Great Tool For ETL Design
- 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.
Our use of SSIS
- 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.
SQL Server Integration Services (SSIS) is not easy for new users due to the plethora of options available.
SSIS enables you to build the data lake of your dreams!
- 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.
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.
- SSIS is particularly well suited for jobs that need to be consistent, repeatable, and error managed.
- Ongoing extract, transform, load [ETL] jobs that are scheduled or manual.
- One-time ETL with complex datasets.
- Migrations of large datasets.
SSIS is not well suited for small or simple datasets that can be copied or exported safely to flat files for import. It is possible to do this but would generally take longer to build in SSIS unless there was a good reason to .remove manual handling of the data in transport or the action needed to be testable/repeatable.
- 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.
SQL Server Integration Services: a great tool for small to medium sized implementations
- 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 for data integration and ETL!
- 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.
Good integration with MSSQL
- 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.
- 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
A solid ETL tool. Data doesn't stay in one place, SSIS keeps it going where it needs to go.
- 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.
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.
- 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.
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.
Great ETL tool
- 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.
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.
SSIS
- 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.
SSIS Addict
- 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
- Full refresh loading files (Excel and Flat File) into SQL Server.
- Integrating .Net (VB/C#) scripting
- Incremental loading
- OLAP database loading
- Streaming, real-time/near real-time loading
- Big data loading
Use it if you have a Microsoft Stack
- 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.
SSIS- Mundane but Dependable
- 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.
A Reliable Data Preparation Tool.
- 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.
SSIS is ETL's best buddy for skilled SQL Developers
- Easy connection configuration
- Powerful wizard for data mapping
- Native exception handling
- User-friendly interface
- Easy to learn
- Package can be deployed via Visual Studio
- Requires programming experience for custom tasks
- Shell version of VS used for SSIS package development doesn't support C# as scripting language (needs at least professional edition)
- Some tasks are hard to debug, aren't they?
SSIS, an ETL tool with a lot of hidden benefits!
We are using SQL Server Integration Services for mission critical import, export, and data transformations. It is being used across the whole organization for various business processes. Since we receive numerous file transfers, we have to create many SSIS packages and projects to import, and process the files. In addition, we send numerous files to clients.
We are also using SSIS to transfer data within our environment from server to server. When replication is not required, we prefer to use SSIS to transfer data at specific intervals to reduce overhead on the servers.
- File import and export
- Data transfer from source database to a destination database
- Database Maintenance tasks
- Data cleansing improved
- Additional database source connections