Share via

Table Storage Source with nullable datetime property does NOT work if property value of first table entry is null

  • Article

Question

Monday, April 8, 2019 5:55 PM

So I've encountered a really weird behavior today. I have a very simple Table Storage table with only one column. It's a DateTime column called "EntryDate".

When I use this table as the source for my Data Factory the import only works if the first entry in the table is not null. But as soon as I delete the property on the first entry I get the error below.

EDIT2: Just as a bonus, if all values in the column are null it starts to work again.



Activity Copy Data1 failed: Failure happened on 'Source' side.
ErrorCode=UserErrorInvalidDataValue,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,
Message=Column 'EntryDate' contains an invalid value '04/01/2019 00:00:00 +00:00'.,
Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.ArgumentException,
Message=Unable to cast object of type 'System.DateTimeOffset' to type 'System.IConvertible'.
Couldn't store <4/1/2019 12:00:00 AM +00:00> in EntryDate Column.
  Expected type is DateTime.,Source=System.Data,''Type=System.InvalidCastException,
Message=Unable to cast object of type 'System.DateTimeOffset' to type 'System.IConvertible'.,
Source=System.Data,'

This seems like a pretty huge bug to me. You can't expect that the first entry of a table will have a value in each column.

Did anyone else encounter this and maybe has a workaround for me how to get it working until this is fixed?

EDIT: To add some more information here is what my Source, Sink and Pipeline in the Data Factory look like:

Source

{
    "name": "SourceTest",
    "properties": {
        "linkedServiceName": {
            "referenceName": "ls_safinancereporting_tablestorage",
            "type": "LinkedServiceReference"
        },
        "type": "AzureTable",
        "structure": [
            {
                "name": "EntryDate",
                "type": "DateTime"
            }
        ],
        "typeProperties": {
            "tableName": "test"
        }
    }
}

Sink

{
    "name": "DestinationTest",
    "properties": {
        "linkedServiceName": {
            "referenceName": "MyDBReference",
            "type": "LinkedServiceReference"
        },
        "type": "AzureSqlTable",
        "structure": [
            {
                "name": "EntryDate",
                "type": "DateTime"
            }
        ],
        "typeProperties": {
            "tableName": "test"
        }
    }
}

Pipeline

{
    "name": "ImportTest",
    "properties": {
        "activities": [
            {
                "name": "Copy Data1",
                "type": "Copy",
                "policy": {
                    "timeout": "7.00:00:00",
                    "retry": 0,
                    "retryIntervalInSeconds": 30,
                    "secureOutput": false,
                    "secureInput": false
                },
                "typeProperties": {
                    "source": {
                        "type": "AzureTableSource",
                        "azureTableSourceIgnoreTableNotFound": false
                    },
                    "sink": {
                        "type": "SqlSink",
                        "writeBatchSize": 10000,
                        "sqlWriterStoredProcedureName": "[dbo].[TestImport]",
                        "sqlWriterTableType": "[dbo].[TestType]"
                    },
                    "enableStaging": false,
                    "translator": {
                        "type": "TabularTranslator",
                        "columnMappings": {
                            "EntryDate": "EntryDate"
                        }
                    }
                },
                "inputs": [
                    {
                        "referenceName": "SourceTest",
                        "type": "DatasetReference"
                    }
                ],
                "outputs": [
                    {
                        "referenceName": "DestinationTest",
                        "type": "DatasetReference"
                    }
                ]
            }
        ]
    }
}

All replies (6)

Wednesday, April 24, 2019 2:00 PM ✅Answered

Hi, Staeff

    Thanks for reporting this issue.

    DataFactory PG will do investigation and apply fix accordingly.

    For quick mitigation, you may specify the Azure Table Column/Structure type as String.

Friday, April 12, 2019 5:37 AM

The entrydate property is of the type datetime , but i think you have selected it was datetimeoffset ( the error message above shows that ) . You need to update the mapping in source and also in sink to datetime .and then it will work fine . 

Thanks Himanshu

Friday, April 12, 2019 6:38 AM

Hi Himanshu,

thanks for your answer. But as you can see in the configuration I've posted in my edit I've already set everything to "DateTime". This is also part of the bug on your side. It seems to try to use "DateTimeOffset" if the first value is null. Again if the first value isn't null the same configuration works just fine.

My current workaround is to insert a dummy entry first which I ignore later on, but this is not a satisfiable solution to this problem.

Could you please look further into this?

Best Regards,
Stefan

Friday, April 12, 2019 10:24 PM

Hi Stefan , 

Once you fix the DatetimeOffset , are you getting the same error ?  

I did tried to repro and I was able to do that with the Datetimeoffset set but once I fixed that the pipeline ran fine ( with the property still being null ) 

Thanks Himanshu

Saturday, April 13, 2019 12:09 AM

Hi Himanshu,

just to be sure I've created my demo from scratch. I now only try to insert into the SQL table directly (without a stored procedure inbetween) and I've rechecked multiple times that everything is set to DateTime.
But still the familiar error:

How I've created my demo:

  1. First I created a table in the Table Storage called "datetest" and added 3 entries, but only for the second one added a property "EntryDate" with a DateTime value.
  2. Then I've created a simple SQL table "dbo.DateTest" with a datetime2 column (see below)
  3. Like last time I just added my source from "Azure Table Storage", set it to my linked Table Storage service and table, hit "Import schema" and changed "DateTimeOffset" to "DateTime.
  4. Then I've added my sink from "Azure SQL Database" and did the same as above without changing the schema because it was already imported with "DateTime".
  5. After that I've created a pipeline with a "Copy" activity, selected source and sink and imported the schemas in the "Mapping" section where everything was mapped as expected with the "DateTime" type.

(The "1/2 mapped" is only because I've removed the RowKey and Timestamp from the source schema, but it also doesn't work if I leave them in)

Here is the generated code:

Pipeline: https://pastebin.com/KCDbpCwH
Source: https://pastebin.com/EBXAHNRQ
Sink: https://pastebin.com/y0Zj3jv9

And here is my SQL table for the sink:

create table [dbo].[DateTest] (
    [EntryDate] DATETIME2(0)
)

Can I provide you with any additional information that would help you?

BR,
Stefan

Thursday, April 18, 2019 8:54 AM

Hi Himanshu,

do you have any update on this? It's still a huge pain point for me and could break production pipelines under certain circumstances.

BR,
Stefan