move || colum delimited file to Synapse using ADF

AzeemK 516 Reputation points

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




Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,758 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Nandan Hegde 29,906 Reputation points MVP

    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 :
    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