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).
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 (sftp://upload.iepanywhere.com/) 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.
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.
samegoalintegrator performs data syncronization in the following steps:
Load configuration file config.xml.
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.
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.
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.
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.
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.
Contact your SameGoal representative. He or she can configure data to be extracted in the file format needed for your specific integration.
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):
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.
config.xml must specify:
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.
procedures.sql must specify stored procedures to:
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.
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.
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. firstname.lastname@example.org) so that multiple staff members may inspect and troubleshoot the integration as necessary.