Search code examples
azure-sql-databaseazure-data-lakeu-sql

U-SQL External table error: 'Unable to cast object of type 'System.DBNull' to type 'System.Type'.'

I'm failing to create external tables to two specific tables from Azure SQL DB,

I already created few external tables with no issues. The only difference I can see between the failed and the successful external tables is that the tables that failed contains geography type columns, so I think this is the issue but i'm not sure.

CREATE EXTERNAL TABLE IF NOT EXISTS [Data].[Devices]
(
    [Id] int
)
FROM SqlDbSource LOCATION "[Data].[Devices]";

Failed to connect to data source: 'SqlDbSource', with error(s): 'Unable to cast object of type 'System.DBNull' to type 'System.Type'.'
Solution

  • I solved it by doing a workaround to the external table: I created a view that select from external rowset using EXECUTE

    CREATE VIEW IF NOT EXISTS [Data].[Devices]
AS 
SELECT Id FROM EXTERNAL SqlDbSource 
EXECUTE "SELECT Id FROM [Data].[Devices]";

    This made the script to completely ignore the geography type column, which is currently not supported as REMOTEABLE_TYPE for data sources by U-SQL.