question

DineshKakumanu-1746 avatar image
0 Votes"
DineshKakumanu-1746 asked MartinJaffer-MSFT commented

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

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:

135061-screenshot-2021-09-24-160450.png


azure-data-factory
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·
MartinJaffer-MSFT avatar image
0 Votes"
MartinJaffer-MSFT answered Jeeva-7004 commented

Hello @DineshKakumanu-1746 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;


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·

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

0 Votes 0 ·
DineshKakumanu-1746 avatar image
0 Votes"
DineshKakumanu-1746 answered MartinJaffer-MSFT commented

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?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes we can @DineshKakumanu-1746 . 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.

0 Votes 0 ·