To be clear, I've not worked with SSIS beyond the tiny little maintenance plans that I used to make many years ago and so most of my "evidence" is anecdotal and hear-say although it's from people that I've worked with and trust.
To summarize, a good portion of my job over the years has been to replace things built in DTS/SSIS and the shedload of other languages called because SSIS couldn't handle everything that needed to be done. It's ironic that you say some people think that SSIS was created for DBAs and other "non-programming" types of people because there was, like I said, a shedload of stuff written to be called by SSIS in other programming languages written by develops.
Another part of my job has been to replace things written in other languages that don't even come near SSIS.
And, in general, I've done it all with great success using T-SQL and the occasional call to the command shell (mostly for file manipulation).
I've also worked with people that have done migrations from 2005 to 2012 and the like and they say that the SSIS part of things was the really bad part of it all where every package needed to be opened and fixed. Rumor has it that that problem has been fixed since 2012 but I don't know anyone personally that has done later migrations that also has SSIS packages to worry about.
I've had to import and export some of the damnedest things in pretty high volume and I have to say that I've not used SSIS for any of it. From lessons learned from other's anecdotal experiences, I've not even tried but once and was totally turned off even by the simple exercise of mapping a file (admittedly with a lot of columns) to a table.
I don't use the Import/Export wizard for much. I can only remember using that twice early on in my life. Once was to see how well it would work against CSVs (don't bother was the conclusion I came to) and the other time was to do a one-off migration of data (from about 100 tables) from Oracle to SQL Server (which was kind of handy but haven't used it since). I do have to say that it worked a real treat there, especially since we had the right drivers for the job!
I've also not been impressed with the solutions that people have used SSIS for. Of course, a lot of people on this site do use SSIS and claim to be quite successful with it. I'm just not one of those people and I've not had the privilege of seeing how they use it and if there's a better way. I can tell you that most attempts at importing spreadsheet data look really silly to me especially if the data on the spreadsheet had to be normalized in some fashion. I did a presentation on that a couple of times and should probably put in in an article but, my point is, I'm not impressed with anything I've seen done in DTS or SSIS and have gone out of my way to get rid of it whenever I can. Usually, there's also a shocking improvement in performance, concurrency, and maintainability, as well.
Whenever someone says the want to use SSIS for sometime, I usually ask what they're trying to do and then suggest a different method.
Again, others may and probably will have a different experiences with than I have.
I also cast a jaundice eye towards anyone that wants to write a custom import/export solution using the likes of PowerShell, C# (or whatever), or pretty much anything of similar nature because a lot of people aren't as good at it as they think and "slow" becomes the word of the day. There have been a couple of exceptions but those have been extremely rare.
As with all else in this business, "It Depends".
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)