samegoalintegrator facilitates automated ODBC-based integrations between SameGoal and any third-party system (typically your student information system).
Basic workflow:
- SameGoal is configured to export CSV data files and upload them to your district SFTP account.
- The district installs samegoalintegrator to a local machine with network access to the IC database.
- samegoalintegrator actions are driven by two files included in the local directory when extracted:
When invoked, the samegoalintegrator program performs data synchronization using the following steps:
- Loads config.xml.
- Uses the SFTP username provided in config.xml to connect to your SFTP account.
- Downloads the most recent set of extracted data files from SameGoal. Save files to history\<epoch>\ia in the local directory.
- Uses the database connection string provided in config.xml to connect to the third-party database.
- Generates one CSV file for each SQLDiff element in the "Download" element of config.xml. Save files to history\<epoch>\ic in the local directory.
- Computes the 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. Details:
- 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 samegoalintegrator. These row differences can occur in two different ways: (i) documents in SameGoal were newly completed or modified OR (ii) 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).
- Calls SQL stored procedures in procedures.sql to bring the third-party database back in sync with SameGoal data.
- Runs 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.
- Uploads the local history\<epoch> directory to your SFTP upload account where SameGoal will automatically process and move it to upload-processed.
Steps
To install and configure samegoalintegrator:Download the samegoalintegrator zip file for your architecture onto a district computer with network access to your third-party system.
Extract and rename the zip file to a location of your choosing. Eg:
- C:\samegoalintegrator\config.xml
- C:\samegoalintegrator\procedures.sql
- C:\samegoalintegrator\samegoalintegrator.exe
- C:\samegoalintegrator\bins\
- C:\samegoalintegrator\ssh\
Add a private OpenSSH-style key to the ssh directory that has been synced with your district SFTP account.
- Configure your SFTP account if you have not yet done so.
- Add your private OpenSSH-style key (typically named "id_rsa") in the ssh directory.
Replace config.xml and procedures.sql with up-to-date files.
- Email tier2help@samegoal.com for up-to-date copies corresponding with current form versions.
- Replace config.xml and procedures.sql in the local directory with updated files received.
Configure config.xml.
- Search in file for "TODO(user)".
- Complete TODOs in file as directed.
Execute procedures.sql.
- Open Microsoft SQL Server.
- In the appropriate database:
- Visit File > Open > File...
- Select procedures.sql
- Click the "Execute" button (red exclamation mark)
- Once the script has run successfully, the messages pane should say "Command(s) completed successfully"
Test samegoalintegrator.
- Double-click the samegoalintegrator program to run the integration.
- samegoalintegrator will automatically create a history subdirectory.
- Each time samegoalintegrator is invoked, an epoch directory is added to `history which contains
- a directory of the files extracted from SameGoal (eg ia/)
- a directory of the files extracted from your third-party system (eg ic/)
- additional extracted CSV files from your third-party system
- a log file named sgi_log.txt
- a COMPLETE file, if the integration completed
- Inspect the epoch directory once the invocation completes. Open and review sgi_log.txt for errors.
Install a scheduled task to invoke samegoalintegrator.
- On Windows, add a Windows Scheduled Task to invoke samegoalintegrator.exe.
- On Linux, add a cron job to invoke samegoalintegrator.