Data Copy activity failing due to a geography data type in one of the columns

Dinesh Kakumanu 11

I'm copying data from a table in SQL DB to Azure Data lake. One of the columns is of data type Geography and I got to know that ADF doesn't support this data type so the copy activity is failing. Is there any way to copy the whole data including this column at a time using data factory itself?

Below is the error description:

MartinJaffer-MSFT 26,026 Reputation points

2021-10-08T23:05:15.653+00:00

If our conversation solved your issue,, please mark as accepted answer. Otherwise let me know how I may better assist.

2 answers

MartinJaffer-MSFT 26,026 Reputation points

2021-09-27T08:05:18.587+00:00
Hello @Dinesh Kakumanu and welcome to Microsoft Q&A.

Since Geography type is not supported, how about treating it as text?

I think the simplest way, would be to write a query, where the Geography columns are converted / typecast as varchar. There is also a geography to string function. In doing this you would need to specify all the columns, but it would work.

Something like,

Select ColumnA, Column B, ColumnC, ... GeoAsTextColumn as GeoColumn.ToString(), ColumnX, ColumnY, ColumnZ From MyTable;
Please sign in to rate this answer.
Jeevanandham Poongavanam 1 Reputation point

2021-09-27T17:56:07.857+00:00

@MartinJaffer-MSFT I do have a similar issue

I am migrating data from one sql server table to another table in a different sql server table. One of the cloumn data type is geography.

In the source data set, I used ToString on the column which gave me the value in Point but then when I tried to transform it back to geography within a sql db sink it gives me a different value.

Example:
Source Geography value - 0x18100000010C59F28B25BF984540A70D74DA40673040
ToString (GeoToString) - POINT (16.403333333333332 43.193333333333335)
In Sink Transformation - 0xE6100000010C59F28B25BF984540A70D74DA40673040 [convert(geography,GeoToString]

Stuck in this issue for a day now.

Jeevanandham Poongavanam 1 Reputation point

2021-09-27T18:12:56.173+00:00

@MartinJaffer-MSFT I kind of worked this out in sql client with below sql

select
position ,
position.STSrid,
position.ToString() as string_position,
geography::STGeomFromText(position.ToString(),position.STSrid )
from
<TableName>

But the same sql query used to transform the data in sql db sink gives me a different result. The value looks something like this UE9JTlQgKDEuNjI1IDUyLjg3MjE2NjY2NjY2NjY2NSk

Not sure whether to goahead with this assuming it would sort itself after inerting the records.
Sign in to comment
Dinesh Kakumanu 11 Reputation points

2021-09-27T08:08:42.667+00:00

Hi Martin,

Thanks for your response.

But I have a huge number of columns and I don't want to mention all the column names in the query. Can we do something like selecting all the columns except that one?
Please sign in to rate this answer.
MartinJaffer-MSFT 26,026 Reputation points

2021-09-27T20:05:48.187+00:00

Yes we can @Dinesh Kakumanu . If you want to skip the Geography columns, and load everything else, go to the Mapping section of the Copy Activity. In there you can import schemas, and then locate and remove the Geography columns.
Sign in to comment