Support for SQL Geography Type in CopyData Activity

[IntegrateGuru]

I am just trying to understand why the copy data activity has such a hard time converting the SQL geography data type to a string to store into a Delta Table. 

It's capable of exporting the values as strings to csv files. 

But the sink mapping of geography -> string will cause your pipeline to fail every time, with this error:

he logical type is not supported in Delta format. Reason: Cannot find supported delta type for column name 'myGeographyColumn', logical type ,Source=Microsoft.DataTransfer.ClientLibrary,'

The only workaround, if you want to copy the data into your lakehouse without going first to csv file, is to manually write the SQL in the source like:

SELECT [myGeographyColumn].toString as myGeographyColumn FROM dbo.myTable

I have tried every source/sink data type, and have scoured the web for more info on the translator configurations that are stored in the pipeline JSON for copydata activites, but there is little to no information available.

Does anyone have any methods of ingesting the data from a pipeline with only the source/sink TabularTranslator configuration?

[v-kongfanf-msft · Accepted solution]

Hi @IntegrateGuru ,

As you say, the best option at the moment is to use below code to make sure that the data is already in string format before it reaches the Delta table, thus avoiding the type mismatch issue.

SELECT [myGeographyColumn].toString as myGeographyColumn FROM dbo.myTable

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.