Automated Data Import from Infinite Campus

This document is intended to guide district technology staff in the installation and configuration of the components required to export student and roster data from Infinite Campus to JumpRope. Once correctly configured - the automated daily export system will allow JumpRope roster data to be in synchronization with rosters is Infinite Campus.

Begin by downloading the file attached below: JumpRope Export2.0.zip

Table of Contents:

1. Choose a Server
2. Setup ODBC Database Connection
3. Install PHP
4. Install WinSCP (FTP client)
4a. Setup WinSCP Session
4b. Test WinSCP Session
5. Install Export Scripts
5a. Test Export Scripts
6. Create Scheduled Tasks


1. Choose a server
These configuration instructions assume that you will be using a Windows server.  This is a very lightweight process, so a dedicated server is not required.

Some configuration steps can be skipped if you choose a server with the following attributes:

• Already has an ODBC connection configured for your Infinite Campus Database
• Already has PHP installed

Some suggestions for choosing a server that may already have ODBC and/or PHP:

• The JumpRope export process is very similar to the process used by School Messenger. If your district uses School Messenger, the server hosting that export process would be an ideal host for the JumpRope export because the ODBC connection and PHP should already be present.
• If your district has a reporting server for generating custom reports out of IC, then such a server would already have an ODBC connection configured.
• If your district has a local Infinite Campus server (i.e. not hosted by IC), then that machine may also have an ODBC connection configured.
• If none of these options are viable – just choose any 64 bit Windows server on your network. NOTE: The process might work on a 32 bit system, but it has not been tested and additional steps (and tech support) may be required.

2. Setup ODBC Database Connection
To complete this step you will need SQL credentials for your Infinite Campus database.

• SQL credentials for IC can be acquired by submitting a support case.
• Read Only database access is sufficient, but you can also use/request Read/Write access.
• If you are hosted, you will likely also need to tell IC the IP address of the server that you will be connecting from (so they can create a firewall exception).

Once you have obtained your SQL credentials, follow these steps:

1. Launch the 32 bit ODBC Data Source Administrator Tool
2. For Servers with 64-bit Operating Systems, launch the 32 bit ODBC configuration tool from the (ironically named) SysWOW64 folder (C:/WINDOWS/SysWOW64/odbcad32.exe)  NOTE: It's best not to use a server with a 32-bit Operating System. It could probably be made to work but additional software is needed.
3. Click on the System DNS Tab
4. Click Add…
5. From driver list select SQL Server Native Client (version 10.0 or 11.0 are both fine, just use whatever is already installed.)
6. Click Finish
7. Choose a Data Source Name that makes sense (something like "Infinite Campus")
8. Description is not important
9. Use your Infinite Campus Server (IP or alias)
10. If Hosted, use odbc.maine.infinitecampus.org
11. Click Next, Choose SQL Server Authentication…
12. Enter your Login ID and Password (scquired from IC via a support case)
13. Click Next
14. Use pull-down menu to change default database to your production database
15. The name of your production database can be found in your URL for IC (preceding .jsp)
16. Click Next, Click Finish (no setting changes on this page)
17. Click to Test Data Source.  If the test completes successfully, your ODBC connection is ready for use.  If unsuccessful, seek support from JumpRope.

3. Install PHP 5.6

1. PHP is a free procedural programming language that will be used to run the export script.
2. This step may be skipped if your server already has PHP installed.
3. A PHP zip file of the recommended version can be downloaded using this link.
1. Alternatively, other versions specific to your operating system version may be downloaded here: http://windows.php.net/download/ Download the zip file for the latest x86 thread-safe version in the 5.6.x series.  NOTE: PHP 7 requires additional configuration and downloads, and 5.6 is safe to run.
4. Right click on the PHP zip file and choose Extract All…
1. A typical install location for PHP is in the root of the C drive, but it can go anywhere.
2. If you are using PHP on your SchoolMessenger serv, it will likely be in C:/SMDIM
3. PHP does not use an install program, unzipping the file is all that is required.

4. Install WinSCP

WinSCP is a free file transfer utility (FTP/SFTP) You can download it here: http://winscp.net/eng/download.php

1. Download & Run the Installation Package
2. Allow WinSCP to make changes to your computer, and choose language etc…
3. Click Next ⦁ Accept License Agreement
4. Click Next ⦁ Choose Typical Installation
5. Choose Commander Interface ⦁ Click Install ⦁ Click Finish

4a. Setup WinSCP Session:

1. Run the WinSCP application (found in Program Files (x86))
2. Choose SFTP file protocol
3. Host Name: sync2015.jumpro.pe
4. Port Number: 22
5. User Name: provided by JumpRope
6. Password: provided by JumpRope
7. Click Save
8. Rename the session (i.e. site name) to jumprope@sync2015.jumpro.pe
9. Choose to Save Password (even though it is not recommended by the tool, this is required to automate the process)
10. It may also be useful to have a desktop shortcut

4b. Test WinSCP Session

