How can I load a fixed length (no delimiter) .DAT file to a SQL Server table using Azure Data Factory?

Question

I have fixed length .DAT files in a ftp server and I need to bring them to ADLS using ADF in a .TXT format to be able to transform data in ADF before loading into a SQL Server table.

So far, I could copy the table as is in ADLS as a .DAT file. When I try to create a dataset in a text format in ADF it does not allow me copy the file without specifying the delimiter.

I need a solution where without any delimiter specified I am able to convert .DAT file to a .TXT file and store it in ADLS or be able to use the dataset without copying files to ADLS to transform dataset contents to do column mapping.

I would need to update the dataset daily because new file would be added everyday to the ftp server.

Any help is appreciated. Thank you.

I tried the following: Create a dataset in ADF by specifying "NO DELIMITER" and was able to preview data as shown .This however does not allow me to copy dataset to ADLS because copy requires to define a delimiter.

Can I transform this dataset in the pipeline without copying to ADLS? Next step is to do mapping to load data from this dataset into a SQL Server table:

Answer 1

Create a dataset in ADF by specifying "NO DELIMITER" and was able to preview data. But this does not allow me to copy dataset to ADLS because copy requires to define a delimiter.

• When the column delimiter is defined as empty string, which means no delimiter, the whole line is taken as a single column.
• Currently, column delimiter as empty string is only supported for mapping data flow but not Copy activity.

So, if you are using copy activity with no delimiter you will get this error in validation as below.

To avoid this the work around is to use Data Flow activity to copy file with no delimiter from source to sink.

I reproduced with No delimiter in dataflow.

• Dataset:

• Dataflow using same file.

• Pipeline running Successfully.