How to copy data from azure data lake to azure SQL database
Copying data from Azure Data Lake Storage (ADLS) to an Azure SQL Database can be achieved using various methods, but one common approach is to use Azure Data Factory. Azure Data Factory is a cloud-based data integration service that allows you to create, schedule, and manage data pipelines to move data between different supported sources and destinations.
Step 1: Set Up Azure Resources
Step 1.1: Azure Data Lake Storage: Make sure you have an Azure Data Lake Storage account where your source data is stored.
Step 1.2: Azure SQL Database: Have an Azure SQL Database created to serve as your destination.
Step 1.3: Azure Data Factory: Create an Azure Data Factory instance. This will be used to define and manage your data pipeline.
Step 2: Create Linked Services
Step 2.1: Linked Service for ADLS: In Azure Data Factory, create a linked service for Azure Data Lake Storage. Provide the necessary authentication and connection details to access your ADLS account.
· Please follow the below steps on how to create a linked services in azure data factory.
· Under manage tab click on new option and search for azure data Lake Gen2.
· Fill all the required details or select from the dropdown. Finally test the connection and click on create.
· You should see the below page after creating your linked service in azure data factory.
Step 2.2: Linked Service for Azure SQL Database: Create a linked service for your Azure SQL Database. Provide the connection details, including server name, database name, and authentication.
· Please follow the below steps on how to create a linked services in azure data factory for SQL database.
· Select the SQL database and click on continue.
Fill the necessary details in as shown in the below image and click on create (Please note that the SQL username and password is the one you have created while deploying the azure SQL database).
ERROR:
In case while you are facing the below error while testing the connection with Azure SQL database connection as shown below .
Cannot connect to SQL Database. Please contact SQL server team for further support. Server: ‘test2122.database.windows.net’, Database: ‘testaehj’, User: ‘testing’. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.
Cannot open server ‘kjdsfksdf’ requested by the login. Client with IP address ‘sdasdasd’is not allowed to access the server. To enable access, use the Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect., SqlErrorNumber=40615,Class=14,State=1,
Click on set firewall rule to set the IP address.
GO to your Azure SQL database and make sure below option is enabled.
STEP 3.Click the three dots and select create pipeline.
STEP 4. Search for copy activity in azure data factory or you can also find the copy activity under move and transform section.
STEP 5. You need to attach your source dataset under source option and attach sink dataset under sink option.
STEP 7. Click on debug option to test whether your copy activity pipeline working fine.
STEP 8. If everything works fine click on publish to save your changes.
STEP 9. Check wheatear the data reflects in Azure SQL database.
Also You can find my blog on how to create linked services and datasets ->>
If you face any errors or need any help on azure data factory please feel free to reach out to me on LinkedIn : https://www.linkedin.com/in/madhu-narayana-varma-m-08216919a/
