This plugin creates CSV files compatible with D2L IPSIS for managing student, staff, guardian and course synchronization from PowerSchool to Brightspace.
Feb-June 2022 : Aaron Ciuffo : aciuffo@ash.nl : aaron.ciuffo@gmail.com
- Data Flow and Integration
- Additional Tools
- Implementation Notes
- PowerSchool Setup and Installation
- Automated Exports from PSL to BrightSpace
- List of Plugins and Functions
- Plugin Errors & Resolutions
- Plugin Documentation
- Reference Documentation
- IPSIS Import Errors and Solutions
Data is exported from PowerSchool SIS (PS) using the PowerSchool Plugin structure and imported to Brightspace using IPSIS. Plugins contain PS/SQL queries and are executed using the scheduled Data Export Manager functionality in PS. More information regarding the structure of the plugins can be found below.
Several tools are provided by this repo to help package PowerQuery plugins and manage IPSIS imports.
package.sh: create a .zip file that is appropriately structured for upload into PowerSchool's plugin interface. The script will append the current version sourced from the version string in PackageDir/plugin.xml and the date and time.
Usage:
package.sh ./path/to/directory
Example
$ ./package.sh BS_Organization
~/Documents/src/PowerQuery/BS_Organization ~/Documents/src/PowerQuery
adding: permissions_root/ (stored 0%)
adding: permissions_root/05_offerings.permission_mappings.xml (deflated 33%)
adding: permissions_root/02_departments.permission_mappings.xml (deflated 33%)
adding: permissions_root/05_offerings_ath.permission_mappings.xml (deflated 33%)
adding: permissions_root/01_template.permission_mappings.xml (deflated 33%)
adding: permissions_root/04_templates.permission_mappings.xml (deflated 33%)
adding: permissions_root/06_sections.permission_mappings.xml (deflated 33%)
adding: permissions_root/03_semesters.permission_mappings.xml (deflated 33%)
adding: plugin.xml (deflated 39%)
adding: queries_root/ (stored 0%)
adding: queries_root/01_template.named_queries.xml (deflated 66%)
adding: queries_root/05_offerings_ath.named_queries.xml (deflated 68%)
adding: queries_root/05_offerings.named_queries.xml (deflated 68%)
adding: queries_root/06_sections.named_queries.xml (deflated 70%)
adding: queries_root/04_templates.named_queries.xml (deflated 66%)
adding: queries_root/02_departments.named_queries.xml (deflated 65%)
adding: queries_root/03_semesters.named_queries.xml (deflated 70%)
CREATED PLUGIN: BS_Organization-V1.2.03-20230227_081756.zipThe CSV files created by the PowerSchool Data Export Manager (DEM) need to be sent to D2L via SFTP as a single zip file. The .zip file must:
- Be a flat directory with no folders
- Contain a
manifest.jsonfile that declares the IPSIS implementation version
The run_SFTP_BS.bat can be run from the Windows OS that hosts the PowerSchool instance as a scheduled job. The script hard-codes the username and password for the D2L SFTP service. The username and password can be found in the IPSIS configuration screen.
To ensure that an upgrade to the PowerSchool system does not result in major changes to the IPSIS CSV files, the following procedure is recommended:
- Prior to the PowerSchool upgrade, switch IPSIS integration to Mode: Validate from the IPSIS Administration screen.
- Obtain an IPSIS .zip file from the day prior to the upgrade and the day after the upgrade. The last seven days of IPSIS imports are stored on pkg.ash.nl/Downloads.
- Use the
bspace_comparison.shscript to compare the two files.
The bspace_comparison tool compares two zip files that contain similar sets of IPSIS exports. If any of the archives have a change delta of more than 10%, the file will be flagged.
In the event a file is flagged, the related export and PowerQuery should be investigated to find the source of the change.
Automated exports are managed through PowerSchool PowerQuery Plugins. Plugins follow the structure outlined below.
Each CSV Export for Brightspace is managed through an individual plugin. Each plugin contains an SQL query that matches the required fields for the CSV. See the Automated Exports from PSL to Brightspace section for more information.
Two different methods for enrolling parents are provided BS_Users_Enrollments and D2L_Users_Enrollments. BS_Users_Enrollments relies on contact information stored in the U_STUDENTSUSERFIELDS view. As of February 2024 this data will be deprecated. The D2L_Users_Enrollments plugin uses the new unlimited contacts data in PowerSchool.
Parents & Guardians are enrolled in Brightspace with the following roles:
Parent & Guardian
Provides access to:
- Student progress and grades via Parent & Guardian app and web interface
- View only access to student courses (as Parent & Guardian role)
- Various training courses
Learner
Provides access to:
- Select courses that rely on intelligent agents
ASH Staff/Parents
Some ASH staff that are also parents used their @ash.nl email address as their parent contact information. This caused collisions in between the Parent Role and the Teacher role when users were created.
To remedy this, ASH staff usernames are set to the username portion of their @ash.nl email address. Staff sign in to Brightspace using Google SSO. The teacher username is only used in the event that SSO fails for a user.
Examples
- Name: Johan Myrthe
- ASH Email address: jmyrthe@ash.nl
- Brightspace teacher username: jmyrthe
- Parent username: jmyrthe@ash.nl
PowerQuery Plugin exports are scheduled through the Data Export Manager (DEM) in PowerSchool > My Templates. Create the template using the instructions under the Data Export Manager Setup section for each plugin in the documentation.
It is critical that the exact filename listed under Export File Name is used. The order in which the files is processed is important and governed by the filenames.
- Download plugins from this GIT Repo; each plugin is stored as a .zip file
- See list below for all plugins and their fuctions
- Install plugins throught the Plugin Management Dashboard:
- Start Page > System Administrator > System Settings > Plugin Management Dashboard
- If the plugin is already installed either choose to Update or Delete and reinstall using the .zip files
- Configure a template for automated export in Data Export Manager
- Start Page > System Administrator > Page and Data Management > Data Export Manager
Start Page > System Administrator > Page and Data Management > Data Export Manager
Each plugin needs to be configured to produce CSV files with the appropriate data, character set and column headers. Each plugin documents the structure and settings under the Data Export Manager Setup heading. See the README.md in each plugin directory for more details.
The basic settings are as follows:
- Select Columns to Export:
- Category: Show All
- Export From: NQ com.txoof.brightspace.table.area (see the DEM section in each readme)
- Select all of the fields:
- Remove the
TABLE.portion in the Labels used on Export for every column (highlighted in blue/red) and click Next
- On the following Select/Edit Records from NQ - com.txoof.brightspace.table.area screen click Next
- No filtering should be needed
- On the following Export Summary and Output Options screen set:
- Export File Name: See README.md for filename format (e.g.
1-Other.csv) - Line Delimiter:
CR-LF - Field Delimiter:
Comma - Character Set:
UTF-8
- Export File Name: See README.md for filename format (e.g.
- Click Save Template; see the
README.mdfor each plugin for specifics- Name:
1-Other-%d.csv Export - Description:
Updated: YYYY.MM.DD
- Name:
- Click Save as New
After templates are created, they need to be scheduled to run using the Scheduled Systems Templates interface in the Data Export Manager screens. Note: PowerSchool users can only schedule templates that they have created. It is not possible to see other users templates.
To schedule a template:
- Navigate to My Templates tab in the Data Export Manager screen.
- Click the calendar icon to the right of the template in the Actions column
- Set the schedule with the following settings:
- Schedule Active: ☑
- Days To Execute: Monday, Tuesday, Wednesday, Thrusday, Friday, Saturday, Sunday
- When to Execute: Any time between 12:00AM and 4:00AM
- Send Output To: PowerSchool Folder
- Path:
C:\Brightspace\Exports
Data is uploaded to Brightspace via the IPSIS interface. The data upload is managed from the PowerSchool Windows server using a scheduled task and is executed via a windows BATCH file.
The automated uploads should be scheduled at the following times:
- 06:00
The batch file depends on the following software:
- Win-SCP
- DO NOT use the "endurance" setting in Win-SCP; this is incompatible with the IPSIS SFTP server
- 7-Zip
Though it is possible to create ORG Units such as Schools, Departments, Templates and Courses by hand within the Brightspace ORG Unit editor interface, IPSIS cannot access these ORG Units. IPSIS maintains its own internal records of ORG Units created and removed based on the imports that are processed vai the IPSIS tool (both by SFTP and web upload).
This means that creating a School ORG Unit with code 999 manually through the ORG Unit editor cannot be referenced an enrollment .csv upload (e.g. 08_e_s_school.named_queries.xml).
To resolve this, a .csv must be uploaded via IPSIS that contains a row that references the new name and code (see below)
01-Other.csv
action,code,custom_code,department_code,end_date,is_active,name,offering_code,semester_code,start_date,template_code,type
UPDATE,999,,,,,New School,,,,,school
BrightSpace accepts imports via IPSIS. IPSIS expects flat zip files with at minimum 2 files: 1 CSV and a manifest.json V2.0.
Files are sent to IPSIS via SFTP. Find SFTP details within the platform here. NOTE: the username and password in the batch_upload.bat file need to be updated to match the credentials in the IPSIS configuration screen
- BS_Organization: Export BrightSpace CSVs 1-6
- 1: Other; 2: Departments; 3: Semesters; 4: Templates, 5: Offerings; 6: Sections
- BS_Users-Enrollments: Export BrightSpace CSVs 7-8 This will be deprecated after PowerSchool moves to Unlimited Contacts
- 7: Users, 8: Enrollments
- D2L_Users_Enrollments: Export Brightspace CSVs 7-8 This plugin will replace the BS_Users-Enrollments plugin after the move to Unlimited Contacts.
- 7: Users, 8: Enrollments
See this GitHub Gist for common problems and resolutions.
The Named Queries (NQ) within each plugin can be updated by editing the associated xxx.named_query.xml file. No changes to the files in the permissions_root should be necessary unless a new NQ is added. See the Basic PowerQuery Plugin Structure section below for more information.
After updating the NQ, it is necessary to update the version number in the plugin.xml file. PowerSchool will complain during the upgrade process if the version number remains the same or regresses. Always increment the version number.
PowerSchool SIS is very particular about the package structure of the plugin zip file. Use the ./package script included in this repository to repackage the script. The package script will generate a new plugin with the current version number and the current time specified in the zip filename.
Usage: package.sh PLUGIN_DIR
Example:
$ ./package.sh BS_OrganizationPlugins must be zipped such that the plugin.xml file is at the root of the structure with no top level folder. Multiple Named Queries (NQ) can be specified within one plugin. Each NQ needs to be placed in the queries_root directory with an associated permission_mappings.xml placed in the permissions_root directory.
The MessageKeys and web_root directories are not used in these plugins and can be ignored.
|
+-- plugin.xml
|
+-- queries_root
|
*-- partner.module1.named_queries.xml
*-- partner.module2.named_queries.xml
+-- permissions_root
|
*-- partner.module1.permission_mappings.xml
*-- partner.module2.permission_mappings.xml
+-- MessageKeys
|
*-- example-plugin-message-keys.US_en.properties
+-- web_root
|
*-- admin
|
*-- home.partner.content.footer.txt
Sets access permissions for this query plugin. See this blog post.
<permission_mappings>
<!--Anyone that has access to the following page can run this query-->
<permission name='/admin/home.html'>
<!--.../query/BASE_PLUGIN should be identical to `name` in named_queries.xml-->
<implies allow="post">/ws/schema/query/BASE_PLUGIN</implies>
</permission>
</permission_mappings>ERROR: Course Offerings.Parent org unit mapping not found
EXAMPLE IPSIS ERROR:
| IPSIS Field | Data |
| ParentSourcedId | Templ_3_ |
| RecordType | CourseOffering |
| Operation | Replace |
| SourcedId | co_3_FHA1IBSSSS2 |
Suggested Resolution: PowerSchool SIS COURSES.SCHED_DEPARTMENT field is empty; add a department. Search in Start Page > School Setup > Courses for the Course Number to verify the issue. The course number is the last portion of the SourceID generated by IPSIS: co_3_[FHA1IBSSSS2].
ERROR: User(s) could not be processed.ParentPortalDeleteRelationshipHandler - Unable to find user mapping, parents not updated
EXAMPLE IPSIS ERROR:
| SourcedId | P_577106 |
Suggested Resolution: This is likely due to a parent that does not exist and is being deactivated or deleted. The parent can be identified by the ID number. This can likely be ignored.
ERROR: User(s) could not be processed.No user mapping found for source system
EXAMPLE IPSIS ERROR:
| SourceSystem | 3 |
| IMIdentifier | P_577106 |
| RecordType | User |
| Operation | Delete |
| SourcedId | P_577106 |
Suggested Resolution: This is likely due to a parent that does not exist and is being deactivated or deleted. The parent can be identified by the ID number. Check the SourceID if this matches the previous error, this can likely be ignored.