move || colum delimited file to Synapse using ADF

Question

I want move || column delimited file from on prem to Synapse using ADF V2 , I am keep getting ADF error that null values are not allowed in my Source data set What's the best way to ingest || column delimited file using ADF

sample:
phone_id||user_id||customer_id||area_code||phone_number||extension||phone_type

100||1||1||512||5551234||23||Home

Answer 1

Hey,
Since your file delimiter is || and ADF till date supports only single character for column delimiter,

for you to consume the file into synapse ; I would suggest the below steps:

1) Create a blob storage
2) Copy the file from On prem into blob storage as is ( use binary type in dataset as source and sink)
3) In synapse create external table and all its necessary parameters :
https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables?tabs=hadoop
You can specify || as the field delimiter in synapse which is what we require
4) Create a Stored procedure that would load data from external table into your actual table
5) Call that SP in ADF after file copy activity