Wednesday, July 29, 2015

SSIS vs Informatica

So I have been thinking a lot about Informatica vs SSIS lately, and I have decided I do finally have some opinions about where Informatica is much better and SSIS is better.

If I could say Informatica was better than SSIS in one major way it is in Debugging.  In SSIS the ability to debug a problem is relatively difficult if you need to get down into the guts of a data flow transformation, but this is something that is relatively easy when you are using Informatica.

SSIS though has some bonuses in that it has much better such as configuration and logging.  SSIS has the ability to do package configurations at the database level, and do so relatively dynamically.  The fact that SSIS also maintains each package as its own distinct "program" also helps.  Informatica has some issues when it comes to logging, especially if you have something that can run multiple times but not error consistently, if you don't configure it right you lose the instance that you errored on, where SSIS logs to the databbase per run, and isolates its logging as such.

Informatica also does a much better at handling large amounts of data in its built in components.  IE when you are doing 10 million or so rows through a lookup transform in Informatica, if you configure it right, it is going to outperform SSIS hands down.

SSIS beats it in one major way though, you can leverage SQL server much more efficiently, especially in the case of pulling data from stored procedures.  In Informatica you just don't have the SQL server integration, which means you can't have quite the power of SQL server at your fingure tips the entire time without having to do some hokey hacks to get around how Informatica is built.

SSIS and Informatica both have built in scripting language ability, but SSIS far out performs because it lets you use C# and import C# libraries.  I know there are those who believe Java is powerful, but there are a lot of things you can do with .NET frameworks that come easy to install, and generally standard, that gives SSIS a big advantage. Both Informatica and SSIS have issues when it comes to handling debugging of the languages.

Just some thoughts as I sit down.  I have had the opportunity lately to go back and do some difficult SSIS tasks while doing some particularly hard tasks in Informatica along the same line, and I find both to have interesting ups and downs when it comes to development.