A quick connection to Salesforce with Pentaho

I have been tasked with create a quick way to load orders into sales force by my employer.  They have set up the custom order type in sales force and provided me a login and csv file.  This is the limit of my knowledge of sales force, so I am starting from scratch.  I was able to look around their developer site long enough to find a wsdl and was well on my way to creating a custom c# app to attach to it and simply pars the csv file, but decided to dual purpose this experience and try to do it with the new tool in the shop, Pentaho. 

Pentaho, if you're not familiar, is a ETL / DTL tool that runs on java and creates files called kettle files.  http://www.pentaho.com/  These files script data transformations and movement as transformations and jobs and can be run on a server on a scheduled basis.  The UI editor for configure it is called spoon and is basically a 100 click tool (100 clicks for every line of code I could write).  Its basically an attempt to make the standard process of file reading, writing and data conversions all take place in this tool.  Its a nice idea as who likes writing file readers and parsers?  Well me too sort of, but you get my drift.

Anyways, I crack open Spoon (Pentaho front end), create a new csv file import type and configure it as below.

Basically, I browse to the file I want to load, make sure its comma delimited and quote enclosed then press get fields, it did a nice job in this case and I was able to simply press ok.

Next, I was looking for web server endpoint to tie the data too, but instead found the SalesForce endpoints sitting there for me.  So I grabbed the upsert one (as the files I'm getting weekly are total refreshes so upsert is the way to go).  I configure it like below. 

Basically, all I had to do was enter a username and password as well as the select the custom order object we had created in SalesForce and then edit mappings.  This is where you tie file fields to the sales force service fields.  This isn't too hard and not worth picturing, you can see a few in the picture above.  Then I was done with the transform and loading data to Salesforce.  I will talk more about finishing the Pentaho job off in another post.

Add comment