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

Question

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?

Answer 1

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!

Answer 2

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:

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

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:

Hope it helps.

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

Answer 3

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.

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.

Answer 4

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

Answer 5

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