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

Question

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'.'

Please check all the data to see if there are any properties inserted as null in the database. — Leon Yue, May 15 '19 at 09:55

score 1 · Accepted Answer · answered May 15 '19 at 12:07

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.

score 0 · Answer 2 · answered May 15 '19 at 10:02

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.

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

2 Answers2