That's part of the good thing about SSIS. It allows people that may not know how to otherwise do things to get things done and allows some great flexibility into how it's done thanks to users being able to use a GUI... for the simple stuff.
I cannot speak for anyone else but that particular "feature" of both the old DTS, SSIS, and now SSDT has been a part of the problem for me in the past and Microsoft has been a huge pain in certain areas... the same areas that they've been a huge pain when it comes to SQL Server (deprecation and wholesale replacement instead of fixing what exists. PowerShell users are going through that mess right now, as well) I'll also make the disclaimer that I was so put off by DTS and my introduction to SSIS that I didn't take the time to learn either to the point of being a casual user, never mind being an expert so I could be wrong in the areas below. But I've not needed DTS or SSIS to do the stuff I've needed to do even in a pinch.
Revisiting "Mr. Peabody" and the "Wayback Machine", I was working as an "on-site full time contractor" for a particular company almost a decade ago. They kept me pretty busy and, suddenly, they stopped giving me things to do which, of course, concerned me greatly and so I asked what was going on. It turned out that everyone was focused on a major problem??. They had a DTS package that was part of a system to download and import the files that formed the crux of the entire business.
The problem was that it was taking 45 minutes just to get one file ready for import, never mind doing the actual import and, thanks to a good upswing in business, there were no longer enough hours in the day to process all of the files even if they were to standup a couple of parallel boxes, which they couldn't do because of serious budget restrictions. They were trying to make improvements to that process but, because the people who built it were long gone (due to a serious reduction in force), they couldn't figure out what to do because the package called PERL (no one was left that knew how to program that), VBS, and ActiveX components/scripts and a few other things that I can't remember. They had essentially created a "Tower of Babel" that no one that was left could fix.
The files that they were trying to import were from DoubleClick.net and they looked like TSVs that were created from spreadsheets. They could be anywhere from 15 to more than 400 columns wide, only the first 9 columns were ever named the same, ??there were "pairs" of columns that needed to be detected as pairs and transformed to 1 row for each pair, and the were groups of 4 columns that also needed the same treatment. The headers had two rows to help discern those areas. The DTS package was a train wreck in that there was a conditional branch that latter needed to merge back into the main flow and they used "ActiveX" (something else no one knew how to do but I taught myself to do in the "discovery" process) to enable one leg and disable another because DTS had no built in mechanism to do such a re-merge conditional. Generally, the daily files only had about 30,000 rows but, like I said, each file was taking 45 minutes (or longer for the really wide files) and DoubleClick.net refused to change the format unless the company paid them more than it would cost to stand up a dozen new boxes.
I suggested that they move away from all the PERL and other stuff that was being called and do it all in stored procedures??. I was told that I "obviously didn't know much about ETL because there's no way to do any of this in stored procedures". I was also told (for the first time) that "Even if you could do it in stored procedures, just because you can do something in T-SQL, doesn't mean you should because SSIS is the obvious tool of choice for ETL".
An additional problem that was also rearing it's ugly head is that they wanted to migrate from SQL Server 2000 to 2008 and even the main office in NY had no clue how to migrate the DTS package and all the junk that was attached to it.
So there's the first problem that I have with such external tools
. They've made it so easy to do things that it enables totally unqualified people do "whatever they need to do" to get some job done without considering what the future may bring and I've not even touched on the increase in "surface area" that the package (nor the rest of the system to support it) required. For those that are good about such things, please don't take that as a slight against your wonderful (and there's no sarcasm there at all) talents or such tools as SSIS or SSDT. In the right hands, they can be awesome tools. They've just made things so easy that people with the "wrong hands" can use it almost willy-nilly especially since there's a lot that it can't actually do very well and so people introduce what they do know in the form of callable scripts and other things to make up for what it can't do.
BTW, because they were all busy trying to keep the proverbial sky from falling, they left me alone for a period of time. It took me 3 days to analyze the system and 4 days to write/test (I always test along the way)?? a replacement in the form of 2 stored procedures. The stored procedures did much more than just get the files ready for importing. Instead, it imported the data directly to staging tables, did all of the validations that the next step in the process would have done, and did the merge into the final tables. Except for the actual downloads of the files, it replaced their entire system. It took them a week to test because they said it ran too fast to be working correctly. My new system was doing the whole shebang at the rate of 8 files every 2 minutes and it was ALL done using T-SQL in the form of 2 stored procedures and they handled every file including some new ones that cropped up. And, no... I'd never done such a thing (unknown number of columns, unknown column names, double headers, etc) in the past. I just took what I'd done in the past and expanded some capabilities.
Fast forward a couple of years and I'd been hired by a company as a Database Developer (a new position for the company) along with an SSIS Developer (she was fantastic and she and I worked VERY well together). The company needed to do imports of millions of rows at a time and some of the rows could be as wide as 250MB (not a misprint) each from some "pre-processing" software, do a bunch of processing on it, save the data as files for another chunk of software to work on, import that result, do some more processing and come up with some final files for a final application to use. ETL on steroids. The obvious choice is SSIS, right?
Through no fault of the SSIS Developer, the processes took too long (even with some stored procedures involved... like I said, she was fantastic but the files were huge and the processes were insane)?? and the number of changes (it was a new system we were developing and requirements were mostly unknown due to some really stupid managers) became overwhelming (after a year or so) and the changes to the packages needed to be deployed to multiple systems. The SSIS Developer could no longer keep up and there were very few windows (24 hour shop) where she could deploy the changes. To make matters worse, some of the new requirements just couldn't be done in SSIS (according to the SSIS Developer... I took her word for it because she had demonstrated her stuff to me and she was that good) and we also started to run into the problem of "not enough hours in the day" to process all the huge files during some of the weeks, especially if they needed a "rerun".
A couple months prior, she and I had worked on a particularly nasty requirement for "load balancing" of human work for all the documents that we were processing. She had started it out with asking me how to do distribute such work load assignments in T-SQL because she didn't know how to write C# (thank goodness for that) and I told her "For that, you need a thing called a 'Quirky Update'". I showed her my article on SQL Server Central and told her to study it so that we could tackle the problem together the next day. Heh... she not only read the article but she came in extra early to "play with the code". Like I said, she was fantastic and when I got into the office, I went up to her and asked if she read the article. "Sure", she said. "Let me show you what I've done so you can check it". Not only had she read the article but she had also solved the problem using the 'Quirky Update". There was nothing missed, Her code was perfect.
When the rest of the system started bogging even more and the deployment schedule became insane, she approached me and said "We're doing this the wrong way, aren't we"? To make a much longer story shorter, she and I rewrote everything using stored procedures and I spent a good amount of time teaching her the "Black Arts" of T-SQL (including some "automapping" tricks against unknown file structures), which she sucked up like a sponge. Deployments became a snap because it took only milliseconds to deploy new or modified code to each server (and could be deployed in the middle of the day) and everything ran "Nasty Fast" blowing away all the expectations of management.
So that's the 2nd problem. It's supposed to be an ETL tool but, IMHO, it's not a really good one. There are a lot of things that it doesn't appear to be able to do and, sometimes, performance becomes quite the problem as do deployments. I understand that they've made deployments a bit easier, especially with not having to edit every package for connections but, damn... it sure too them long enough to fix that "little" nuance. A good friend of mine had to upgrade from 2005 to 2012... and they had to edit more than 400 SSIS packages to make it work. Not my idea of a complete tool.
Ah... then comes the fun part. You'd think that MS would get their bloody act together in making their products talk with each other. Let's talk about importing spreadsheets. And no... I'm not talking about the kind of nicely normalized or EAV style spreadsheets that you had to Sumo wrestle your users into following. I'm talking about the real way they use spreadsheets... more than 2 dimensions, temporally horizontal in temporal groups, and a whole bunch of database "sins" that are completely normal and expected in the real world of spreadsheet users. For example...
Whether you have to import it directly from the spreadsheet file or a TSV file, that's a bloody mess for importing even into a staging table. You finally get it all done and have a beautiful package to do import it with and what happens to the spreadsheet next month? This...
They changed the temporally based multi-line header. They added a month between March and the Total. The add a new category column. Etc, etc. They even changed the starting cell from B3 to D5 and added "Another" column to what needs to be distributed to each row when you do normalize this. What this means for those people that are using SSIS/SSDT because it's "simple" to use now have to go and change the package to bring things in and then they realize that they have to do so every month. They either waste a huge amount of time doing just that (and there may be dozens to hundreds of these things) and that's when they realize that it's not in useful normalize form. They either accept that and commit more database sins or they "write a script" or maybe even a DLL to call as a CLR or some other damned thing (PowerShell) to normalize the data because they don't know enough about T-SQL (which is the reason they used SSIS to begin with) to do the job and you end up with dog slow imports like the very first scenario I mentioned in this post. Because no one is watching them or doing reviews (which is another reason they decided to do it this way... get's around the DBA), it continues to a fault and then they blame the DBA for the "server is slow" especially when the DBA finds out that it's them eating the face off of the server.
And yeah... I did a presentation on this one. It boils down to 3 fairly short stored procedures and it's "self healing". In fact, you can throw most spreadsheets at it an it will analyze them and "auto-magically" flip things into a nice EAV for further easy manipulation.
So that's the third thing I don't like about it. It allows people to "get around the DBA" either intentionally or just due to ignorance.
The fourth thing I don't like about is, this is a very common type of import for every company I've ever worked for and for every company of people that I know have worked for and yet MS made no contingency in their myriad GUIs and Wizards to actually handle such things in a correct manner.
Then there are BIG file imports. Let's see you set up imports for new files that have 400 columns or even just 40 columns with even a chance at doing the right thing for data types. This is why people end up with "hundreds of SSIS packages". Can you create the package using only SSIS in less than 2 minutes? You can using T-SQL... even miserable spreadsheet stuff with occasionally text qualified cells that changes format EVERY reporting period. 😉
Heh... sorry about the rant and that's not all of the bad stuff I've run across and had to fix but, as Katie Couric would say, "Now you know". 😉