Problem Statement
Sravan works as a Azure SQL DBA with UniversalCollege Group. Developers at UniversalCollege wants Sravan to upload the Student data present in a text file on an Azure BLOB Storage onto an Azure SQL Managed Instance named universalcollege.database.windows.net.
Solution
Sravan decides to perform this Task using Azure Data Factory. Azure Data Factory is a very powerful ETL tool that allows users to create ETL solutions in the Cloud. In the next couple of days, we are going to explore Azure Data Factory in depth.
At present, the text file is uploaded on an Azure Blob Storage inside a Container as shown below:
It contains the following data: sid,sname,gender.
1,Sravan K,Male
2,Rahul,Male
3,Niharika,Female
4,Amandeep,Female
This data needs to be uploaded inside the Student table hosted on the college database which in turn is hosted on a PaaS Instance named universalcollege.database.windows.net.
Now let’s logon to the Azure Portal; portal.azure.com and try to create a Data Factory, go to All services->Integration->Data Factories as shown in the screen capture below:
Click on + Create as shown in the screen capture below:
Enter the required information as shown in the Screen Capture below and then click on Review+create button.
Under Git Configuration; ensure that you click on the CheckBox named Configure Git later as shown below:
Once all the Validations are passed; kindly click on the Create button as shown in the screen capture below:
Kindly click on UniversalCollege Data Factory as shown below:
Click on Author & Monitor as shown in the screen capture below:
This opens the Azure Data Factory home page as shown below:
Click on Copy data as shown below:
Please give an Appropriate Task Name and then click on the Next> button as shown in the screen capture below:
In the Source Data Store; from the DropDown; kindly select Azure Blob Storage and then Click on + Create New Connection as shown in the screen capture below:
In the New Linked Service; select Azure BLOB Storage as shown in the screen capture below:
Enter the required information and then click on Test Connection as shown in the screen capture below:
If the Test Connection is successful; it means that the source is configured successfully; click on the Create button.
Click on the Next> button as shown below:
Choose the required file and then click on the Next> button as shown in the screen capture below:
Click on the Next> button as shown in the screen capture below:
In the Destination Data Store; kindly select Azure SQL Database from the drop down and then click on +Create New Connection as shown in the screen capture below:
In New Linked Service; select Azure SQL Database and then click on Continue button as shown in the screen capture below:
Enter the required information and then click on Test Connection as shown in the screen capture below:
If Test Connection is successful; then it means that we are good to proceed ahead.
Click on the Create button as shown below:
Click on the Next> button as shown below:
Click on the Next> button as shown below:
Click on the Next> button as shown below:
Click on the Next> button as shown below:
Click on the Next> button as shown below:
Click on the Finish button as shown below:
The below screen capture confirms that the data is inserted successfully into the desired table on the destination PaaS database.
I hope you found something interesting to learn.
History
- 10th June, 2021: Initial version
