Pentaho Spoon microsoft access output performance

Question

i'm trying to populate a database table (access) with the "Microsoft Access Output" step, but I get very bad performance. I mean my data was read from 2 xmls and got merged in 1 minute (36000 rows of data) and the access output is running now for 1 hour with 12 r/s. I set the Commit size high enough to commit all ma data at once (with Commint size 500 I got some java error after the 10th commit).

If I write my file in a csv and import it in access, that is way more quicker, but I would want to automate as much as possible from the process. Any suggestion is welcome on how to get better performance out of this.

Answer 1

I've never had good luck with the MS-Access output step. It seems to work much better if you create an ODBC entry for your Access DB and load it with a regular Table Output step.

Also you might check that you have the most current version of the Jackcess driver.

Edit: also, for automating flows of data, like writing a CSV and then loading it, you would use a Job. If you're only familiar with transforms, look at the docs on ETL jobs.