Way back before there was indoor plumbing, there was this thing called DTS. It was used to process data in SQL Server, a typical use case involved getting data from text files and loading them into tables.
It sucked. A lot. You could program them graphically and/or in Visual Basic, but they were a bitch to write and maintain (somewhere on this blog there’s an entry of how to export them using Microsoft Word, which was necessary if you wanted to edit them in bulk since they had connection strings and whatnot embedded in them). Migrating from one server to another when you had hundreds of them was a……non-trivial task.
So, when word came of this new thing called SSIS, which was a complete rewrite that would replace DTS, there was a spark of joy in the hearts of men. Only, as happens on occasion, to be snuffed out when it came out, as it was, in certain respects, significantly worse. Prettier, but significantly worse.
Apparently, it still can’t handle a basic use case. Take a csv file and import it into a new table, defined off of the data in the file. For anything but the most trivial of files, it still will give you ‘data would be truncated’ errors if you just try to do it. Even if the number of rows to be loaded is less than the default 200 rows that it supposedly uses to determine types, it still can’t figure out what to do. It’s just miserable.
I haven’t tried it on SQL Server 2014. Maybe after 10 years, they’ve gotten it right. Right.