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

1
votes

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).

2
sql-serverazure-data-factorytable-valued-parameters
stackoverflow.com/questions/26390617/… - Amit Kumar Singh
Does this answer your question? Syntax for Default Value for User Defined Type as Table - Amit Kumar Singh
I don't have control over how ADF will call the stored procedure using the table value type. Even though the column was NULLABLE, I used your reference to create a default value of NULL for the type, but it still complains that I'm only passing 2 values yet the type expects 3. - Geekn

2 Answers

1
votes

It will cause the mapping error in ADF.

  1. In the Copy Activity, every column needs to be mapped.

enter image description here

  1. If the source file only has two columns, it will cause mapping error.

enter image description here

So, I suggest you to create two different Copy activities and create a two columns table type.

0
votes

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. enter image description here

  2. In the Get Metadata1 activity, I specified the file path. enter image description here

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

  4. Inside the ForEach1 activity, use Set Variable1 to set the FileName variable. enter image description here

  5. In the Get Metadata2, use item().name to specify the file. enter image description here

  6. In the Get Metadata2, use Column count to get the column count from the file. enter image description here

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

  8. In the True activity, use variable FileName to specify the file. enter image description here

  9. In the False activity, use Additional columns to add a Column. enter image description here

  10. When I run debug, the result shows:
    enter image description here