Copy Activity data socurce preview error

Mahesh Madhusanka 216 Reputation points
2020-06-19T14:15:06.213+00:00

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'

10404-sourcetable-error.jpg

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,113 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Mahesh Madhusanka 216 Reputation points
    2020-06-20T03:09:04.4+00:00

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

    0 comments No comments

  2. HimanshuSinha-msft 19,381 Reputation points Microsoft Employee
    2020-06-25T22:08:55.57+00:00

    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