ADF Copy Data Activity Fails with InvalidCastException when Moving Data from Dataverse to Oracle Database 19c

Question

I am having trouble using a copy data activity in ADF. The copy is supposed to move data from Dataverse to Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.23.0.0.0. I am mapping just 2 fields, contact.contactid (this is a guid), and contact.accountnumber .

I keep getting this error. The error message looks like there is a cast problem. Just no idea how to fix it.

I tried changing the Oracle destination field type from varchar2 to nvarchar2, that didn't help.

Failure happened on 'Sink' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Failed to add value to the parameter array. ParameterName=ACCOUNT_GUID, ValueType=WCHAR, ParameterType=WVARCHAR, ColumnSize=-1, ElementBufferSize=-1, Precision=0, Scale=0, Length=0, Value=1012e65e-e52f-ee11-bdf3-000d3a31ed99, ValueLogicalType=System.Guid,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=Microsoft.DataTransfer.ClientLibrary.Odbc.Exceptions.OdbcBulkCopyException,Message=Failed to add value to the parameter array. ParameterName=ACCOUNT_GUID, ValueType=WCHAR, ParameterType=WVARCHAR, ColumnSize=-1, ElementBufferSize=-1, Precision=0, Scale=0, Length=0, Value=1012e65e-e52f-ee11-bdf3-000d3a31ed99, ValueLogicalType=System.Guid,Source=Microsoft.DataTransfer.ClientLibrary.OdbcBulkCopy,''Type=System.InvalidCastException,Message=Object must implement IConvertible.,Source=mscorlib,'

Mapping:

"mappings": [
                {
                    "source": {
                        "name": "accountid",
                        "type": "Guid"
                    },
                    "sink": {
                        "name": "ACCOUNT_GUID",
                        "type": "String"
                    }
                },
                {
                    "source": {
                        "name": "accountnumber",
                        "type": "String"
                    },
                    "sink": {
                        "name": "ACCOUNT_NUM",
                        "type": "String"
                    }
                }
            ]

Answer 1

The error you're encountering during the ADF Copy Data activity when moving data from Dataverse to an Oracle Database 19c seems to be related to the datatype conversion of the GUID from Dataverse to Oracle.

1. Change the GUID Mapping to a String Representation:

• Since Oracle does not have a native GUID datatype, you should store the GUID as a string in Oracle. Ensure that the destination column ACCOUNT_GUID in Oracle is defined as VARCHAR2 (or NVARCHAR2 if you need Unicode support) with sufficient length (typically 36 characters).
  • In your mapping, ensure that the GUID is treated as a string. This should already be done based on your configuration, but ensure the length of the destination column is correct.

2. Use a String Conversion in ADF:

• If ADF is attempting to cast the GUID to a different type, you may need to explicitly convert the GUID to a string format before inserting it into Oracle. You can do this by using a derived column transformation in ADF to ensure that the GUID is treated as a string.
  • Create a derived column in your ADF pipeline before the data is loaded into Oracle:

    
         {
    
             "name": "derived_column",
    
             "type": "DerivedColumn",
    
             "column": {
    
                 "ACCOUNT_GUID_STRING": {
    
                     "type": "string",
    
                     "expression": "toString(accountid)"
    
                 }
    
             }
    
         }
    
    

  • Then map this derived column to your Oracle destination column.

3. Check the Oracle ODBC Driver:

• Ensure that the ODBC driver used for connecting to Oracle is up to date and compatible with the datatype mappings you're using. Sometimes, issues like this can be caused by an outdated or incompatible driver.

4. Modify ColumnSize, Precision, and Scale:

• If you're manually configuring the mapping, make sure the ColumnSize, Precision, and Scale values are appropriately set for the Oracle destination. In some cases, setting these parameters explicitly can resolve issues related to type conversion.

5. Error Handling with Data Type Mismatch:

• You may also consider implementing error handling in your ADF pipeline to capture and manage rows that fail due to type conversion issues. This can help you identify specific problematic data.

6. Verify Oracle Field Type:

• Double-check that the field in Oracle is defined correctly to receive a string representation of a GUID. If you originally defined it as NVARCHAR2 but still face issues, try setting it as VARCHAR2 if Unicode is not a concern, and ensure that the length is sufficient.

Example of Final Mapping in ADF:

   "mappings": [

       {

           "source": {

               "name": "accountid",

               "type": "Guid"

           },

           "sink": {

               "name": "ACCOUNT_GUID",

               "type": "String",

               "length": 36

           }

       },

       {

           "source": {

               "name": "accountnumber",

               "type": "String"

           },

           "sink": {

               "name": "ACCOUNT_NUM",

               "type": "String"

           }

       }

   ]