Sync MailChimp and SQL server - seems impossible to handle SQL primary keys

  • 30Views
  • Last Post 11 April 2018
0
votes
John thirtle posted this 10 April 2018

I'm developing a facility to mediate between a SQL database hosted on Azure and MailChimp's ListMembers table.  Skyvia seems to be the right tool - great. 

Your connector to SQL correctly requires that the target SQL database has a primary key. So I create a DT_WSTR compliant field called 'Id' in the SQL table and declare it as a primary key.

MailChimp 'ListMembers' has a matching field, but Skyvia's matching screen does not show primary key fields on the matching list. So you would think that there is some default matching taking place maybe.

However when I run the task I get the error "SQLite error no such column: REF_ID_Id".

Maybe this is a bug -- unless I'm doing something wrong?  

(BTW I have tried this process using the MailChimp API 2.0 and 3.0 Skyvia connectors. In both cases the SQL table's primary key is not shown on the matching screen and running the task throws the 'no such column' error for the primary key. In MailChimp API 3.0 the error is 'Invalid column name [column]. Invalid column name [column].')

0
votes
Mariia Zaharova posted this 11 April 2018

 If we understood you correctly, you want to specify some key column in order to allow the system to know which record in one source corresponds to which record in the opposite source. Unfortunately, at the moment, it is impossible to specify any key for mapping existing records in the synchronized sources to each other for the first synchronization.

When synchronization is performed for the first time, it does not check whether the records in sources are identical and simply copies data from one side to another and vice versa, and "maps" the original records to their copies in another source. 

When performing synchronization repeatedly, Skyvia synchronizes only data that was changed since the previous synchronization. It uses the fields storing information about when a record was created and modified in cloud applications and creates special tracking tables and triggers in relational databases for data modification tracking.

 

NOTE: The main requirement for synchronizing database data is that the primary key columns of the database tables must be autogenerated. And this columns is not used in the mapping.

Also, the "***_tracking_store" table is created in SQL Server database while performing initial synchronization and contains information about changes in the corresponding SQL Server tables before the next synchronization. After each synchronization this tables is cleared. If you delete or rename this table, your sync task will fail. 

 

Thus, please make sure your SQL Server table has autogenerated primary key columns, specify mapping for the tables/columns that must be synchronized.

In order to restore "***_tracking_store" table and start initial synchronization, please open your package editor and click Reset value for InitTrackingObjects and LastSyncTime in the Parameters section.

 

Please refer to: 

https://skyvia.com/resources/docs/index.html?synchronization_overview.htm

https://skyvia.com/resources/docs/synchronization_tutorials.htm

Close