The samegoalintegrator program developed by SameGoal is designed to help facilitate automated ODBC-based integrations between SameGoal and any third-party system (typically your student information system).

Overview link

SameGoal export link

A nightly job in the SameGoal server cluster extracts CSV data files nightly from your SameGoal database. These CSV files are placed in an epoch directory and uploaded to your district's SFTP account. The SFTP server (s is located in the SameGoal DMZ so that it can accept incoming connections from the external network. OpenSSH-style key pairs are required for authentication.

Third-party import link

A nightly scheduled task, run on a district computer which has network access to the third-party database, invokes samegoalintegrator. samegoalintegrator performs the work required to bring the third-party database back in sync with data in SameGoal.

Data synchronization link

samegoalintegrator performs data syncronization in the following steps:

  1. Load configuration file config.xml.

  2. Use the SFTP username provided in config.xml to connect to your SFTP account. Download the most recent set of extracted data files from SameGoal. Save files to history\<epoch>\ia in the local directory.

  3. Use the database connection string provided in config.xml to connect to the third-party database. Generate one CSV file for each SQLDiff element in the Download element of config.xml. Save files to history\<epoch>\ic in the local directory.

  4. Compute a diff between each history\<epoch>\ia\filename.csv and history\<epoch>\ic\filename.csv based on the order of the SQLDiff elements in config.xml. Call SQL stored procedures to bring the third-party database back in sync with SameGoal data. See default stored procedures file procedures.sql for more details.

  5. Run SQL statements provided in the Upload element of config.xml. These statements may execute stored procedures required for final data cleanup in the third-party database and/or generate necessary CSV files related to open records or student mapping which must be processed before the next extract of SameGoal data files. Upload extracted files to your SFTP account upload directory. SameGoal will automatically process them.

  6. Upload the local history\<epoch> directory to your SFTP upload account. SameGoal will automatically process the directory and send a summary email based on the results of the integration.

Performing the diff link

  • Performing the diff is not strictly necessary for INSERT/UPDATE cases. In theory, samegoalintegrator could blindly invoke the stored procedure for each row in the SameGoal extract CSV. Calling the stored procedure would result in no change when the row in the third-party database "matched". However, this design would be slow as each stored procedure invocation is relatively expensive.
  • Computing the diff in native code means samegoalintegrator only calls stored procedures for rows that do not align in SameGoal and the third-party application. In other words, rows that have changed since the last run of samegoalintergrator. These row differences can occur in two different ways:
    • Documents in SameGoal were newly completed or modified.
    • Data in the third-party application was modified (eg. an IEP is unlocked due to a user viewing it).
  • Due to this design, in the steady state the nightly diff will only include the working set of changes for that day (relatively small set of total rows).
  • The diff will fail if the rows in both sets of CSV files are not sorted. A safety check is included in samegoalintegrator to automatically fail if it finds two adjacent rows which are not in ascending order.
  • The history folder contains logging information for the last 365 runs of samegoalintegrator. Diff information for runs older than the last 365 runs is automatically garbage collected to avoid boundless disk use. Note that if samegoalintegrator is run multiple times in a single day, that counts for multiple runs in the quota (resulting in less than a full year of history).

directions_walk STEPS

To install:

1. Schedule an automated job on SameGoal's servers to extract data files nightly from your SameGoal database. link

Contact your SameGoal representative. He or she can configure data to be extracted in the file format needed for your specific integration.

2. Install samegoalintegrator and associated configuration files.

Download the samegoalintegrator zip file for your architecture onto a district computer with network access to your third-party system.

Rename the zip file (samegoalintegrator is recommended). Extract the zip file to a location of your choosing (eg C:\). The resulting directory structure will be (on Windows):

  • C:\samegoalintegrator\config.xml
  • C:\samegoalintegrator\procedures.sql
  • C:\samegoalintegrator\samegoalintegrator.exe
  • C:\samegoalintegrator\bins\
  • C:\samegoalintegrator\ssh\

3. Add your private OpenSSH-style key to the ssh directory. link

Generate a public/private key pair if you have not done so already. Place your private OpenSSH-style key in the ssh directory. This key will be used by samegoalintegrator to authenticate to your district SFTP account. This key is typically named id_rsa, and was created when you configured your SFTP account.

4. Customize config.xml

config.xml must specify:

  • SFTP account username
  • Database connection string
  • Download configuration (directory path on SFTP, diff SQL statements, etc)
  • Upload configuration (final stored procedures to call, extract SQL, etc)

The default configuration file provided contains examples and detailed instructions. Update this file to contain settings and logic specific to your district and third-party system.

lightbulb_outline TIP
To test your integration in a SANDBOX environment, supply the SANDBOX database connection string in this configuration file. Once you are ready to move the integration to production, simply update the connection string.

5. Customize procedures.sql

procedures.sql must specify stored procedures to:

  • define integration mapping tables
  • perform diff handling (delete, insert, update rows)
  • perform final data manipulation (eg. close records)

The default stored procedures file provided contains examples and detailed instructions. Update this file to contain settings and logic specific to your district and third-party system.

6. Install a scheduled task to invoke samegoalintegrator nightly.

On Windows, add a Windows Scheduled Task to invoke samegoalintegrator.exe nightly. On Linux, add a cron job to invoke samegoalintegrator. We recommend invoking samegoalintegrator around 1am Eastern Time to ensure new data files have been extracted from the SameGoal database.

7. Troubleshoot/debug. link

samegoalintegrator automatically creates a history subdirectory. Each time samegoalintegrator is invoked, an epoch directory is added to history which contains (1) a directory of the files extracted from SameGoal, (2) a directory of the files extracted from your third-party system and (3) a log file. The epoch directory for each invocation may be inspected. These files are also automatically uploaded to your SameGoal SFTP account in order to generate a summary email.

This email summary may be sent each time samegoalintegrator is invoked. Contact your SameGoal representative to enable this feature, providing the email address(es) you wish to receive summaries. We recommend sending these types of emails to an alias at the district (eg. so that multiple staff members may inspect and troubleshoot the integration as necessary.