Transformation from MySQL DB to PostGre DB

[jlabuelo]

Good Afternoon

We are just checking Kettle to use it as our ETL tool to integrate different systems like our online store and our wharehouse ERP.

Problem we are getting is that our online shop is running over MySQL 5.1 DB and our ERP is running over PostgreSQL 8.3.

We have got to extract the info from our online Store to a text file and then import it from that file to the ERP system, but we think would be much more effective if we can run the transformation using the MySQL data model as a source and the PostGreSQL as an exit or destination, no using the text file in the middle.

We are trying this, however we are fiding, lots of problems, sure due to our lack of experience in Kettle.

Our questions would be

a) Is this possible with Kettle?

b) Our transformation is running fine, getting the data from MySQL, transforming it, howeven when we get to the insert step in the Extit step of the transformation to write in the PostGreSQL table, we get this error:

INSERT INTO c_order (documentno, ad_client_id, ad_org_id, c_bpartner_id, c_bpartner_location_id, c_currency_id, c_doctype_id, c_doctypetarget_id, c_order_id, c_paymentterm_id, createdby, dateacct, dateordered, deliveryrule, deliveryviarule, docaction, docstatus, freightcostrule, grandtotal, invoicerule, isactive, isdelivered, isdiscountprinted, isinvoiced, isprinted, isselected, isselfservice, issotrx, m_pricelist_id, m_warehouse_id, paymentrule, posted, priorityrule, processed, totallines, updatedby) VALUES ( PS_2,  1000000,  1000000,  1004461,  1004204,  102,  1000026,  1000026,  1018808,  1000003,  100,  2010/01/19 00:00:00,  2010/01/19 00:00:00,  R,  S,  CO,  CO,  I,  47.22,  S,  Y,  N,  Y,  N,  N,  N,  N,  Y,  1000213,  1000000,  1,  N,  5,  N,  38.98,  100) was aborted.  Call getNextException to see the cause. 

However if we get the insert sql sentence, and go directly to the PostgreSQL database editor, running a SQL we need to transform it converting everything to "String" even fields are integer, date....

INSERT INTO c_order (documentno, c_bpartner_id, grandtotal, totallines, ad_client_id, ad_org_id, isactive, createdby, updatedby, issotrx, docstatus, docaction, processed, c_doctype_id, c_doctypetarget_id, isdelivered, isinvoiced, isprinted, isselected, isdiscountprinted, c_currency_id, paymentrule, c_paymentterm_id, invoicerule, deliveryrule, freightcostrule, deliveryviarule, priorityrule, m_warehouse_id, m_pricelist_id, posted, isselfservice, c_bpartner_location_id, c_order_id, dateordered, dateacct) VALUES ( 'PS_2',  '1004461',  '47.22',  '38.98',  '1000000',  '1000000',  'Y','100',  '100', 'Y',  'CO',  'CO',  'N',  '1000026',  '1000026',  'N',  'N',  'N',  'N',  'Y',  '102',  '1',  '1000003',  'S',  'R',  'I',  'S',  '5',  '1000000',  '1000213',  'N',  'N',  '1004204',  '1018807',  '2010/01/19 00:00:00',  '2010/01/19 00:00:00')

Any ideas of what can be wrong... or we should stay with the MySQL-File-PostGreSQL approach?

Thanks a lot

[darrell.nelson]

It should be possible to read from one database and write to the other on the fly. I'm very familiar with MySQL but not at all familiar with Postgre. I suspect there are differences in the types of columns supported, i.e., the handling of boolean data types. Kettle also makes some conversions of these types and needs to be tweeked to behave.

What did you find out when you did this? "Call getNextException to see the cause."

[jlabuelo]

Hi thanks a lot for the help.

Yes we thought at the beginning that could be a problem between date formats, but did not find the error.

About "Call getNextException to see the cause." sorry we are quite new in Kettle, not in ETL as we used to work a lot with Informatica, but dont know what that means or what we must do to "see the cause".

Is there any message window or button we need to press to see the exact cause?

Thanks once more

[darrell.nelson]

I think that error is coming from Postgre. Kettle is only reporting it. Don't shoot the messenger! There should be a way in Postgre to get information on the last error encountered. I know that functionality is in MySQL.

[jlabuelo]

Thanks for the help, let us take a look to PostGre so

Cheers!!!