I am trying to import csv files from blob storage into a sql server database using Azure Data Factory. These csv files do not have a consistent format. Not all csv files have the same number of columns.
How can I check to see if a column exists in the csv file and if it doesn't just insert a NULL value for that column into the SQL Server database?
I am assuming you are using ADF v2.
You can see the column presence by clicking on the ADF activity and then going to Mapping tab, it will enable you to map the columns present in CSV file with the input dataset and from there you will be able to know if there are any missing columns as the mapping would fall short for columns from input file.
If you don't see any mapping present already, then you need to click on Import Schemas to map the file columns with input dataset.
To insert a NULL value in database, you can write a stored procedure and call that via ADF or you can keep the database column with a default value as NULL, so if the data is not present in the file for that column, then automatically it will take value as NULL.
Let me know if you have any further questions.
Thanks and Regards,
Pratik Somaiya
