Step 1: Login to Azure portal and search for storage accounts
**First we need to create a Blob file storage before we link it to our SQL database.
Step 2 : Create a file storage **
Select your subscription
Create a resource group or add from an existing one you have created before.
Create a storage account name
Select a region and ensure performance is set at “Standard”
Select Redundancy to Geo-redundant storage then click “Create”
_Leave other tabs as default, the main goal to create a file storage that can be sent to SQL database.
_
Once you click on resources, the below page shows, click on containers to create a file storage.
Step 3: Create a container to store our files such as excel files, csv or other files that can be used to store data.
Select “container access level” and click create.
Step 4: click on the container and upload any csv file or any file used to create, store dataset from your computer, then click “upload”.
**
Now we have BLOB storage container with a CSV file in it. Now let’s create an SQL database
**
Step 5 : Go to the Home page of the portal and search “SQL database” and click Create
Select Subscription and resource group( if you have created one before) in this case you can select the same resource group used to create the BLOB storage for this SQL database to ensure you have all resources for the activity in the same resource group.
Create a Database name and select a server or create a server
Ensure the storage redundancy is “geo-redundant storage” similar to the BLOB storage, leave other tabs has default and click “create”
Once your SQL Database has been deployed, go to the resource page of the database. And click “Query editor.
Enter your login details you created while creating your SQL database.
**_
Now we are going to create table heads of CSV file in the blob storage into this tables folder in the database for us to link the dataset later.
_**
**
Now save the query as “dataset” and congratulations, you have create a SQL database
**
**
Congratulations you have successfully created A Blob storage and A SQL database.
**
**
_Now lets link the storage file to the Database, _
**
Step 6: Go back home, and create “Data Factory” and create.
Select subscription, resource group such as the one for the data storage and SQL database.
Once you create and deploy, click on the resource for it and “launch studio”
This will open up a new-tab on your browser
**_
Now we need to provide a link service that will enable us link the Blob storage to our SQL database
_**
_Under Data store tab, search for “Azure Blob Storage” _
As usual, follow the details of subscriptions and under the storage account select the name of the blob storage file we created in azure earlier.
once it is done, click “create”
Now let’s repeat the process for SQL database,
Select the required information has shown in the screenshot below, and click create.
**_
Now we have created the linking services for both the blob storage and Sql database, let’s go back to the pipeline2
_**
_From the above select “Delimited text” which represents “CSV” file format stored in the blob storage in azure portal. _
_
select “dataset” and click ok _
**_
Let’s create same dataset for the SQL Table
_**
**_
Now Go back to “pipeline2” and select “Move and transform”
_**
_Move and drag “copy data to the open space next to it _
_Click on “Source” – Source is the source of the data which we want to connect the data from which is the “Blob storage”. _
**_
Now we click on “Sink”, sink is the sql table headers we created with the sql syntax called “orders”
_**
**_
Now click on “Mapping” – to map both files together
_**
Confirm that dataset types from the source (Blob storage) is the same to destination (Sql database)
After that, confirm the tables “dataset and xsqltable1” are shown above then click “debug”.
Once the “Debug is complete”, and its succeeded as shown above CONGRATULATIONS, YOU HAVE SUCCESSFULLY LINKED YOUR BLOB STORAGE TO YOUR SQL DATABASE.
**_
Now go back to azure portal, check the SQL database query d.bo.dataset
_**
**_
Let’s compare the table previously
Before
_**
**_
After
_**
**_
CONGRATULATIONS, YOU HAVE SUCCESSFULLY LOADED YOUR BLOB CSV DATASET TO THE SQL DATABAS
_**
Top comments (1)
Good one Fagbenro 👍