#1 most frequent
United States of America
56.0%9,234 installations of 16,489
What is SSIS?
Microsoft's SQL Server Integration Services (SSIS) is a data integration solution.
Categories & Use Cases
Product Demos
Top Performing Features
Connect to traditional data sources
Ability to connect to traditional data sources like relational databases, flat files, XML files and packaged applications
Category average: 8.8
Data model creation
Ability to create and maintain data models using a graphical tool to define relationships between data
Category average: 8.4
Simple transformations
Simple data transformations are calculations, data type conversions, aggregations and search and replace operations
Category average: 8.1
Areas for Improvement
Integration with MDM tools
Integration with master data management tools to ensure data consistency across the organization
Category average: 7.8
Connecto to Big Data and NoSQL
Ability to connect to non-traditional data sources like Hadoop and other big data technologies, and NoSQL databases
Category average: 7.8
Testing and debugging
Tool to debug and tune for optimal performance
Category average: 7.1
Who Buys & Uses SQL Server Integration Services
#2 most frequent
United Kingdom of Great Britain and Northern Ireland
6.8%1,124 installations of 16,489
#3 most frequent
Canada
6.3%1,040 installations of 16,489
SQL Server Integration Services - still kicking after all these years
Use Cases and Deployment Scope
Data Engineering and integration. Move data between different on-premise servers no matter the platform. Since we have an Enterprise Agreement with Microsoft, it does not cost any extra to use SQL Server Integration Services unlike Azure Data Factory or Informatica. Wherever data transfer is involved, it is the de-facto standard in our company.
Pros
- Easily connects to source and destination systems
- GUI interface - so minimal learning curve
- Script task feature enables extensibility and access to .NET framework
Cons
- Zero enhancements by Microsoft in a very long time
- Has fewer connectors compared to Power BI or ADF
- Not ideal for handling huge datasets
Return on Investment
- Saved tons of money over the years by not having to go out and buy another licensed product. Since we have been using it for 15 years, that would be approximately 15 times $25K
- Saved a lot of development hours due to the minimal learning curve, even our interns are able to use it thereby freeing time for higher skilled employees to work on more complex problems
- A big user community helps speed up trouble-shooting and support
Usability
Alternatives Considered
Azure Data Factory
Other Software Used
Microsoft Power BI, SQL Server Management Studio (SSMS), Microsoft SQL Server
Good for startups or companies with limited data variability, Big data companies will need better tools
Use Cases and Deployment Scope
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).
Return on Investment
- Good ROI for companies using the MS suite of tools (SQL Server / SSDT) without having to purchase additional tools.
- SQL Server is expensive, so running very complex processes using SSIS will become quickly very expensive.
Alternatives Considered
Alteryx and Informatica PowerCenter
Other Software Used
Databricks Lakehouse Platform (Unified Analytics Platform), Snowflake, IntelliJ IDEA, Alteryx, Microsoft SQL Server
SSIS, free but not intuitive
Use Cases and Deployment Scope
SQL Server Integration Services is an extremely powerful tool that we're using exclusively for SQL Server inventory and other jobs. While it is very powerful, it's also not exactly intuitive in my opinion. I had not used this before my current job, and it was already setup when I joined. I've had to edit some of those jobs a handful of times and I just have not yet been able to make it 'click' in how it works. I seem to think oppositely of how this was designed (potentially just the jobs I've had to interact with), but almost everything is counter-intuitive.
Pros
- Runs flawlessly after setup properly
- Powerful, as mentioned
- Free
Cons
- Confusing operation, even with its gui
- Requires special install and setup
- Requires the Visual studio integration services install (separate interface from SSMS)
- Could be more easily integrated into SSMS, but may also be used for other products that I'm not aware of
Return on Investment
- Without this, we would have to manually update a spreadsheet of our SQL Server inventory
- We would also have poor alerting; if an instance was down we wouldn't know until it was reported by a user
- We only have one other person who uses SQL Server Integration Services , he's the expert. It would fall to me without him and I would not enjoy being responsible for it.
SSIS had its day, but in the big leagues it can no longer play
Use Cases and Deployment Scope
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.
Return on Investment
- SSIS provided good value when there were few competitors, for somebody already using SQL Server Enterprise Edition.
Alternatives Considered
Fivetran, Stitch, from Talend and Etleap
Other Software Used
Snowflake, Fivetran, MuleSoft Anypoint Platform
Amazing data warehousing tool
Use Cases and Deployment Scope
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.
Pros
- 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.
Cons
- 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.
Return on Investment
- We can easily merge data on a variety of databases.
- Loading and migrating data is fast.
- It offers a simple system for analyzing raw data.
Alternatives Considered
AWS Glue
Other Software Used
Azure Virtual WAN, Azure Bot Service (Microsoft Bot Framework)