#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.9
Simple transformations
Simple data transformations are calculations, data type conversions, aggregations and search and replace operations
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
Areas for Improvement
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.6
Complex transformations
Complex data transformations are data normalization, advanced data parsing, etc.
Category average: 7.4
Integration with MDM tools
Integration with master data management tools to ensure data consistency across the organization
Category average: 7.2
Who Buys & Uses SQL Server Integration Services (SSIS)
#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
Easy to implement needs improvement
Use Cases and Deployment Scope
We use this to run several ETLs to update the data warehouse on a daily basis.
Pros
- Easy interface to create data connections and data transfer object
- functionality to use multiple programming languages when an existing object is not sufficient
- functionality to call api
- easy to understand
Cons
- Reports that show the ETL running, also used for finding failures need drastic improvement
- The report should be able to show what objects are currently running and how they are performing. They will be helpful to troubleshoot when the ETLs are running slow.
- The reports currently only show tasks that have completed, not the ones running now. Add the running now tasks and their performance will greatly enhance the product.
Return on Investment
- It is one of the cheapest data integration tools in the market and working on great business solutions. If enhanced, it can be even better.
Usability
Alternatives Considered
Informatica PowerCenter (legacy) and CloverDX
Other Software Used
Google BigQuery, CloverDX, Microsoft SQL Server, SQL Server Management Studio (SSMS), Databricks Data Intelligence Platform
SQL Server Integration Services
Use Cases and Deployment Scope
In my organization, I use SQL Server Integration Services to automate data flow into different systems used by different departments. I create reports of our daily/weekly data for the upper management to review. Those reports are made with the help of SQL Server Integration Services. I fetch all the data for the report using SQL Server Integration Services. The reports are generated automatically now, I just have to send it to the upper management.
Pros
- SQL Server Integration Services helps in fetching the data from our cloud Genesys application to create/generate reports. The reports are on point with accurate data. It is so efficient and time saving for me.
- We use SQL Server Integration Services to keep the employee data records updated. It automatically enters all the employee data such as recent pay slips, recent performance reviews everything to the appropriate entry. Manual work has been very less since we started using SQL Server Integration Services.
- We also collect IT data using SQL Server Integration Services. It gets us detailed data of our systems which would so if there are any technical issues/glitches in the system. We can easily use that to resolve the issue rather than wasting time on manually searching where the issues started and what is causing the technical issue.
Cons
- I think the response time of real time data is very slow. It is difficult to use SQL Server Integration Services in a fast faced environment where we have to work on lot of real time data.
- SQL Server Integration Services issues are very hard to resolve. If there is a technical issue, the errors are not worded properly, It doesn't give a straight forward issue. They are very generic and it takes a lot of time to find what the error is and to fix it.
Return on Investment
- Time efficiency is the biggest positive impact, I think. It helps with large complex data. It saves a lot of time. Generating reports are super easy with it.
- Data efficiency - The data is also accurate. There are hardly any errors in the data.
- Negative impact - Doesn't work well with real time data.
- Its technical issues are very complex, it takes lot of time to find the problem, fix it and making sure that the entire system is working well.
Usability
Alternatives Considered
Azure Data Factory
Other Software Used
Jira Service Management, Freshservice, ConnectWise Cloud Backup, SAP SuccessFactors HCM, Workday Workforce Management, Greenhouse, iCIMS Talent Cloud, SmartRecruiters, Visier People
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.