1. Select the saved session called ⦁ jumprope@sync2015.jumpro.pe
2. Click Login
3. If asked: click Yes to allow connection to unknown server.
4. WinSCP session should open:
• Left panel shows files on your computer.
• Right panel shows the JumpRope server and contains a folder called jumprope_imports.
• The export process (from IC) will place files in the jumprope_imports folder.
• You can test the FTP process simply by dragging a file from your computer to the jumprope server.

5. Install Export Scripts

1. From the zip file attached below (JumpRope Export2.0.zip), copy the JumpRopeExport folder to C:/ on your chosen server.
2. Some changes will need to made to the Export Scripts that are unique to your district:
• Open Notepad (i.e. basic text editor included in all windows Operating Systems)
• From within Notepad, choose File > Open
• Navigate to your JumpRopeExport Folder then locate and open the following file:
• C:/JumpRopeExport/Scripts/DatabaseConfig.php
• NB: Notepad will try to open a *.txt file but you are looking for a .php file extension
• On Lines 24, 25 & 26 you will need to configure the database connection for the PHP script:
• For the $odbcName variable provide your ODBC connection name (probably “Infinite Campus”)
• For the $userName variable provide the read_only username that IC gave you
• For the $passWord variable provide the appropriate password (also provided by IC)
• Save and exit
3. Now we will need to associate the export script with PHP so that it may be run and tested:
• Use windows explorer to locate C:/JumpRopeExport/Scripts/CreateJumpRopeExportFiles.php
• Right click on the file and choose Open With > Choose a default program
• Browse to the location of your PHP installation and select php.exe as the file association
• php.exe will probably be in C:/PHP[version]/php.exe
• If using a SchoolMessenger server it will likely be in C:/SMDIM/PHP/php.exe
4. Now double click CreateJumpRopeExportFiles.php to test the script execution. A black command prompt window should open while the script runs.  For hosted sites, the script may take 20 minutes or more to run, depending on the size of your data set. For those with their own server, it should typically take less than 1 minute
5. To check if the script was successful, go to C:/JumpRopeExport/Export Files
• There should be 4 export files and one log file
• Each file should be more than 0 bytes in size
• If files are missing or empty, an error has occurred: Check logs and contact JumpRope

5a. Test Export

• There should also be a file in the scripts folder called TransferJumpRopeExportFiles.bat, which is a batch script that will use your pre-configured WinSCP session (called jumprope@sync2015.jumpro.pe) to your export files (and log files) to the JumpRope server.
• This file does not require any editing (as long as you named your WinSCP session correctly)
• Double click this file to test the script execution. Again, the black command prompt window should open briefly while the script runs (this should take less than a minute to complete)
• Now re-launch WinSCP (C:/Program Filex(x86)/WinSCP.exe)
• Login using the saved JumpRope session
• In the right hand panel of the FTP screen, open the jumprope_imports folder and confirm the existence of your export files and one log file.
• You can re-test the file creation and export scripts at any point, and if you do it more than once, be sure to take note of the date and time on each file to confirm that new files have been created/exported

6. Create Scheduled Tasks

Two tasks will be created.  The first will run the PHP file creation script.  The second will run the batch FTP script.  Both actions could be placed in a single task, but two tasks allows better spacing between executions (i.e. 30 minutes) and differentiated error diagnosis.

1. Open the Windows Task Scheduler: Start > Administrative Tools > Task Scheduler
2. Click to Create Task…
3. Name the task JumpRope-CreateExportFiles
4. Set to run whether user is logged in or not
5. Run with highest priviledges
6. Select the Triggers tab
7. Click New…
8. Choose Daily execution
9. Supply Start Date (i.e. today)
10. Supply Start Time.  We recommend 12:15am.  Click Ok.
11. Select the Actions tab
12. Click New…
13. Choose to Start a Program
• Program/Script, browse to the php executable (typically C:\PHP\php.exe)
• Arguments = CreateJumpRopeExportFiles.php
• Start in = C:\JumpRopeExports\Scripts
14. Conditions and Settings do NOT need to be added or modified from default settings
15. When you exit the Task Creation wizard, you will be asked for windows credentials. By running this task with the highest privileges, approval by a system admin is required.

To create the second task, repeat same steps as above, but with the following changes:

• Call the task JumpRope-TransferExportFiles
• Choose a daily execution time that is one hour after the first task, such as 1:15am (this should ensure that the first task fully completes first), but ensure that it is before 3am EST in order to be included in JumpRope's scheduled synchronization.
• In Program/Script settings, simply use the full path to the batch program, and leave the two optional fields blank. The batch file is run by windows, not be by PHP.
• Program/Script: C:/JumpRopeExport/Scripts/TransferJumpRopeExportFiles.bat
• Add arguments (optional) = <blank>
• Start in (optional) = <blank>

At this point the setup should be complete!  Please notify JumpRope's implementation team, and we will check the next morning to ensure that data came through as expected.  If so, we will get in touch with next steps!