Interface with a Staging SIS (Student Information System) Database

Your EMS consultant will provide you with the following files:

• SIS Staging DB Schema.xlsx – for you to fill out with the help of your implementation consultant

  This file will contain information about the data that needs to be taken from the SIS and put into the staging database in the appropriate locations.

• queries.xml – needed when you install the Campus Web Service

  It goes in the CustomConfig folder.

• StagingDatabaseInstall.sql – run against the SQL environment

  This file installs the staging database and creates all the appropriate tables and configurations that need to be created.

1. Run the StagingDatabaseInstall.sql script in your SQL environment.

   This installs the staging database. You need admin rights for SQL (usually an administrator on the server will have sufficient rights, but as long as the user is a SQL admin in the environment, that will work). Do not try to create the database / tables manually. This script will do that for you and set the permissions correctly.

2. Install the Campus Web Service (CWS).

   If you are an EMS Cloud customer, the CWS installation will be given to you by your consultant. If you are an on-prem customer you will need to download it through the EMS support portal.

3. Replace the default queries.xml file with the one you were given above.

4. Verify that the web.config file in the CWS directory is updated to point to the staging database.

5. In the web.config file, find the Connection String section.

6. Verify that the System.Data.SqlClient part of the connection string is not commented out (<!--), and that the connection string for it is pointing to the correct SQL server and has the staging database name listed as the “database=”.

7. In the User ID field enter EMSUser.

8. In the Password field enter p3CTU9i87h8TSB2op3CTU9i8.

9. If you are an on-prem user, indicate your EMS database connection in the “deaConnection” string in the line below.

   If you are an EMS cloud customer, fill in the “emsDataWebServiceUrl” (your consultant will provide additional information).

Once the installations are done, you will have to load data from your SIS into the staging database. It is your responsibility to load the data into the staging database, and to work out a process for doing this. Most customers create a program on their end to pull the data from the SIS into the staging database on an automated schedule.

Use the SIS Staging DB Schema.xlsx spreadsheet to determine what data from your SIS needs to be matched to the fields in the staging database. Each sheet corresponds to a table in the staging database, with a list of the columns that appear in each table. Your Implementation Consultant will assist you in this process.

In the image above, for Buildings each row corresponds to a column in the staging database Building table. In this example, you need to populate the first column with a Building ID which must be unique, the second column with a Building Code, and the third column with the Building Description. The different columns in the spreadsheet give information on what kind of data EMS is looking for, the max length (if one), and any helpful notes to guide you.

In the middle columns in the spreadsheet that are listed as SIS, you indicate what data from the SIS is going to be used to populate the staging database. Below is an example of the spreadsheet filled out:

This mapping exercise is to simplify what data you will be pulling from the SIS and its location. It also allows you to see how the data in the different tables interacts to get the data that EMS needs.

This step requires input from both the academic scheduling office and the SIS database administrators to determine what data will meet the requirements that EMS is looking for.

On the Course Dates tab, there are two rows Room ID and Room Updated that are highlighted in red. These data fields are not populated by data from the SIS; this is where EMS writes location data back in case you want to update the SIS with information from EMS.

To get the data that has been identified from the SIS into the Staging Database you can use one of the following methods:

• Manual flat file transfers
• Direct database communication

You will need to determine how often you refresh data from the SIS into the staging database, and how you pull new terms / course information. Also, when you want to stop pulling course / term data for past terms.

The hardest part is if you pick up location changes from EMS. EMS will write location information to the staging database, but if you do not pick up the change before you overwrite the course information with a refresh of data, the location change will be lost and EMS will sync with old data (putting the course back in the old location). You also need to develop a process that sets the Room Updated back to 0 once it has processed the change.

You should also take into consideration the frequency of the auto sync process. If auto sync only happens every 60 minutes, it’s not necessary to update the staging database every 15 minutes.