SSIS Frustration? Enter Pentaho
For awhile now I have been wrestling with SSIS. I don’t like it at all so much to the point that the majority of my nightly packages are still on SQL 2000 as I have been unsuccessful in porting them to SQL 2005 SSIS.
A little while ago I went searching for alternatives. There are quite a few out there, some having very good reviews. I don’t have a list of what ones I looked at as this was a couple of months ago. The one that did stand out was Pentaho. Not only does it do everything and more than SSIS, but specific verions are available as open source. Namely, the Pentaho Data Integration is available as open source. This is the main ETL tool that they have and is written in Java. It is very mature and has a wealth of documentation that accompanies it. This was the deciding factor for me.
Originally I was using RhinoETL, but the only problem was the lack of documentation. This was ok for simple tasks, but once I had to do more complex data processes, and my lack of knowledge with boo it became more difficult to stay with RhinoETL. Ayende has a great tool, it just needs to simmer for a little while I think. I would definately consider going back once it has matured a little more.
In addition to the great documentation, Pentaho Data Integration also has a visual designer for creating data workflows. Here is one screen shot of a simple process that exports to a csv, uploads to a remote FTP and then send’s an e-mail upon success or failure of the task.
You can see a few of the tasks available to you on the left sidebar.
Everything in Pentaho is in a propietary Package Repository on the server so if you mess up an export you can rollback the repository to previous versions. Very nice feature!
Here are some more screenshots of the interface:
Very basic export with email
E-Mail configuration window:
Options for attaching logs to the e-mail to send:
This is just barely scratching the surface of the processes you can perform with this tool. The Data Integration server and clients are all available under open source liscences. As well as access to the forums and all the accompying documentation.
I will post more on the topic later. It has just been a very helpful tool and I think other people need to see some alternatives to SSIS.