Data migration from Legacy system database to OutSystems Application database

Sankhadeep Rakshit

Question

Dear members,
We are modernizing one of our key enterprise solutions by completely redesigning and rebuilding it in OutSystems. In this project we are trying to migrate the current data from a legacy systems MySQL database to the newly built OutSystems Application database. In our case, not all the data models and table structures of the legacy system database is same as the data models and the table structures of the OutSystems application database. As I have been figuring out so far, forge component https://www.outsystems.com/forge/component-overview/2812/infosistema-dmm looks like a solution to do the data migration. However, I'm not sure if it can support our scenario. Also, we have thought of taking an approach of bootstrapping the data from excel upload. However I feel that's not very efficient given the legacy system has almost 1.5K+ tables.

Really appreciate your opinion on how best to handle this data migration situation. Is there any OutSystems way to handle this scenario?

12 Oct 2020

Daniël Kuhlmann

MVP

Hi Sankadeep,

I would suggest the following approach.

Analyze which tables need to be migrated and what transformation is required.
Create staging tables in your MySQL database, that are as much as identical to the entities in OutSystems
Create SQL scripts to transform and load the legacy data into the staging tables.
Test, and repeat step 1,2,3 until you are happy with the data in the staging tables.
Now you have some options:
1. Use a tool like Infosystema DDM.
2. Create an external database connection and load the data using SQL Bulk insert.

Regards,

Daniel

12 Oct 2020

1 reply

12 Oct 2020

Show thread

Hide thread

Sankhadeep Rakshit

Daniël Kuhlmann wrote:

Hi Sankadeep,

I would suggest the following approach.

Analyze which tables need to be migrated and what transformation is required.
Create staging tables in your MySQL database, that are as much as identical to the entities in OutSystems
Create SQL scripts to transform and load the legacy data into the staging tables.
Test, and repeat step 1,2,3 until you are happy with the data in the staging tables.
Now you have some options:
1. Use a tool like Infosystema DDM.
2. Create an external database connection and load the data using SQL Bulk insert.

Regards,

Daniel

Thanks a lot Daniel for the suggestion. For step 2, how do we make the OutSystems table structure known for creating the staging tables? Is exporting OutSystems tables in excel format the way to do it? Or is there any easy way to expose/export these data entities and data models so that the MySQL staging tables can be built accordingly?

12 Oct 2020

Ivo Gonçalves

Hi Sankhadeep,

An easy way to achieve what you want is using MySQL Workbench. Check this article, it gives you all the details need for step #2. Workbench can also help you with #3.

12 Oct 2020

1 reply

13 Oct 2020

Show thread

Hide thread

Sankhadeep Rakshit

Ivo Gonçalves wrote:

Hi Sankhadeep,

An easy way to achieve what you want is using MySQL Workbench. Check this article, it gives you all the details need for step #2. Workbench can also help you with #3.

Thanks Ivo Gonçalves. But I was actually looking for an easy way to export these data entities from my OutSystems application and make them available in the MySQL workbench. Is there any easy way to pull out this data model/attributes from the OutSystems DB?

13 Oct 2020

Ivo Gonçalves

Hi Sankhadeep,

Let me confirm I fully understand your request:

You have your Outsystems data model created;
Your Outsystems data model is different than your legacy system's data model;
Your ultimate goal is to move some tables from your MySQL legacy system to Outsystems;
At this point you want to replicate your Outsystems' model into MySQL.

Is that correct?

In my previous post I assumed your Outsystems database was running on MS SQL Server. If your are running on top of Oracle you cannot use MySQL workbench since it is not supported. So, let me break it down.

MS SQL Server

If your target database is running on top of MS SQL Server I recommend you to use MySQL Workbench. The documentation has a very comprehensive how-to. You can find there even the data type mappings between the two RDBMS. With MySQL workbench, as shown in the documentation, you can generate the DDL from your source database into MySQL format. In the end you'll be able to generate a mirrored Outsystems model into your MySQL database.

Oracle

For Oracle you can use migration workbench available with SQL Developer. I could not find a how-to as for MySQL workbench but the mechanics are the same.

NOTE: You may have better options, the tools I described are just one of the options. I have used both in the past on a few projects and both are really powerful.

13 Oct 2020

1 reply

14 Oct 2020

Show thread

Hide thread

Sankhadeep Rakshit

Ivo Gonçalves wrote:

Hi Sankhadeep,

Let me confirm I fully understand your request:

You have your Outsystems data model created;
Your Outsystems data model is different than your legacy system's data model;
Your ultimate goal is to move some tables from your MySQL legacy system to Outsystems;
At this point you want to replicate your Outsystems' model into MySQL.

Is that correct?

MS SQL Server

Oracle

For Oracle you can use migration workbench available with SQL Developer. I could not find a how-to as for MySQL workbench but the mechanics are the same.

NOTE: You may have better options, the tools I described are just one of the options. I have used both in the past on a few projects and both are really powerful.

Thanks a lot Ivo. This is really helpful.

14 Oct 2020

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.

See the full guidelines