Overview of sync process

How the process works

There is a two step process to configure the Smartsheet to SQL process. The first step is configuring the sync process with the configuration script (this should be a one-time process for each sheet you wish to sync), the second step is performing the sync with the sync script.

The configuration script generates two files that the sync script uses - a JSON file and a SQL file. The JSON file stores the configuration necessary to perform the sync, and the SQL file contains a SQL script that will be used to update the database table (see details below).

Each sheet that you configure will generate separate JSON and SQL files. For example, if you sync a sheet with the ID 1234, the script will generate a JSON file named config-1234.json and a SQL script named SmartsheetToSQL-1234.sql.

If you then add another sync for a sheet with the ID 5678, the script will generate two new files specific to that sheet (config-5678.json and SmartsheetToSQL-5678.sql).

SQL Server changes

The sync process creates two tables in the SQL Server database. One table for the actual Smartsheet data and another as a temp table to handle changes. You can chooose the names of the tables during the configuration process.

During the actual sync process, the script deletes all data in the temp table, syncs the current Smartsheet data to the temp table, and uses the generated SQL file (via a MERGE command) to update the main sheet table.