Copy Data from Blob to SQL via Azure data factory

0
votes

I have two sample files in blob as sample1.csv and sample2.csv as below

data sample

SQL table name sample2, with column Name,id,last name,amount

Created a ADF flow without schema, it results as below

preview data

source settings are allow schema drift checked. sink setting are auto mapping turned on. allow insert checked. table action none.

I have also tried setting a define schema in dataset, its result are same.

any help here?

my expected outcome would be data in sample1 will inserted null into the column "last name"

2
blobazure-data-factorydataflowazure-data-flowazure-data-factory-pipeline
Hi Awo Ningan, If my answer is helpful for you, can you please accept it as answer( click on the check mark beside the answer to toggle it from greyed out to filled in.)? This can be beneficial to other community members. Thank you. – Leon Yue

2 Answers

0
votes

If I understand correctly, you said: "my expected outcome would be data in sample1 will inserted null into the column last name", you only need to add a derived column to you sample1.csv file.

You could follow my steps:

  1. I create a sample1.csv file in Blob Storage and a sample2 table in my SQL database: enter image description here enter image description here

  2. Using DerivedColumn to create new column last name with null value:

expression: toString(null()) enter image description here

  1. Sink settings: enter image description here

  2. Run the pipeline and check the data in table: enter image description here

Hope this helps.

0
votes

You cannot mix schemas in the same source in the same data flow execution.

Schema Drift will handle changes to the schema on an execution-per-execution basis.

But if you are reading multiple different schemas from a folder, you will get non-deterministic results.

Instead, if you loop through those files in a pipeline ForEach one-by-one, data flow will be able to handle the evolving schema.