Transfer data from U-SQL managed table to Azure SQL Database table

Question

I have a U-SQL managed table that contains schematized structured data.

CREATE TABLE [AdlaDb].[dbo].[User]

(

    UserGuid Guid,

    Postcode string,

    Age int?

    DateOfBirth DateTime?,

)

And an Azure SQL Database table. 

CREATE TABLE [SqlDb].[dbo].[User]

(

    UserGuid    uniqueidentifier NOT NULL,

    Postcode    varchar(15) NULL,

    Age         int NULL,

    DateOfBirth Date NULL,

)

I would like to transfer data from U-SQL managed table to the Azure SQLDB table without losing the data types.

I'm using azure data factory, seems like I cannot

1. directly query the U-SQL managed table as an input dataset for data factory
2. do a federated write query to Azure SQLDB

Hence I'm having an intermediate step where I copy from U-SQL managed table to Azure Blob and then move to Azure SQLDB table. Doing this, I'm losing the data type and having to have type conversion/transformations later again before inserting.

Is there any better way to transfer data from U-SQL managed table to Azure SQL Database table without losing data type? Or am I missing something? 

Answer 1

• We can make use of Azure Data Factory to move the file into Azure SQL Database else we can export the U-SQL table into an intermediate format like CSV in data stores like Azure Data Lake or blob storage

• We can directly write into a table from a U-SQL script that has a lot of challenges due to the fault-tolerance and scale-out processing in U-SQL. This makes atomic writing in parallel into a transacted store a bit more complex.