# Sql Server JdbcSinkConnector to multiple tables

**URL:** <https://forum.confluent.io/t/sql-server-jdbcsinkconnector-to-multiple-tables/3791>\
**Category:** Kafka Connect\
**Created:** [30 December 2021 12:29 UTC](https://forum.confluent.io/t/sql-server-jdbcsinkconnector-to-multiple-tables/3791 "2021-12-30T12:29:29Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![guz280](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/guz280/32/1439_2.png) [@guz280](https://forum.confluent.io/u/guz280)\
**Post date:** [30 December 2021 12:29 UTC](https://forum.confluent.io/t/sql-server-jdbcsinkconnector-to-multiple-tables/3791/1 "2021-12-30T12:29:29Z")

</div>

**Scenario:**  
I am using the JdbcSinkConnector in Kafka to sink data from Kafka to the sql server database. I am using C# .net core 5.

**Problem:**  
I created tables in Sql server consisting of: Users & CreditCards tables.  
The CreditCards tables has a FK to the Users table which is PublicId.

I created the Connector successfully, infact when I sink to the Users table ONLY it works flawlessly (Inserting & updating).

But when I sink to 2 tables ie Users & CreditCards tables it fails. The Kafka Connector fails with the following error:  
_“Table “users” is missing fields ([SinkRecordField{schema=Schema{ARRAY}, name=‘CreditCard’, isPrimaryKey=false}]) and auto-evolution is disabled”_

**This is the Avro Schema I have:**  
@“{”“type”“:”“record”“,”“name”“:”“User”“,”“namespace”“:”“sample”“,”“fields”“:[{”“name”“:”“publicid”“,”“type”“:”“bytes”“},{”“name”“:”“name”“,”“type”“:”“string”“},{”“name”“:”“surname”“,”“type”“:”“string”“},{”“name”“:”“CreditCard”“,”“type”“:[”“null”“,{”“type”“:”“array”“,”“items”“:{”“type”“:”“record”“,”“name”“:”“CreditCard”“,”“namespace”“:”“sample”“,”“fields”“:[{”“name”“:”“publicid”“,”“type”“:”“bytes”“},{”“name”“:”“cardnumber”“,”“type”“:”“string”“},{”“name”“:”“expiry”“,”“type”“:”“string”“}]}}]}]}”

The C# class has reference to the CreditCard object which implements the _ISpecificRecord_.

Is there something wrong with my connector?  
Is there a working example where I can follow?

**Connector details:**  
{  
“connector.class”: “io.confluent.connect.jdbc.JdbcSinkConnector”,  
“connection.url”: “jdbc:sqlserver://0.0.0.0:1433;databaseName=database”,  
“connection.user”: “user”,  
“connection.password”: “\*\*\*”,  
“topics”: “users”,  
“key.converter”: “io.confluent.connect.avro.AvroConverter”,  
“key.converter.schema.registry.url”: “[http://schema-registry:8081](http://schema-registry:8081)”,  
“value.converter”: “io.confluent.connect.avro.AvroConverter”,  
“value.converter.schema.registry.url”: “[http://schema-registry:8081](http://schema-registry:8081)”,  
“auto.create”:“true”,  
“pk.mode”:“record\_value”,  
“pk.fields”:“publicid”,  
“insert.mode”:“upsert”,  
“table.whitelist”: “dbo.users, dbo.creditcards”  
}

**SQL table Users**  
Id (int, not null) auto incrementing on Insert  
PublicId (PK, uniqueidentifier, not null)  
Name (varchar(50), not null)  
Surname (varchar(50), not null)

**SQL table CreditCards**  
Id (int, not null) auto incrementing on Insert  
PublicId (PK, FK, uniqueidentifier, not null) → refers to the PublicId in the Users table  
CardNumber (varchar(50), not null)  
Expiry (varchar(50), not null)

---

<div class="post-metadata">

**Author:** ![rick](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/rick/32/49_2.png) [@rick](https://forum.confluent.io/u/rick)\
**Post date:** [30 December 2021 14:31 UTC](https://forum.confluent.io/t/sql-server-jdbcsinkconnector-to-multiple-tables/3791/2 "2021-12-30T14:31:31Z")

</div>

@guz280 The schema you reference has a field `CreditCard` with a union type of either `null` or the `CreditCard` Record type. The error message indicates that the table `users` is missing that field, and the table definition you provide confirms that `Users` does not have a `CreditCard` field.

I don’t believe that Kafka Connect can do the magic for you to extract the Credit Card information from the `user` event and sink it into the `creditcards` table for you.

I also do not believe that the `table.whitelist` configuration value you provide is relevant to a `JDBCSinkConnector`. You provide the `topics` and the `table.name.format` configurations. This tells the connector which topics to consume and what the destination table names will be.

If possible, you could write the Credit Card events to an separate credit card topic at the source, or, If the credit card records are required to be embedded in the `users` topic events, I believe you will have to use some type of stream processing to extract them into their own topic if you wish to use Kafka Connect to sink them to a `creditcards` table.

Hope this helps

---

<div class="post-metadata">

**Author:** ![guz280](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/guz280/32/1439_2.png) [@guz280](https://forum.confluent.io/u/guz280)\
**Post date:** [30 December 2021 15:41 UTC](https://forum.confluent.io/t/sql-server-jdbcsinkconnector-to-multiple-tables/3791/3 "2021-12-30T15:41:17Z")

</div>

@rick Thanks for your reply

I agree with what you said especially that kafka will not do the magic for you to extract the Credit Card information from the `user` event .

Thanks again & happy new year

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex019/uploads/confluentcommunity/original/1X/c49438c90c9df282e9996fdf6971be890c71b65a.svg) [@system](https://forum.confluent.io/u/system)\
**Post date:** [29 January 2022 15:42 UTC](https://forum.confluent.io/t/sql-server-jdbcsinkconnector-to-multiple-tables/3791/4 "2022-01-29T15:42:03Z")

</div>

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.