Unknown SQL type - -99 error when importing schema on Copy activity mapping tab for a DB2 table with Clob data

Question

I am trying to copy data from one db2 database table to another duplicate db2 table for archiving purposes.

I am using a "Copy" activity to create a csv, and then using the csv in another "Copy" activity to insert the data into the other table.

When I click the "import schemas" button in the "Mapping" tab of the second "Copy" activity I am getting the following error:

Failed to import sink schema. The value of the property '' is invalid: 'Unknown SQL type - -99.'.

Unknown SQL type - -99.

Activity ID: 89cbee77-ed06-4977-a9c5-95a318d82fad

The source and target tables are made up of String and DateTime types except for one column that is a Clob. I believe that is the column that is causing issues.

I see that in the data type mapping table on https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-schema-and-type-mapping Clob is not listed.

Is there a way to convert the String value from the csv to the Clob on the target table from within the pipeline?

Answer 1

Hi @Zakary Kurzawski Thank you for posting the question on this community.

I just want to double check if you are trying the copy activity pipeline to push the data DB2 as a sink. DB2 is not currently supported as a Sink end point for copy activity. Please refer Supported data stores and formats for Copy activity and notice that DB2 is not supported as s Sink. We have an existing feedback to add DB2 as a Sink datastore. Here are feedback ideas for your reference - DB2 as sink data store DB2 as a Sink. I request you to kindly upvote the feedback ideas.

Coming to your question on casting data from string to Clob, as you observed in the supported Data type mappings, Clob is not a supported option for Copy activity. If you just want to store the data as a back and if there is a flexibility on type of data on the back up table, you can set the corresponding column type as string in back up table and copy from CSV file to the table.

Alternatively, if you want to keep the same data type, try using Data Flow Cast transformation activity which lets you modify the data types of individual columns.

Hope this helps. Please let us know if you need any further assistance regarding this.

---

If the response helped, please do click Accept Answer and Yes for the answer provided. Doing so would help other community members with similar issue identify the solution. I highly appreciate your contribution to the community.