CODEWITHSUNDEEP
azure-data-factory
Being Real
Being Real

Reputation: 83

Unable to skip a line from Excel while doing copy activity in azure data factory

I have a excel Column as a source in Copy Activity. I need to map the columns from source to sink (MS SQL Server).

I have one constraint, whenever I have a blank row in the excel source I should Stop the copy Activity.

I want to copy all the column data before the blank row, and ignore what ever after the blank row.

For example if I have this dataset:

A B
ABC DEF
HIJ KLM
NOP QRS

Then the first two rows should be imported and the last two rows should not be imported.

What should I do?

Upvotes: 1

Views: 989

Answers (1)

Ed Elliott
Ed Elliott

Reputation: 6856

In an ADF pipeline there are limited options for working with the contents of datasets (you can add cols, remove cols, etc) but modifying the rows themselves is hard. You can do it with dataflows but as your sink is mssqlserver then you can use row_number() to find any empty rows and keep the rows up to the blank row.

The process is:

  • Create a staging table which is the same as the target table but includes a row column that is defined row INT IDENTITY(1,1) NOT NULL
  • Truncate the staging table
  • Write the data to a staging table
  • From the staging table write to the main table using the insert query:
SELECT * FROM staging_table 
  WHERE rowid < 
    (SELECT 
       COALESCE(MIN(rowid), (SELECT MAX(rowid)+1 FROM staging_table)) FROM staging_table WHERE a IS NULL)

Upvotes: 1

Related Questions