CODEWITHSUNDEEP
azuremappingazure-data-factory
Dhananjay Singh
Dhananjay Singh

Reputation: 11

How to use data flow activity and derived column transformation to do column mapping in ADF and load it in SQL Server table

I have fixed length .DAT files in a ftp server and I need to use data flow activity and derived column transformation to do column mapping using ADF to be able to transform data before loading into a SQL Server table.

  1. There is no delimiter in the file.
  2. The target table in SQL server has columns same as mapping photo below.
  3. Need to do column mapping on dataset created from fixed length .DAT file for each line and land it in target SQL server table.

I need a solution where without any delimiter specified I am able to convert .DAT file to a dataset and use derived column transformation and then take substring for every column to do column mapping in a similar way as shown below:[![Column Mapping][1]][1]

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

Any help/snapshots are appreciate. Thank you.

Upvotes: 0

Views: 781

Answers (1)

Joel Cochran
Joel Cochran

Reputation: 7758

Fixed width format is not natively supported as a Dataset. In order to process it, you'll need to parse the rows first. Here is a rough outline:

  1. Create a Dataset with no schema and no delimiter. This will read in each row as a single column named "Prop_0".

enter image description here

  1. Use Derived Column and substring to parse the columns out of the row. When you name the columns, name them the same as the target SQL columns.

enter image description here

  1. Perform any additional operations like trims (recommended) or type conversions.

  2. Write the data to your Sink.

If the data file is really large, you might want to save the data to an intermediate location, like Parquet files, and then perform the write in another Dataflow. This should make the write to SQL perform better because it will be able to parallelize. It also gives you the opportunity to validate the converted data prior to writing it to SQL.

Upvotes: 1

Related Questions