PowerSchool Integration

In late 2019, when we upgraded to PS 19, that broke the automation that updates BBC nightly with parent + staff contact info. This is what BBC support and I did to fix it.

Section 1: Define the SFTP connector

1. System -> System Settings -> Plugin Management Configuration -> Remote Connection Manager -> Remote Connection Manager -> Create Connection

2. Fill in fields as shown:

(Password is in the breadcrumbs document.)

Then click Test Connection and accept the fingerprint the first time.

Section 2: Define and schedule the student data export template

1. System -> Page and Data Management -> Data Export Manager, created a template called BlackboardStudentUpdate which exports from category "Student Information" and Export From is set to "Students"

2. Fields and labels:

3. Columns to export:

4. Output options:

Click Save Template.

5. Then click the little calendar icon under Actions.

Initially, this did not show for me, and we had to edit the "Data Export Manager Export/Edit Access" role (which was already assigned to me):

Once that's done, schedule it accordingly:

6. Press "play" icon to test. To verify that it worked, log in to Blackboard and go to Recipients -> Import Data -> View import history. Many warnings are normal, has to do with phone field mappings.

Section 3: Create a plugin that uses a PowerQuery and schedule that for the STAFF data export

This is pretty advanced stuff. I got help from a reference document (attached below), as well as from members of the NH PowerSchool Users Group, before I was able to get this working. The plugin itself is also attached here as a backup.

1. Create plugin. This is its file structure before zipping:

These are the contents of those three files:

plugin.xml:

<?xml version="1.0" encoding="UTF-8"?>

<plugin xmlns="http://plugin.powerschool.pearson.com"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://plugin.powerschool.pearson.com plugin.xsd"

name="crossroadsacademy.bbcStaffExport"

version="1.0"

description="Export for Blackboard Connect for Crossroads Staff">

<publisher name="mbates">

<contact email="marion.bates@crossroadsacademy.org"/>

</publisher>

</plugin>

bbcStaffExport.permission_mappings.xml:

<permission_mappings>

<permission name='/admin/home.html'>

<implies>/ws/schema/query/crossroadsacademy.bbcStaffExport</implies>

</permission>

</permission_mappings>

bbcStaffExport.named_queries.xml:

<queries>

<query name="crossroadsacademy.bbcStaffExport" coreTable="schoolstaff" flattened="true" tags="Blackboard">

<summary>Export for Blackboard Connect for Crossroads Staff</summary>

<description>Export for Blackboard Connect for Crossroads Staff</description>

<columns>

<column column="schoolstaff.status">Status</column>

<column column="schoolstaff.schoolid">Institution</column>

<column column="schoolstaff.id">ReferenceCode</column>

<column column="users.First_Name">FirstName</column>

<column column="users.Last_Name">LastName</column>

<column column="userscorefields.gender">Gender</column>

<column column="users.email_addr">EmailAddress</column>

<column column="users.home_phone">HomePhone</column>

<column column="u_staffcontacts.home_email">EmailAddressAlt</column>

<column column="u_staffcontacts.mobilephone">Mobilephone</column>

</columns>

<sql>

<![CDATA[

SELECT ss.status AS Status,

ss.schoolid AS Institution,

ss.id AS ReferenceCode,

u.first_name AS FirstName,

u.last_name AS LastName,

ucf.gender AS Gender,

u.email_addr AS emailAddress,

u.home_phone AS HomePhone,

usc.home_email AS emailAddressAlt,

usc.mobilephone AS mobilephone

FROM SchoolStaff ss

INNER JOIN users u ON ss.users_dcid=u.dcid

INNER JOIN userscorefields ucf ON u.dcid=ucf.usersdcid

INNER JOIN u_staffcontacts usc ON ss.dcid=usc.schoolstaffdcid

WHERE ss.schoolid=26885 AND ss.status=1

ORDER BY u.lastfirst

]]>

</sql>

</query>

</queries>

When you zip it, do it like Terry said: "I always zip my files by selecting the permissions_root folder, the plugin.xml file and the queries_root folder (use the command or shift keys) and right click and select compress these 3 items. The zip file it creates should install at that point." Install and enable the plugin.

2. System -> Page and Data Management -> Data Export Manager, created a template called MB BBC staff export which exports from category "Staff Information" and Export From is set to "Export for Blackboard Connect for Crossroads Staff" (the plugin from step 1.) The fields and labels should fill in automatically because they're predefined by the plugin.

3. Do nothing on the column filters page, and set the Output Options like so:

4. Schedule it to run 5 minutes before or after the student one, and use the same settings as for student data.