Copy Activity data socurce preview error

Question

Hi Team,

Currently i have setup some tables move to dataLake using copy Activity pipeline, However i have realized few of tales have a issue when i connect it to copy activity source, According to error pop up as a below, Could you please check and advise on it?

Error - ''Type=Newtonsoft.Json.JsonSerializationException,Message=Error getting value from 'Value' on 'System.Data.SqlTypes.SqlDouble'.,Source=Newtonsoft.Json,' 'Type=System.Data.SqlTypes.SqlNullValueException,Message=Data is Null. This method or property cannot be called on Null values.,Source=System.Data,' . Activity ID: 1d856261-8d28-4f27-a76a-6d0af9c2ef0f'

Answer 1

@KranthiPakala-MSFT and @majaffer
Thank you for your response, please find the Answer for a above concern

What is source dataset ? - This is a SQL server Data set

What is the IR used? - Self Hosted IR

most of tables moved using copy data activity, few tables faced this issue, According to i checked after ran pipeline then its error getting as a

“{
"errorCode": "2200",
"message": "Failure happened on 'Sink' side. ErrorCode=DataTypeNotSupported,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The data type Microsoft.SqlServer.Types.SqlGeography is not supported.,Source=Microsoft.DataTransfer.Common,''Type=System.Collections.Generic.KeyNotFoundException,Message=The given key was not present in the dictionary.,Source=mscorlib,'",
"failureType": "UserError",
"target": "Bulk_Direct",
"details": []
}”

10228-geography-datatype.jpg
Then i found a issue as a that issue affected all tables have a this Geo datatype. Is that Reason for a Data Preview issue as well ?

Then i used query for a data source table then its worked but data type value total different

select GC_PK,
convert(varchar, GC_GeoLocation.STAsText()) as GC_GeoLocation_AfterConvert
from Glbcompany;

Before convert original data Geography type data
10377-original-geocolumn-data.jpg

After Convert to Varchar Data values got change, How can i copy same column data values to data Lake? Your Immediate response much appreciate.

Answer 2

Hello @MaheshMadhusanka-3900

The Geography data type cannot be copied as it is as ADF does not support , I suggest you can convert the sane to varchar ( as you already said ) , that will give you in the data in points and then copy the same on the sink and then run from Transformation to change back to geography data type . Adding transformation is very easy in data flow ( DF ) but since you are using SHIR you cannot use that . I am putting some SQL queries and it should give you the idea .
Hope this helps

 CREATE TABLE [dbo].[GeographyTest]
 (
     [RowId] [int] IDENTITY(1,1) NOT NULL,
     [Location] [geography] NOT NULL,
     CONSTRAINT [PK_GeographyTest] PRIMARY KEY CLUSTERED ( [RowId] ASC )
 ) 
    
 insert into GeographyTest(Location) values (geography::Point(47.6475, -122.1393, 4326))
    
 select 
 convert(varchar, [Location]) as GC_GeoLocation_AfterConvert
 ,convert(geography, convert(varchar, [Location]) ) -- Add this on the sink side 
 from [GeographyTest];

Himanshu

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members