Data Integration from SFTP - As Data Source

Importing Data as a Data Source via SFTP

The Course Evaluations & Surveys SFTP data import as a data source feature allows the secure transfer of files containing course information and corresponding student, instructor, and teaching assistant enrollments. Once imported, this data can be searched and selected for inclusion in specific projects. Additionally, the Enrollment Refresh feature automates student, instructor, and teaching assistant enrollment updates for courses already added to a project.
 

Operational Steps

SFTP Folder Setup

1. If SFTP has not already been set up for your organization, contact the Support Team to request an SFTP folder setup. Course Evaluations & Surveys will establish a secure directory to deposit files.

2. Connect to the folder via Secure File Transfer Protocol (SFTP).

3. Locate the two primary directories created within the SFTP root directory:

• DataSourceFilesToProcess: Drop new data files here to upload course and enrollment data into the system data source.

• DataSourceFilesProcessed: Review this directory to find files that have successfully completed processing.

SFTP File Specifications

1. Format: Save files in Comma Delimited format with a CSV file extension (.csv).

2. File Name: Name the file using the format CourseUsers-mmddyyyyhhmm.csv, where MMDDYYYYHHMM serves as the date and time identifier.

3. Header Row: Include a required header row containing the exact field names.

NOTE: Column headers must match required field names exactly. For example, the SFTP file will fail if a header is written as 'First Name' instead of 'Firstname'. However, character capitalization does not matter (e.g., 'firstname' or 'FIRSTNAME' will still process successfully).

4. Content Rows: Ensure each row represents a single enrollment action (Add or Drop) into a course, aligned with the header row fields.

SFTP Fields and Characteristics

Feel free to omit optional fields from the SFTP file if they are not needed. When omitting a field, remove the field name from the Header Row and exclude the data from the Content Rows entirely.

SFTP Fields and Field Characteristics

1. File Header row includes the following fields:
   1. Code, Title, UniqueId, UserTypeId, Firstname, Lastname, Email, Username.
2. Each additional row in the file indicates either a student, instructor or teaching assistant enrollment into a course following the fields indicated in the Header.

SFTP File with Optional Fields

1. File Header row includes the following fields:
   1. Code, Title, UniqueId, SurveyStart, SurveyEnd, AdminStart, AdminEnd, InstructorStart, InstructorEnd, TAStart, TAEnd, UserTypeId, Firstname, Lastname, Email, Username, Password.
2. Each additional row in file indicates either a student, instructor or teaching assistant enrollment into a course following the fields indicated in the Header.

File Processing

1. Once a file is deposited in the SFTP folder, the system/service will detect a new file has been added and immediately process the file. The enrollments contained in the file will accordingly get added or dropped from the specified course.
2. Dropping enrollments is no different than the process described above, with the exception that the AddDrop code in the row would be set to “0” to drop the enrollment.
3. The SFTP files should only contain an enrollment needing to be added or dropped. Enrollments contained in the file to be added, which are already in the specified course will be ignored if no changes are detected in any of the row contents for that enrollment.

File Confirmation Email and Error Messaging/Handling

1. Once a file is deposited into the SFTP folder and processed, a confirmation email will be sent to the email address you designated under Account > SFTP.
2. Confirmation Email Subject Line
   1. Includes – Filename
   2. Includes – File Status (Successful, Some Failures, Failed)
3. Confirmation Email Contents of Email
   1. Includes – Filename
   2. Includes – File Status (Successful, Some Failures, Failed)
   3. Summary Metrics Organized by Project ID from the file
      1. Courses Added
      2. Student Enrollments Added and Dropped
      3. Instructor or Teaching Assistant Enrollments Added and Dropped
      4. Failed Enrollments
      5. Specific Rows in File That Failed
4. Additional Error Handling
   1. In the event a file is deposited in the SFTP folder and the Course Evaluations & Surveys service to process the file is halted for any reason (e.g., System Maintenance, etc.), SFTP files will accumulate in the SFTP Folder until the service resumes processing, at which time the service will process any/all files in the folder beginning with the oldest file.
   2. Any files added to the SFTP Folder not conforming to the required format will generate a Confirmation Email indicating a Failure to process the file.

Removing Courses from the Data Source

Once courses are added to the data source, they remain there indefinitely. If courses are no longer needed (e.g., when starting a new Fall term and the previously uploaded Spring courses are no longer needed), they can be deleted by navigating to Account > FTP.

This feature allows for clearing the entire data source or deleting specific courses via the search tool. Once the process is complete, a confirmation email is sent, and the deletion appears under Notifications.

NOTE: This process does not remove any courses that have already been imported into a CES project. It only removes courses from the list used to select and import courses into a CES project.

FILE REQUIREMENTS AND SAMPLE

1. Required file format is Comma Delimited, with CSV file extension (.csv)
2. Name file CourseUsers-mmddyyyyhhmm.csv
3. File contains a header row and each additional row represents an enrollment into a course.

ATTACHMENTS