. I think SSIS will continue to be one of the most valuable tools in SQL Server (along with lots of demand for work), but it still needs improvement and enhancement to catch up to other ETL tools.
SSIS/SSDT needs a lot of improvement before I can recommend its use.
1. The issue where SSIS/SSDT will cache destination/source table metadata and will NOT release the cache without intervention is a show stopper for me. Nothing like changing something in a table and then having the tool you are using ignore the change. Making any schema changes to an existing SSIS/SSDT project requires careful QA to make sure that the tool actually is doing what you tell it to.
2. Having to use a wysiwyg type tool, that then requires you to assign variables in one place, put your SQL in another and then write C# code somewhere else (why do I need to write code for any of this?) is beyond crazy. There is code everywhere and the structure of SSIS/SSDT makes troubleshooting a massive pain in the butt.
3. Transforms/tools/widgets that just plainly don't work. I am looking at you merge. I can manually write a merge that will vastly out perform the merge in SSIS/SSDT. Truthfully, where I work, we ignore most of the SSIS/SSDT widgets and use stored procedures instead because we have found so many of the widgets to be poor.
I've said this before and I'll say it again, I can create an ETL 'package' in KSH/SQL faster than I (and I dare say many of the people that I work with) can create a SSIS/SSDT package and my 'package' will be more readable and easier to troubleshoot because you only have one or two pieces of code to look at instead of six or seven different places to look for to find the errors in SSIS/SSDT. My package just won't have the boxes and lines, which if you are an experienced ETL developer won't matter.
I agree about data portability.
Working in a health care setting, I can confirm that data between vendors/applications is not portable.
Even with standards like HL7 or X12, it can be very difficult to have two data sets 'talk' to each other.