Today we had our weekly team meeting. Part of my team is working on a new app to convert an ancient MS Access app into a WPF app. We're also introducing a new database for the new app. Thus we've got a need to migrate data from the old db to the new db.
Because of my background, having to be both an acidental DBA and developer, I'm used to using SSMS's Import and Export Wizard, making the necessary mapping modifications between two different tables depending upon the conditions of the target table. I have a little experience with SSIS, but not much. I know that the last step of the Import and Export Wizard it asks if I want to save it so that I can re-run it in the future. I'm pretty sure if I choose save, that it saves it as a SSIS package.
None of my teammates have ever had to be a DBA, accidental or otherwise. So when it comes to migrating the data from one system to another, they reach for Visual Studio and start writing code. Initially this took a few days. Now, because we're past 12 tables converted they're able to pump it out in a few hours, depending upon the complexity of the tables involved. However, I still think I could do it faster with the Import and Export Wizard, even if I have to include a couple of tables from the source and do some field mapping modifications.
However, one of my colleagues made a negative assertion concerning SSIS. Basically he said that there's lots of angry complaints on the Internet about how unreliable SSIS is. How poorly it does anything. How its written for DBAs because they can't code so they've given SSIS by Microsoft so they can migrate data from one place to another. How its impossible to see what SSIS is doing and therefore SSIS cannot be trusted.
All of these arguments plays very well with my colleagues. I, on the other hand, am not so sure. But one thing I am bad at (its a character fault I have, I'll admit) is responding to such out of left field claims. After all, so I think, I've never researched this. Is SSIS really that bad? So, I pose these questions to you, especially the real DBAs here. Is SSIS so bad that you really don't trust it? Or do you loath to use SSIS but have to, for some reason? Or, maybe, you have a few issues with SSIS but for the most part you find it useful and reliable?
Kindest Regards, Rod Connect with me on LinkedIn.
October 19, 2019 at 10:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
October 20, 2019 at 8:26 am
This was removed by the editor as SPAM
October 20, 2019 at 8:48 am
This was removed by the editor as SPAM
October 20, 2019 at 9:18 am
This was removed by the editor as SPAM
October 20, 2019 at 4:16 pm
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".
--Jeff Moden
Change is inevitable... Change for the better is not.
October 21, 2019 at 7:52 am
How its written for DBAs because they can't code
Charming. I dislike SSIS, and try not to use it. Fortunately I've rarely needed to.
October 21, 2019 at 2:46 pm
I'll be the dissenting voice here. I've used SSIS quite successfully for migrations, data warehouse loads, and file loads into databases. In my opinion, SSIS, particularly since 2012 with the SSIS catalog, is very good at moving and transforming data.
Like anything else, if you know how to use it correctly, it is a good tool, but still just a tool.
I'm often frustrated when I see data loading processes that don't use SSIS because it is what I'm most familiar with and I like the logging and error-handling I can do with SSIS, that, in my opinion, is more difficult with T-SQL.
SSIS is definitely better than hand-coding with .NET or some other language in my opinion. Microsoft developers aren't perfect but they are pretty smart and have probably done a better job optimizing memory use for the pipelines than any .NET developers I've worked with would do, and I've worked with a couple that wanted to basically re-create SSIS in .NET.
Also, the import-export wizard in SSMS is just SSIS and when you save it does create a package behind the scenes.
One tip I give people new to SSIS is to think of it like an object-oriented language. Don't try to do everything in one package. Multiple packages that each do one thing or simple things orchestrated by a master or parent package tends to be the pattern used by most experienced SSIS developers/architects.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 21, 2019 at 3:14 pm
My primary role is data migration and i use both SISS and T-SQL, I use the tool i find the most appropriate for the task i'm trying to achieve. That said, in the main i have successfully migrated many systems successfully just using T-SQL, this included pulling data from various sources for example CSV. I alway lean towards T-sql because i find it easier to decode any and fix issues.
***The first step is always the hardest *******
October 21, 2019 at 4:14 pm
The short answer is no, SSIS is not that bad. It's not the greatest ETL tool around but it's pretty good especially considering it comes free with SQL Server. And moving data in and out of SQL Server is exactly what SSIS is designed for and I'd trust it over writing some custom code. If those developers don't think it's reliable it's more likely they just don't know how to use it.
October 21, 2019 at 4:48 pm
I've gotten some great feedback here. Thank you, all who have responded. My conclusion is that even among DBAs, you're not all enamored with SSIS, for various reasons. Some do like SSIS and it works for you. I'm coming away with the feeling that I could use SSIS, at least the Import and Export Wizard, for some simple migrations. Certainly 1 table in the source to 1 table in the destination. I think that even a couple of source tables to a destination table is doable. Jack, I like your analogy of approaching of SSIS as an OOP language; doing small migrations in 1 package and connecting two or more packages together.
I guess that at the end of the day, in my environment, my colleagues are used to using C# for everything. So, when faced with migrating data they crack open Visual Studio and start to pound C# code. It's their hammer and all problems look like nails.
Kindest Regards, Rod Connect with me on LinkedIn.
October 21, 2019 at 4:50 pm
SSIS is a good (enough) tool, for what it does. It is actually very good at moving data around, to/from SQL Server and other platforms as well.
SSIS also has another very-hard-to-duplicate feature: it's very easy to set up a set of tasks to run async. That can be difficult, or even very difficult, in other languages.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 21, 2019 at 6:50 pm
Over the years as both a Database Developer and DBA, I've found SSIS to be a good tool. As others have said, not necessarily the best tool, but it exposes many capabilities that would be difficult to reproduce in .Net or PowerShell or some other programming language. I agree with Jack's comment about building small SSIS packages that each perform one task, and I also typically focus on the extract and load portions of ETL within SSIS, and do most of my actual transformations with plain old T-SQL.
I find it funny that .Net developers think SSIS was designed for DBA's. In my perspective, so much of it is in the developer's terminology and mindset. If it was written for DBA's, it probably wouldn't have all of the strange datatypes you have to convert and map to, and would probably be more script based than drag-and-drop, point and click property based.
October 21, 2019 at 11:40 pm
I know that a lot of people don't have the following kinds of problems with SSIS but I see them happen enough where I'm really happy that I've mostly divorced myself from such things. Please see the following currently active thread... and help if you can. I don't knpw enough about it to even hazard a guess anymore, especially when it comes to all that 32 vs 64 bit garbage. I thought they got rid of everything that was 32 bit (tongue in cheek on that comment).
--Jeff Moden
Change is inevitable... Change for the better is not.
October 22, 2019 at 4:36 pm
Over the years as both a Database Developer and DBA, I've found SSIS to be a good tool. As others have said, not necessarily the best tool, but it exposes many capabilities that would be difficult to reproduce in .Net or PowerShell or some other programming language. I agree with Jack's comment about building small SSIS packages that each perform one task, and I also typically focus on the extract and load portions of ETL within SSIS, and do most of my actual transformations with plain old T-SQL.
I find it funny that .Net developers think SSIS was designed for DBA's. In my perspective, so much of it is in the developer's terminology and mindset. If it was written for DBA's, it probably wouldn't have all of the strange datatypes you have to convert and map to, and would probably be more script based than drag-and-drop, point and click property based.
I do apologize for my colleagues mindset concerning DBAs and SSIS.
Kindest Regards, Rod Connect with me on LinkedIn.
Viewing 15 posts - 1 through 15 (of 45 total)
You must be logged in to reply to this topic. Login to reply