How to pass optional column in TABLE VALUE TYPE in SQL from ADF

Question

I have the following table value type in SQL which is used in Azure Data Factory to import data from a flat file in a bulk copy activity via a stored procedure. File 1 has all three columns in it so this works fine. File 2 only has Column1 and Column2, but NOT Column3. I figured since the column was defined as NULL it would be ok but ADF complains that its attempting to pass in 2 columns when the table type expects 3. Is there a way to reuse this type for both files and make Column3 optional?

 CREATE TYPE [dbo].[TestType] AS TABLE(
    Column1 varchar(50) NULL,
    Column2 varchar(50) NULL,
    Column3 varchar(50) NULL
)

Operation on target LandSource failed: ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Trying to pass a table-valued parameter with 2 column(s) where the corresponding user-defined table type requires 3 column(s)

Would be nice if the copy activity behavior was consistent regardless of whether or not a stored procedure with table type is used or native BCP in the activity. When not using the table type and using the default bulk insert, missing columns in the source file end up being NULL in the target table without error (assumming the column is NULLABLE).

Answer 1

You can pass optional column, I've made a test successfully, but the steps will be a bit complex. In my case, File 1 has all three columns, File 2 only has Column1 and Column2, but NOT Column3. It will use Get Metadata activity, Set Variable activity, ForEach activity, IfCondition activity.

Please follow my steps:

1. You need to define a variable FileName to foreach.

2. In the Get Metadata1 activity, I specified the file path.

3. In the ForEach1 activity, use @activity('Get Metadata1').output.childItems to foreach the filelist. It need to be Sequential.

4. Inside the ForEach1 activity, use Set Variable1 to set the FileName variable.

5. In the Get Metadata2, use item().name to specify the file.

6. In the Get Metadata2, use Column count to get the column count from the file.

7. In the If Contdition1, use @greater(activity('Get Metadata2').output.columnCount,2) to determine whether the file is larger than two columns.

8. In the True activity, use variable FileName to specify the file.

9. In the False activity, use Additional columns to add a Column.

10. When I run debug, the result shows: