question

SanchetDighe avatar image
0 Votes"
SanchetDighe asked ShaikMaheer-MSFT commented

ADF Copy Activity not converting empty string to null for int32 target column

I am using Copy Activity to transfer data from CSV files on Azure Data Lake Gen2 to Synapse table using partition discovery and Bulk Insert mode. Polybase and Copy Command do not work with partition discovery, hence the Bulk Insert mode.

One of the fields in the target table is integer data-type that allows nulls (fieldname int null), whereas some of the records in the source files have empty value ("") for this field. The Copy Activity failure reported is:

Exception occurred when converting value '' for column name 'fieldname' from type 'String' (precision:, scale:) to type 'Int32' (precision:10, scale:255). Additional info: Input string was not in a correct format.

I could skip these records from the load for getting the copy to complete, and then have a data flow with the type conversion to load these records. Here is a sample record with few masked values that gets skipped; the field in question is the last field CanSerNbr :

"TrnId","Module","Event","Operator","EventDtTm","Results","PresNum","Nmdcode","Qty","SerialNbr","Notes","SubRoutine","ComputerName","ExecCode","CanSerNbr" "205242015","Ord","Assign PQ Bank","","2021-08-14 12:05:21.600","","02.NNNNNNNN","","0","0","Pres assigned to neighborhood id 1"," UpdtOrdrNeighborhoodID ","XYZREPLACEMENT-","X ",""

However, instead of this 2-step approach, I wonder why Copy Activity should not be able to interpret a zero-length string and map it to null for the target nullable field. Possibly a bug in the Copy Activity, maybe with Bulk Insert mode only? Or, is there a property setting for the Copy Activity to deal with such type-conversion issues? Please suggest.


azure-data-factoryazure-synapse-analytics
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @SanchetDighe ,

Following up to see if below answer helps you? If yes, please consider accepting answer by clicking on 130329-acceptansw.jpg button. Please let us know if any further queries. Thank you.

0 Votes 0 ·
acceptansw.jpg (2.4 KiB)

1 Answer

ShaikMaheer-MSFT avatar image
0 Votes"
ShaikMaheer-MSFT answered ShaikMaheer-MSFT commented

Hi @SanchetDighe ,

Welcome to Microsoft Q&A Platform. Thank you posting query here.

Unfortunately for your case you need to go with Data flows only. Because copy activity in Azure data factory only allow us to perform data movement as is. Handling type conversion from string to int directly is not available in copy activity. To do any kind of checks or transformation we should consider data flows.

Data flows allow data engineers to develop data transformation logic without writing code. You can consider derived column transformation for your case to check if value is empty or not and pass value accordingly.

Hope this will help. Please let us know if any further queries. Thank you.


  • Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.

  • Want a reminder to come back and check responses? Here is how to subscribe to a notification.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for your reply, Shaik. It would have been nice if handling of empty strings would have been considered as a special case scenario in the Copy Data Activity, for I now have to forego the advantages of using BULK INSERT, which leads me to the next question: Is it possible to have BULK INSERT with data-flow?

0 Votes 0 ·

Hi @SanchetDighe ,

Thank you for marking it as accepted answer. Yes, data-flows are capable to perform bulk insert. Please feel free to open new thread if any queries on it.
Please Note, I have recorded your feedback. I will forward this to Product team to consider for future releases. Thank you.

0 Votes 0 ·