Jeff Moden wrote:
A little bit off topic, if you good folks don't mind... I am curious about good experiences in SSIS (and, no... not being ironic there... they made the tool for a reason). I've had a couple of people tell me that it was an arduous task to migrate SSIS packages when they migrated from one version of SQL Server to the next, especially if the migration was to new hardware at the same time.
When we migrated from 2008 to 2012 is was arduous, however, there is a reason for that; SSISDB was introduced which was a brand new deployment method. It's (in my opinion) a significant improvement on the old MSDB and File System deployment methods, but moving from one deployment method to another is also involved.
As for moving after that, we're actually in the process of migrating at the moment, from 2012 to 2019. Moving our Development and Testing environments were honestly trivial; I just had to create the SSISDB (get annoyed that SSISDB ignored the
model database and created it in the wrong recovery model and lacked certain custom roles/users we want across the board), set up the keys, and then just deploy. Took me, 10 minutes?
I wouldn't say I'm an advocate for SSIS; it has an awkward learning curve and it's very clunky at times. I wouldn't say I've had bad experiences with SSIS, but I wouldn't say it's specifically been "good"; I'm pretty neutral to it. I might be more positive when we're on 2019 across the board, and I don't have to redploy an entire project every time I make a change to a single package. It is, however, a better option than Powershell on SQL Server 2012 though; the fact that SQLPS on 2012 runs Powershell 2.0 is a significant limiting factor on some of the things I have wanted to do in the past.
I do make a lot of use of it though; we have several ETL projects that it uses. Part of that reason, however, might be because of the 3rd party application we use, and the lack of control we have over it; forcing ETL process that should be application based to be migrated elsewhere, and often that ends up in SQL Server's SSIS tool. It does those jobs well, provided that staff members follow instructions properly. 🙂 (the amount of times I see Excel files with different headers, or are passworded (which obviously SSIS can't read) and I get an email saying "It's not working" infuriates me. Especially when the message they get explicitly asked them to check that first.)
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.