Overall Satisfaction with SQL Server Integration Services
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
- 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.
- It comes with SQL server, so if you are already using that for the DB, we had no charge for using SSIS for the same solution. That is one big advantage of Microsoft, they provide the whole combo for BI at once.
- ROI is a time measure, if you bought SQL just for the DB, but then you want to use SSIS, most probably it is already there.
- If you try even an open source solution, you will have to install it and get trained on it, for SSIS the learning curve is very short.
- Be careful, SSIS is for ETL, not for middleware at an enterprise level. In the begining it might work but it is not the purpose of the tool, for middleware solutions where the business depends on, don't get confused and if you are still looking for Microsoft go for BizTalk.
Although I did like TIBCO Jaspersoft and it can be mixed with TIBCO Spotfire for data analysis. If your ERP is SAP, then you must keep in mind using SAP BusinessObjects. As for free tools, and if you have a great and constant team of scripting (Perl, Python) engineers, then you can think about going open source.
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.