ADF Copy 1 to 1 from Azure Table Storage to Azure SQL

Question

I am trying to make an exact copy of a table from Table storage into Azure SQL.

I have an issue with a column from the Source table(Table Storage) because it can have multiple data types, in my case String or DateTime. The problem occurs only when the column ArrivalTime from the first row retrieved has DateTime datatype. As I understood the datatype of the column is given by the first record. I get the below error just in the case mentioned above because other String values from column can't be covenverted to DateTime.
In other cases when the column from first row retrived has String DataType I don't have any issue since any other different datatype can be converted to String.

Destination column from AzureSql is set to nvarchar, so this is not a problem. The error occurs Source side

Copy activity encountered a user error at Source side:

Error Message:

ErrorCode=UserErrorInvalidDataValue,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Column 'ArrivalTime' contains an invalid value 'DateTime.Null'.,Source=Microsoft.DataTransfer.Common,''Type=System.ArgumentException,Message=Specified cast is not valid.Couldn't store in ArrivalTime Column. Expected type is DateTimeOffset.,Source=System.Data,''Type=System.InvalidCastException,Message=Specified cast is not valid.,Source=System.Data,'.

Did somebody else faced this situation ? Is there any way to bypass this issue ?

Thanks

Amor Amor · Accepted Answer · 2017-04-07T04:36:26

Couldn't store in ArrivalTime Column. Expected type is DateTimeOffset

Have you defined a structure property in the dateset definition for your Table Storage? If you didn't specify the structure of data by using the structure property in the dataset definition, Data Factory infers the schema by using the first row in the data. Though you changed the data type to nvarchar in Azure SQL, the date type of ArrivalTime from source will also be treated as DateTime if the data type of ArrivalTime in first row is DateTime.

Please add following structure definition to the dataset of Azure Table Storage.

structure:  
[
    { "name": " ArrivalTime ", "type": "String"}
]

For more information of Datasets in Azure Data Factory, link below is for your reference.

Datasets in Azure Data Factory

I already defined the structure like this in the input dataset and also in the output dataset

Sorry for providing the wrong direction. I tested ADF Copy followed by your post and I reproduced the issue. The issue is related to the mechanism of how ADF read data from Azure Table Storage. I haven't found any ways to fix this issue.

Is there any way to bypass this issue ?

If you are familiar with programming, it is easy to implement the copy function using Azure WebJob or Azure Function. In the WebJob or Function, you could read data from Azure Table Storage and write data to Azure SQL using following code.

// Parse the connection string and return a reference to the storage account.
CloudStorageAccount storageAccount = CloudStorageAccount.Parse("Azure Storage Connection String");
// Create the table client.
CloudTableClient tableClient = storageAccount.CreateCloudTableClient();
// Retrieve a reference to the table.
CloudTable table = tableClient.GetTableReference("table name");

TableQuery<MyTableEntity> query = new TableQuery<MyTableEntity>();
// Loop all the entities of Azure Table and insert into Azure SQL
foreach (MyTableEntity entity in table.ExecuteQuery(query))
{
    using (SqlConnection connection = new SqlConnection("connection string of azure sql"))
    {
        SqlCommand cmd = new SqlCommand("insert into tables (ArrivalTime) values (@ArrivalTime) ", connection);
        cmd.Parameters.AddWithValue("ArrivalTime", entity.ArrivalTime);
        connection.Open();
        cmd.ExecuteNonQuery();
    }
}

public class MyTableEntity : TableEntity
{
    public MyTableEntity(string pkey, string rkey)
    {
        this.PartitionKey = pkey;
        this.RowKey = rkey;
    }

    public MyTableEntity() { }

    public string PKey { get; set; }

    public string RKey { get; set; }

    public string ArrivalTime { get; set; }
}

ADF Copy 1 to 1 from Azure Table Storage to Azure SQL

1 Answers