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

Question

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.

Answer 1

Hi @Anonymous ,

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.

Answer 2

iif(toString(length(rtrim(Column))) != "0", Column,toString(null()))

Here is the answer to this. I have it working in my ADF. Some columns records are sent in a flat file with empty spaces. This code will trim white spaces, then count the characters and IF 0, then change to NULL. I spent a few days on this working several different approaches and this is the only one that works. SSIS has an option 'Retain NULLs' ADF should add that feature to Source Files so it works like SSIS did. This code will get you what you need.