Item: SQL Server Integration Services
Rating: 8
Author: Vishal Shah

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 and Cons

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).

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

SSIS is similar to Alteryx and Informatica PowerCenter in a way because these are all drag-and-drop ETL tools with similar functionality. Alteryx is a step ahead because it has some advanced ETL functionalities including statistical calculations etc. and a better ability to set process dependencies. Informatica is by far the most powerful ETL tool I've used but it can be daunting for a new user because it has so many options and configurations, SSIS does similar work with less complexity (it is easier to use).

Key Insights

Do you think SQL Server Integration Services delivers good value for the price?

Yes

Are you happy with SQL Server Integration Services's feature set?

Did SQL Server Integration Services live up to sales and marketing promises?

I wasn't involved with the selection/purchase process

Did implementation of SQL Server Integration Services go as expected?

I wasn't involved with the implementation phase

Would you buy SQL Server Integration Services again?

Other Software Used

Databricks Lakehouse Platform (Unified Analytics Platform), Snowflake, IntelliJ IDEA, Alteryx, Microsoft SQL Server

Likelihood to Recommend

Ideal for daily standard ETL use cases whether the data is sourced from / transferred to the native connectors (like SQL Server) or FTP. Best if the company uses MS suite of tools. There are better options in the market for chaining tasks where you want a custom flow of executions depending on the outcome of each process or if you want advanced functionality like API connections, etc.

Good for startups or companies with limited data variability, Big data companies will need better tools

Overall Satisfaction with SQL Server Integration Services