move || colum delimited file to Synapse using ADF

AzeemK 516 Reputation points
2021-08-12T02:15:04.493+00:00

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

122533-pipedelimited.jpg

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
    2021-08-12T03:17:54.677+00:00

    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