CODEWITHSUNDEEP
blobdataflowazure-data-factory
Awo Ningan
Awo Ningan

Reputation: 3

Copy Data from Blob to SQL via Azure data factory

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"

Upvotes: 0

Views: 598

Answers (2)

Leon Yue
Leon Yue

Reputation: 16431

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.

Upvotes: 0

Mark Kromer MSFT
Mark Kromer MSFT

Reputation: 3838

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.

Upvotes: 0

Related Questions