sql server – Migrate Mysql to Azure Sqlserver db

I read some articles and also tried SSMA, linked server and other things, but not help. I have one client’s mysql Db (later we need other too) in my local pc and have Azure DB which used for SAAS-based application (i.e. all client’s data is store in Azure DB with bifurcate from ClientId column in each table. Good thing is, before each migration process, we know what is cilentid.

What I tried,

  • SSMA, its simple and very usefull, but not have logical things
  • Linked server, some one suggest to migrate first the mysql to local sql and then migrate to Azure, it is faster. This is good, only that based on Patient-id of patient table have reference of other patient table in AzureDB, so again required the other logic for that and not help.

I want to migrate the patient table and another reference patient table of MySql (local PC) to Azure sqlserver.

Details

  1. In MySql (installed in my local pc) which has patient table which has PatNum (auto-incremented or PK) and other columns, the same data need to migrate in Azure DB.
  2. Azure DB has tblPatientMaster have PatientId( auto-increment id or PK ), PatientNum (PK of Mysql above table). Also change some columns name, add some columns (like Recordstatus, CreatedBy, CreatedDate etc)
  3. Want to loop each record and insert a record with the below condition

| MySql | Azure Sqlserver |
| ——– | ————– |
| PatNum | PatientNum |
| | ClientId (this is already known and fix and while insert, we pass via parameter) |
| PatStatus | PatientStatus|
| PatStatus=2 or 3 | RecordStatus = 2|
| other columns | others columns same as Mysql |
4. After patient master insert and get a patient-id of Azure Sqlserver, need to add a record in patient reference table like patient allergy (PatAllergy) of Mysql DB where replace the PatNum of Mysql with PatientId of sqlserver and insert into tblPatientAllergy of Azure DB.

I have some question as

  1. Is there any option other than SSIS for this type of migration.
  2. If no and only SSIS, then anybody gives steps or hint that how to loop each record of my SQL and while loop call Sqlserver procedure (I think I used stored procedure at Azure side) and do the logical thing and then insert in the first PatientMaster.
  3. In SSIS, after insert into Patient Master, how to fetch and insert MySQL to Azure Sqlserver for that patnum.