Quickstart database migration using Talend

quarks picture quarks · Jul 27, 2012 · Viewed 10.8k times · Source

I have this requirement to migrate a live SQL server 2008 to MySQL server 5, I have tried the MySQL migration toolkit but its throwing lots of migration errors. Someone here in Stackoverflow suggested that I use Talend. I installed Talend Open Studio for Data Integration but its quite a general purpose tool that there's no quickstart to do database migration like what I really needed.

Where can I find a quickstart guide to use Talend for such job?

Answer

John Mayor picture John Mayor · Oct 16, 2012

Talend is quite generic data integration tool, but you should get the job done by following these steps (which actually apply to any database-types: PostgreSQL, Oracle, etc.):

  1. Create a new database connection in Repository-panel, to the Metadata-"Db Connections"-node. Name it and select "DB Type" as "Microsoft SQL Server" (or whatever is your source database) and give the connection details.
  2. Create another new database connection, but this time as "MySQL" (or whatever is your target database).
  3. Create a new job to "Job designs" (in Repository).
  4. Expand the "SQL Server"-DB-connection from the Metadata, and drag the desired table (under "Table schemas") to the work-area of the job AND select tMSSqlInput.
  5. Drag the MySQL-DB-connection from the Metadata to the work-area AND select tMysqlOutput. Define the target table.
  6. Click Palette and drag tMap component to the work-area between the two previous components.
  7. Connect tMSSqlInput to tMap and tMap to tMysqlOutput with arrows. You do that by right-clicking the tMSSqlInput, selecting Row-Main and dragging the arrow to the tMap. Same from tMap to tMysqlOutput.
  8. Double-click the tMap, select all columns from the left and drag them to the right. The tMap component allows you to make data transformations. It might be that you wouldn't even need to use the tMap between the database-components. But it is good to have just in case.
  9. Run the job, and troubleshoot as necessary. If something goes wonky, restart Talend Studio or just close and open the job, in many occasions it helps.