Creating sql database table from csv schema file using Azure data factory

Question

Hi,

i have requirement, we will get schema files in csv(see below highlighted), there were around 100s of files.

here is sample schema csv file

TableName,columnae,LENGTH,vartype

Customer,custnumber,8,NUM

Customer,custname,100,char

Customer,custtype,12,char

Customer,custordernumber,20,NUM

using Azure data factory, i have to create Customer table with columns(custnumber,custname,custtype,custordernumber) with data types in the sql server table . i can't hard code the mapping since i have hundreds of file.

File doesn't have any data, it has only schema.

Can someone help me how to import this to sqlserver?

Thank you,

Anil

Answer 1

Hello @Anil

To create a SQL Server table from a CSV schema file using Azure Data Factory, you can follow these 8 steps:

1. Create a pipeline in Azure Data Factory.
2. Add a Copy Data activity to the pipeline.
3. In the Source tab of the Copy Data activity, select the CSV schema file as the source dataset.
4. In the Sink tab of the Copy Data activity, select the SQL Server table as the sink dataset.
5. In the Mapping tab of the Copy Data activity, select the "Import schema" option.
6. Click on the "Import schema" button and select the CSV schema file.
7. Map the columns from the CSV schema file to the columns in the SQL Server table.
8. Save and run the pipeline. This will create the SQL Server table with the columns and data types specified in the CSV schema file. Note that you will need to create the SQL Server table beforehand and make sure that the column names and data types match the ones specified in the CSV schema file.

---

I hope that this response has addressed your query and helped you overcome your challenges. If so, please mark this response as Answered. This will not only acknowledge our efforts, but also assist other community members who may be looking for similar solutions.

Answer 2

Hi @Anil ,

Thankyou for using Microsoft Q&A platform and thanks for posting your query here.

It seems you want to create tables in SQL using the schema present in CSV metadata file for multiple tables where you can't hardcode the schema.

ADF might not directly help here, you might need to write a python script to achieve this scenario and use Azure functions to run the code using ADF.

Here is the sample code you could try to use and generate tables based on the schemas for each of the tables:

createsqltablesusingschemafiles.txt

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou