For developers, is it worth it to learn/use SSIS?

Question

I'm starting to get involved in quite a bit of ETL work a my current job, and everyone seems to be pretty partial to SSIS. I'm struggling trying to do the most trivial transformations through BI studio that would usually equate to a couple foreach loops with a pinch of LINQ. Im not sure of the use cases or users this tool would be useful for, and will probably convince management that I can do without. What's the best way to make my case, or if there are any recommendations for better tooling, that would be appreciated as well.

Answer 1

SSIS is a great tool, but you have to grasp underlying concepts first. It allows you to focus on the ETL process, instead of programming and debugging your code. There is a learning curve.

Take a look at Microsoft Project Real examples and see what would be foreach loop equivalent for something like that.

You may also want to take a look at Pentaho-Kettle and Oracle Data Integrator, but my guess is you'll have the same feeling.

Answer 2

As with everything, SSIS has its pros and its cons.

There is a steep learning curve, and often things that feel like they should be simple are not. Sometimes that is that fault of SSIS and its tooling, sometimes it is just a case of not thinking in the SSIS way.

One nice thing about it is that it is a server product so things like error handling and logging have been (at least partially) taken care of for you.

If you are building complicated ETL packages across different systems, then it is a very appropriate and powerful tool, and can be a great thing to add to your skill set, particularly when you need to be involved in the initial design process for a solution. If all you know is C# code, then that is all you can suggest, when an SSIS package may be just the right solution.

Currently there is a bit of pain around how SSIS integrates with other DB products than SQL (its Oracle support is horrible for example) but I believe that Microsoft is committed to SSIS and will keep improving it. If you work in a Microsoft shop then investing in learning it now is probably worthwhile.

---

And on the cons side, here is a post by Ayende talking about some of the key pain points with SSIS.

I don't agree with all of them, or don't think they are a deal breaker, but it is good to know what may hurt you later on.

• The lack of good logging extensibility has annoyed me - it is quite hard to log in a way other that SSIS's
• The configuration schema can be a real pain

I do think that the next version of SSIS will address all of those though... that's just part of the Microsoft world, things get better incrementally, but they do tend to get better.

Answer 3

I don't know SSIS, besides reading up some of the basics. Also like you I'm very comfortable with LINQ, and could quickly write code to push around and transform data.

But what if the table has 4 gigs of data? Do you think your LINQ code is going to handle that gracefully? It could be handled, but not easily, especially if there are a lot of tables cross-referencing each other. A naive LINQ implementation would try to load everything into memory and crash.

Another justification is because your coworkers are strong with SSIS. Any solution you give the company built on SSIS will be more valuable to the company than LINQ, since other people can pick it up and fix or extend it.