My aim was to recursively copy ten DB tables from an on-prem SQL Database to the Azure cloud. Key technologies involved are SQL server, Host integration (self-hosted) Run Time, Data pipelines using Azure Data Factory and Azure cloud storage using ADLS Gen 2.
I will share three problems I faced and resolutions for the same. The traditional documentation available were of less help.
In few cases, the ERROR messages given were of less help and were not connected to the ERROR.
Description | Resolution | Technology | |
Error 1 (a) | While configuring the integration runtime, the authorization with the Azure provided key was failing | Checked the System Clock - The incorrect on-prem system time need to be correctly set | MS Host integration runtime |
Error 2(b) | Copying few Tables succeeded while other copies failed | Added the missing @ in SQL script syntax - We are using SQL scripts to automate. There were some syntax errors. | Azure Data Factory, SQL query feature for specifying Sink |
Error 3 (b) | The migration process failed throwing error | Corrected the SQL script syntax error- missing space after FROM @{concat('SELECT * FROM ', item().SchemaName, '.', item().TableName)} | Building Copy Data pipeline in Azure Data Factory |
(a). The Integration (self-hosted) Run Time node has encountered an error during registration. The integration (Self-hosted) node failed to connect to the cloud service due to network connectivity issues. Check network connectivity issues.
(b). Failure happened on 'Source' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Incorrect syntax near 'FROMSalesLT'.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Incorrect syntax near 'FROMSalesLT'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=102,Class=15,ErrorCode=-2146232060,State=1,Errors=[{Class=15,Number=102,State=1,Message=Incorrect syntax near 'FROMSalesLT'.,},],'
(c).ErrorCode=AdlsGen2OperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ADLS Gen2 operation failed for: Operation returned an invalid status code 'Conflict'. Account: 'studentsn020sa3'. FileSystem: 'bronze'. Path: 'SalesLT/item().TableName/item().TableName.parquet'. ErrorCode: 'LeaseNotPresentWithLeaseOperation'. Message: 'The lease ID is not present with the specified lease operation.'. RequestId: '9edb3332-901f-001a-2b6a-8026cd000000'. TimeStamp: 'Wed, 27 Mar 2024 17:17:38 GMT'..,Source=Microsoft.DataTransfer.ClientLibrary,''Type=Microsoft.Azure.Storage.Data.Models.ErrorSchemaException,Message=Operation returned an invalid status code 'Conflict',Source=Microsoft.DataTransfer.ClientLibrary,'