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

0
votes

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'.'
2
azure-sql-databaseazure-data-lakeu-sql
Please check all the data to see if there are any properties inserted as null in the database. – Leon Yue
@LeonYue I'm taking only 1 column: Id, and it is not null – Dor Meiri

2 Answers

1
votes

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.

0
votes

Please have a look at my answer on the other thread opened by you. To add to that, I would also recommend you to have a look at how to create a table using a query. In the query, you should be able to use "extractors" in the query to create the tables. To read more about extractors, please have a look at this doc.

Hope this helps.