Getting error while ingesting a nested Avro in a MS SQL table using kafka-connect-jdbc in kafka

Question

As part of POC, I am trying to ingest Avro messages with schema registry enabled from Kafka Topics into JDBC Sink(MS SQL Database).But i am facing some issues while ingesting nested avro data to a MS Sql table. I am using kafka-connect-jdbc-sink to ingest avro data to a MS Sql table from Kafka Avro Console Producer.

Details mentioned below

Kafka Avro Producer CLI Command

    kafka-avro-console-producer --broker-list server1:9092, server2:9092,server3:9092 --topic testing25 --property schema.registry.url=http://server3:8081 --property value.schema='{"type":"record","name":"myrecord","fields":[{"name":"tradeid","type":"int"},{"name":"tradedate", "type": "string"}, {"name":"frontofficetradeid", "type": "int"}, {"name":"brokerorderid","type": "int"}, {"name":"accountid","type": "int"}, {"name": "productcode", "type": "string"}, {"name": "amount", "type": "float"}, {"name": "trademessage", "type": { "type": "array", "items": "string"}}]}'

JDBC-Sink.properties

        name=test-sink
        connector.class=io.confluent.connect.jdbc.JdbcSinkConnector
        tasks.max=1
        topics=testing25     connection.url=jdbc:sqlserver://testing;DatabaseName=testkafkasink;user=Testkafka
        insert.mode=upsert
        pk.mode=record_value
        pk.fields=tradeid
        auto.create=true
        tranforms=FlattenValueRecords
        transforms.FlattenValueRecords.type=org.apache.kafka.connect.transforms.Flatten$Value
        transforms.FlattenValueRecords.field=trademessage

connect-avro-standalone.properties

bootstrap.servers=server1:9092,server2:9092,server3:9092
key.converter=io.confluent.connect.avro.AvroConverter
key.converter.schema.registry.url=http://server3:8081
value.converter=io.confluent.connect.avro.AvroConverter
value.converter.schema.registry.url=http://server3:8081
internal.key.converter=org.apache.kafka.connect.json.JsonConverter
internal.value.converter=org.apache.kafka.connect.json.JsonConverter
internal.key.converter.schemas.enable=false
internal.value.converter.schemas.enable=false
offset.storage.file.filename=/tmp/connect.offsets
plugin.path=/usr/share/java

So after running the jdbc-sink and the producer while i am trying to insert the data in cli i am getting this error

ERROR WorkerSinkTask{id=test-sink-0} Task threw an uncaught and unrecoverable exception. Task is being killed and will not recover until manually restarted. (org.apache.kafka.connect.runtime.WorkerSinkTask:584)
org.apache.kafka.connect.errors.ConnectException: null (ARRAY) type doesn't have a mapping to the SQL database column type

I understand that it is failing on Array Data type as SQL Server does not contain any such data type. So I researched and found that we can use Kafka Connect SMT's(Single Message Transform) functionality(flatten) to flatten nested values.

But this does not seems to be working in my case. Transform values passed in JDBC-sink are doing nothing. Infact I tested with other transformations as well like InsertField$Value & InsertField$Key but none of them are working. Please let me know if i am doing anything wrong in running these transformations in Kafka connect.

Any help would be appreciated.

Thanks