CODEWITHSUNDEEP
azureazure-data-factory
Tim F
Tim F

Reputation: 85

ADFv2 trouble with column mapping (reposting)

I have a source .csv with 21 columns and a destination table with 25 columns.

Not ALL columns within the source have a home in the destination table and not all columns in the destination table come from the source.

I cannot get my CopyData task to let me pick and choose how I want the mapping to be. The only way I can get it to work so far is to load the source data to a "holding" table that has a 1:1 mapping and then execute a stored procedure to insert data from that table into the final destination.

I've tried altering the schemas on both the source and destination to match but it still errors out because the ACTUAL source has more columns than the destination or vice versa.

This can't possibly be the most efficient way to accomplish this but I'm at a loss as to how to make it work.

Yes I have tried the user interface, yes I have tried the column schemas, no I can't modify the source file and shouldn't need to.

The error code that is returned is some variation on:

"errorCode": "2200",
    "message": "ErrorCode=UserErrorInvalidColumnMappingColumnCountMismatch,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Invalid column mapping provided to copy activity: '{LONG LIST OF COLUMN MAPPING HERE}', Detailed message: Different column count between target structure and column mapping. Target column count:25, Column mapping count:16. Check column mapping in table definition.,Source=Microsoft.DataTransfer.Common,'",
    "failureType": "UserError",
    "target": "LoadPrimaryOwner"

Upvotes: 4

Views: 10990

Answers (1)

Jay Gong
Jay Gong

Reputation: 23782

Tim F. Please view the statements in this Schema mapping in copy activity:

Column mapping supports mapping all or subset of columns in the source dataset "structure" to all columns in the sink dataset "structure".

The following are error conditions that result in an exception:

1.Source data store query result does not have a column name that is specified in the input dataset "structure" section.

2.Sink data store (if with pre-defined schema) does not have a column name that is specified in the output dataset "structure" section.

3.Either fewer columns or more columns in the "structure" of sink dataset than specified in the mapping.

4.Duplicate mapping.

So,you could know that all the columns in the sink dataset need to be mapped. Since you can't change the destination,maybe you don't have to struggle in an unsupported feature.

Of course ,you could use stored procedure mentioned in your description.That's a perfect workaround and not very troublesome. About the using details, you could refer to my previous cases:

1.Azure Data Factory activity copy: Evaluate column in sink table with @pipeline().TriggerTime

2.Azure Data factory copy activity failed mapping strings (from csv) to Azure SQL table sink uniqueidentifier field

In addition, if you really don't want avoid above solution,you could submit feedback to ADF team about your desired feature.

Upvotes: 2

Related Questions