Copy Data Activity failes on datatype uniqueidentifier when Staging is enabled and no column mapping configured.

XchangeVisions 301 Reputation points
2020-06-30T14:49:54.783+00:00

Using Azure Data Factory Copy Data Activity from Azure SQL Database to Azure Synapse Analytics.

The Copy Data Activity failed with InvalidCastException error 'The given value of type String from the data source cannot be converted to type uniqueidentifier of the specified target column.'.

This happens when Copy Data Activity is configured with Staging Enabled but without explicit columnd mapping configured.

The activity doesn't give an error if Staging is Disabled (without explicit column mapping).
The activity also doesn't give an error when explicit column mapping is configured while Staging is Enabled.

I think this is a bug?

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

Accepted answer
  1. XchangeVisions 301 Reputation points
    2020-07-13T09:51:24.06+00:00

    Hi @ChiragMishraMSFT-1092,

    For now, as a quick fix, I've resolved the issue by setting the staging option as disabled.
    I will create a workaround solution using a SWITCH activity to perform CopyData with specified mappings for the tables that have a uniqueidentifier column and a default (no specified mappings) for the tables that don't have a uniqueidentifier column.

    It will make the pipeline a bit more complex and less flexible, but it's manageable.

    Thanks for your support!


4 additional answers

Sort by: Most helpful
  1. ChiragMishra-MSFT 956 Reputation points
    2020-07-01T06:04:50.82+00:00

    HI @XchangeVisions ,

    In your case, when you specify column mapping, data types of source and sink are mismatched. Please check Data type mapping for SQL server.

    Your source data type is string which is mapped to uniqueidentifier in sql database, which needs GUID type in azure data factory.

    So,please configure sql server stored procedure in your sql server sink as a workaround.

    Please follow the steps from this doc:

    Step 1: Configure your Sink dataset:

    Zrcb2.png

    Step 2: Configure Sink section in copy activity as follows:

    XOQ4b.png

    Step 3: In your database, define the table type with the same name as sqlWriterTableType. Notice that the schema of the table type should be same as the schema returned by your input data.

        CREATE TYPE [dbo].[CsvType] AS TABLE(  
        [ID] [varchar](256) NOT NULL  
    )  
    

    Step 4: In your database, define the stored procedure with the same name as SqlWriterStoredProcedureName. It handles input data from your specified source, and merge into the output table. Notice that the parameter name of the stored procedure should be the same as the "tableName" defined in dataset.

    Create PROCEDURE convertCsv @ctest [dbo].[CsvType] READONLY  
    AS  
    BEGIN  
      MERGE [dbo].[adf] AS target  
      USING @ctest AS source  
      ON (1=1)  
      WHEN NOT MATCHED THEN  
          INSERT (id)  
          VALUES (convert(uniqueidentifier,source.ID));  
    END  
    

    Output:

    UEfkj.png

    Hope it helps.

    Ref - https://stackoverflow.com/a/51419543/10653466

    0 comments No comments

  2. XchangeVisions 301 Reputation points
    2020-07-01T12:33:50.463+00:00

    Hi @ChiragMishraMSFT-1092,

    Thanks for your reply.

    I also found the ref on stackoverflow but this doesn't give a answer to my problem.
    The Copy acitivity get's an uniqueidentifier from source and writes it into a uniqueidentifier on sink.
    So I don't deliver a string, data factory translates uniqueidentifier into a string when Staging is enabled and without explicit column mapping.

    11122-image.png

    11009-image.png

    I think it's weird behavior as the staging property is capable of handling uniqueidentifier as soon as I configure explicit column mapping.

    If however the difference in behavior is as intended, than I could better skip the dynamic copy activity and create specific activity for every table.
    Or create a SWITCH statement for tables that hold uniqueidentifier and just disable the staging for that activity.
    This will process without errors and is way less complex than using table types and stored procedures.

    I can't understand why there must be a difference but I will probably change the pipeline as a work around.


  3. Judkins, Shayne 1 Reputation point
    2022-06-03T19:57:02.73+00:00

    I am having the same problem. Is there a fix for this, yet?

    0 comments No comments

  4. Brandon Duncan 0 Reputation points
    2024-05-14T20:11:42.1+00:00

    If you're here in 2024 - On your source, convert the column when pulling down the data by specifying a query like this
    User's image