locked
DateTime source column type not supported by Azure Table? RRS feed

  • Question

  • Hi there,

    I'm trying to use the Data Factory Copy Wizard to copy an Azure SQL table over to Azure Table Storage on an hourly schedule. The schema of the SQL table includes Datetime columns.

    Right now, the wizard isn't letting me proceed past the destination schema mapping screen, saying 'Source column type is not supported by Azure Table' for said Datetime columns. This is strange because I know it's possible to use Datetime columns in an Azure Table schema, and also because I was able to successfully set up a similar scheduled copy activity two months ago, which is still running without error. (And by 'similar' I mean 'copying the exact same table to a different table storage account.')

    Is this an error, or has something changed with any of the aforementioned services since July that's preventing me from achieving my goal, or is there something I'm missing with my setup? Is there a suggested workaround I could use in the meantime?

    Thanks much.

    ETA: I was able to successfully copy the table by setting the activity up manually; however, it's still strange that the wizard doesn't allow this.
    • Edited by Katy Shimizu Saturday, September 17, 2016 8:34 PM More information.
    Saturday, September 17, 2016 6:53 PM

Answers

  • Hello Katy,

    Azure Table Storage doesn't support Datetime column in essential, instead it supports DateTimeOffset. So the workaround could be using query to convert the Datetime columns to DateTimeOffset columns. See https://msdn.microsoft.com/en-us/library/bb630335.aspx?f=255&MSPPError=-2147217396 for how to use TODATETIMEOFFSET to do conversion.

    Let me know if it doesn't work.


    Keep involved!

    • Marked as answer by Katy Shimizu Wednesday, September 21, 2016 9:31 PM
    Monday, September 19, 2016 4:04 AM
  • Hello Katy,

    Sorry for my too quick reply. Actually, I've spent several hours to explore the Datetime supporting in Azure Table Storage underneath. And it turns out Azure Table Storage does support Datetime type, it also stores a Datetime value (Always UTC) for DateTimeOffset. So we would enhance the Copy Wizard to support that. Before this improvement is shipped you can have the workaround like I said above.

    Thanks,

    Jingfei


    Keep involved!

    • Marked as answer by Katy Shimizu Wednesday, May 24, 2017 4:49 PM
    Thursday, September 22, 2016 7:33 AM

All replies

  • Hello Katy,

    Azure Table Storage doesn't support Datetime column in essential, instead it supports DateTimeOffset. So the workaround could be using query to convert the Datetime columns to DateTimeOffset columns. See https://msdn.microsoft.com/en-us/library/bb630335.aspx?f=255&MSPPError=-2147217396 for how to use TODATETIMEOFFSET to do conversion.

    Let me know if it doesn't work.


    Keep involved!

    • Marked as answer by Katy Shimizu Wednesday, September 21, 2016 9:31 PM
    Monday, September 19, 2016 4:04 AM
  • Thanks, Jingfei; I appreciate this clarification of how Azure Table Storage works. Manually setting up the copy activity worked without a DateTime to DateTimeOffset conversion, but I'm going to go the DateTimeOffset route in the future as per your advice.
    Wednesday, September 21, 2016 9:33 PM
  • Hello Katy,

    Sorry for my too quick reply. Actually, I've spent several hours to explore the Datetime supporting in Azure Table Storage underneath. And it turns out Azure Table Storage does support Datetime type, it also stores a Datetime value (Always UTC) for DateTimeOffset. So we would enhance the Copy Wizard to support that. Before this improvement is shipped you can have the workaround like I said above.

    Thanks,

    Jingfei


    Keep involved!

    • Marked as answer by Katy Shimizu Wednesday, May 24, 2017 4:49 PM
    Thursday, September 22, 2016 7:33 AM