Import, Export, Transfer Multiple Tables with Pentaho Kettle

Working with Pentaho for the latest requirement we had on migrating sybase database to PostgreSQL, I had to find a way to Import/ Export all the tables from Sybase to Postgres and this had to be done quickly and with minimum effort. If it were SQL Server, I could go with the simple Import/ Export wizard it would have been a piece of cake. But Pentaho also has a similar approach and here's how to do it,

Step1: Create a Job in kettle.

You can do this simply going to file --? new --> Job

Step2: Create Data Connections

You need to create the source and destination database connections. You can do this in the view tab, right clicking the Data Connection and selecting New Data connection.

Step3: Open the wizard to Create the Job

Once the source and destination connections are in place, it's time to create the job that will actually copy the data. Go to Tools --> Wizards --> Copy Tables. In the first view select the source and the destination connections and press next. Then select the tables from the source connection that should be copied to the destination. In the third step of the wizard, provide details where the job and the relevant transformations should be saved. Press finish and it will create a new job with all the Create Scripts and Transformations.

Things to consider,

1. The wizard would not create Create Scripts for already existing tables in the Destination Database.
2. Based on what destination database product used, SQL Server, PostgreSQL, there might be things missing from the source database. i.e, Default Values, Identity Columns created as ordinary Numeric columns ..etc. In that case, it had better if the destination tables can be manually created.

There might be more considerations that should be taken care of, but these are what I came across.

Hope this helps!