Author: Ankit Gupta (Associate Data Engineer), Hitesh Srivastava (Associate Data Engineer)
This article describes how to migrate selected tables with different schema from one Azure SQL database to another Azure SQL database through Azure Data Factory.
Prerequisites:
- Two Azure SQL databases (Source with a sample database and destination)
- Azure Data factory
- SSMS
Step 1: Connect SQL server to SSMS and Enter the server name, login, and password.
Step 2: Now Create One table in the source (Sample Azure SQL database) with schema name and table name to contain specific tables which we want to migrate.
Step 3: Insert the table name into Table1 which we want to migrate.
Step 4: (i) Then open Azure Data Factory and create a new pipeline and Drag one lookup Activity into it for getting the list of tables.
(ii) Query to be used to get list all the tables and schema names in lookup;
SELECT '[' + schem + '].[' + name + ']' AS mytable, schem AS myschema, name AS myname FROM dbo.table1;
(iii) Disable the first row only checkbox and then Preview the data where the list of tables will show.
(iv) Then Validate and debug the lookup.
Step 5: Then drag “ForEach” to loop through all my tables names.
Add the following code: @activity(‘Get-Tables’).output.value
Step 6: Add one copy Activity inside ForEach and add the source dataset.
Query to be used : SELECT * FROM @{item().mytable}
Step 7: Then go to the Sink part add sink data set and create two parameters.
Step 8: Now go to connection and add a dataset. Configure the dataset to use these new parameters.
Step 9: Then go back to copy activity and Assign the values from the ForEach task to the parameters. Save and publish.
Hence, the migration of the selected table with different schema is successfully completed.
