CODEWITHSUNDEEP
azure-data-factory
saul
saul

Reputation: 979

ADF V2 - ADLS to SQL Server On prem, does column order matter?

I've been trying to move data from a flat file in ADLS (Azure Data Lake Store) to a SQL Server On Prem, using purely ADLS and SQL Server Datasets, I thought that the TabularTranslator Property was going to help to map the columns and ignore the order of how they are in the flat file. doing something like this

{
    "source": {
        "type": "AzureDataLakeStoreSource"
    },
    "sink": {
        "type": "SqlSink",
        "writeBatchSize": 10000
    },
    "enableStaging": false,
    "cloudDataMovementUnits": 0,
    "translator": {
        "type": "TabularTranslator",
        "columnMappings": "ColA: ColA, ColB:ColB"
    }
}

However it looks like the order matters, and TabularTranslator might only work for SQL Server and Azure SQL databases, is there anyway to map these columns without having to change the column order of my target?

The error I'm getting is a truncation error because it is trying to insert ColB data into ColA in my destination

More details In other words, I have the following source and target...

Source: File.tsv

ColA ColB

"codeA" 1

"codeB" 2

"codeC" 3

Target: SQL Server table

|---------------------|------------------|
|      ColB           |     ColB         |
|---------------------|------------------|
|                     |                  |
|---------------------|------------------|

Looks like the data coming from ColA tries to get inserted into ColB and then I got the following:

"ErrorCode=UserErrorInvalidDataValue,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Column 'ColB' contains an invalid value 'codeA';. Cannot convert 'codeA'; to type 'Double';.,Source=Microsoft.DataTransfer.Common,''Type=System.FormatException,Message=Input string was not in a correct format.,Source=mscorlib,'"

Upvotes: 0

Views: 556

Answers (1)

Martin Esteban Zurita
Martin Esteban Zurita

Reputation: 3209

Using a tabular translator is not needed, I've only used it when moving data from ADLS to a warehouse using Polybase, but I think this is not supported for on-premises databases.

Take a look at the documentation when using a data lake as a source: https://learn.microsoft.com/en-us/azure/data-factory/connector-azure-data-lake-store#copy-activity-properties

It should work completely fine without a tabular translator in your case.

Hope this helped!!

Upvotes: 1

Related Questions