Azure Data Factory dynamic excel data flow

my scenario is the following: I created a pipeline that work on an Excel file and is triggered by the upload of the Excel in a Blob Storage. The filename is passed as a parameter to the pipeline from the trigger. I then apply a lookup and then a forEach where I copy each row of the excel to different SQL tables. Now I have two problems:

1. Sometimes some rows are deleted manually from the excel before uploading, but even if they are empty the Lookup activity reads them as rows full of null, while I would like to skip them. I tried to filter them out with a data flow, but I do not understand how to configure it because nothing appears in the dropdown menus and I don't know what to write as a dynamic content and how to write it. For example, I tried to use a Select to rename the columns: but then in the filter activity nothing appears in the Input schema section so I don't know how to make it work

2. In the last part of my pipeline, when I copy data to SQL table I do it with a json mapping:

   @json(' { "type": "TabularTranslator", "mappings": [ { "source": { "name": "sampleId" }, "sink": { "name": "sample_id", "physicalType": "int" } }, { "source": { "name": "parameterId" }, "sink": { "name": "parameter_id", "physicalType": "int" } }, { "source": { "name": "resultText" }, "sink": { "name": "result_text", "physicalType": "varchar", "nullValue": "null" } }, { "source": { "name": "resultSymbol" }, "sink": { "name": "result_symbol", "physicalType": "varchar", "nullValue": "null" } }, { "source": { "name": "resultNumber" }, "sink": { "name": "result_number", "physicalType": "decimal", "nullValue": "null" } } ], "typeConversion": true, "typeConversionSettings": { "allowDataTruncation": true, "treatBooleanAsNumber": false } } ')

   But even if the result_number column is nullable and the input contains a null, I get an error: ErrorCode=TypeConversionFailure,Exception occurred when converting value '' for column name 'result_number' from type 'String' (precision:, scale:) to type 'Decimal' (precision:10, scale:2). Additional info: The input wasn't in a correct format.

   This is what I see in the input tooltip:

   

   How can I make it insert a NULL in the db instead of failing?

Thank you

EDIT1

Some more details on my pipeline. This is where I think I should put the dataflow: And here's the details about the dataset:

Sometimes some rows are deleted manually from the excel before uploading, but even if they are empty the Lookup activity reads them as rows full of null, while I would like to skip them.

To skip the rows with full of null You can use filter activity as below:

• Take filer activity and pass the lookup output to it as items as below.
• Thena add condition as below where we are comparing each item if it is null or not if all items are null then row would get skipped;

@or(or(or(or(not(equals(item().sampleld,null)),not(equals(item().parameterld,null))),not(equals(item().resultText,null))),not(equals(item().resultSymbol,null))),not(equals(item().resultNumber,null)))

ErrorCode=TypeConversionFailure,Exception occurred when converting value '' for column name 'result_number' from type 'String' (precision:, scale:) to type 'Decimal' (precision:10, scale:2). Additional info: The input wasn't in a correct format.

In Copy activity, Value in additional column can be of string only. It does not accept null value there is the cause of error you are getting.

You can use DataFlow instead of Copy activity to resolve the issue

• First create dummy file with your data in single row and add it as source in dataflow. My source Data Preview:
• Create parameters in the data flow with default value as null() as per your respective datatype as below:
• Then pass the values from foreach to this parameter in dataflow activity parameter as below:
• Then in Dataflow take Derived column transformation and create columns with expressions using the above parameters as below image and expressions:

resultText1 - iif(equals($resultTextparam,''), toString(null()), $resultTextparam)
resultSymbol1 - iif(equals($resultSymbolparam,''), toString(null()), $resultSymbolparam)

• Then take select column transformation and select only the columns you created in derived column and rename it to original column name:
• In sink map the column properly and copy the data:

Final output: