move || colum delimited file to Synapse using ADF

Question

move || colum delimited file to Synapse using ADF

AzeemK 516

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

MarkKromer-MSFT 5,221 Reputation points Microsoft Employee Moderator

2021-08-12T06:48:17.59+00:00

In the dataset definition, click "Edit" under column delimiter. Enter || for the delimiter.
Nandan Hegde 35,911 Reputation points MVP Volunteer Moderator

2021-08-13T08:51:07.267+00:00

Hey @MarkKromer-MSFT ,
Dual delimiter is not supported in ADF . Though you would be able to preview the data but not use that dataset:
Mark Kromer MSFT 1,146 Reputation points

2021-08-13T09:00:31.843+00:00

That's because you're using Copy. Use data flow where multi-character delimiter is supported.

1 answer

Your answer

MarkKromer-MSFT 5,221 Reputation points Microsoft Employee Moderator

2021-08-12T06:48:17.59+00:00

In the dataset definition, click "Edit" under column delimiter. Enter || for the delimiter.
Nandan Hegde 35,911 Reputation points MVP Volunteer Moderator

2021-08-13T08:51:07.267+00:00

Hey @MarkKromer-MSFT ,
Dual delimiter is not supported in ADF . Though you would be able to preview the data but not use that dataset:
Mark Kromer MSFT 1,146 Reputation points

2021-08-13T09:00:31.843+00:00

That's because you're using Copy. Use data flow where multi-character delimiter is supported.

Answer 1

Nandan Hegde 35,911 MVP Volunteer Moderator

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

AzeemK 516 Reputation points

2021-08-13T06:27:50.067+00:00

how do I do binary type in data set source and sink??
Nandan Hegde 35,911 Reputation points MVP Volunteer Moderator

2021-08-13T08:56:09.003+00:00

You need to create a new dataset of type File (my assumption is File was also your original source as On prem) and then in format you need to select Binary :

And make sure your sink dataset is blob storage with binary format.

For more details :
Dataset: choose the format you want. To copy file as-is, select "Binary" type.
https://learn.microsoft.com/en-us/azure/data-factory/format-binary
Mark Kromer MSFT 1,146 Reputation points

2021-08-13T09:00:56.723+00:00

Just use data flow instead. Copy doesn't support multi-char delimiter.

Share via

move || colum delimited file to Synapse using ADF

1 answer

Your answer