To set up automated data exports from PowerSchool via SFTP, your company will need to handle two key parts: providing the district administrator with a secure SFTP destination and giving them detailed instructions for configuring their PowerSchool instance.
Here is a step-by-step breakdown of how your team would facilitate this process.
Your team's responsibilities
1. Set up a secure SFTP endpoint
- Establish a server: Your development or IT operations team will need to set up a secure SFTP server or find a managed file transfer service. This server will receive the rostering files from the district.
- Create unique credentials: For each district, you must generate a unique set of SFTP credentials, including a username and password. You may also need to provide a host address and a port number.
- Secure the connection: Best practice is to require the district's PowerSchool instance to accept a host key to establish a trusted connection.
- Provide credentials to the district: Communicate these unique credentials securely to the district's PowerSchool administrator.
2. Define the file format specifications
- Define the data fields: You must provide clear and precise specifications for what data your system needs. This includes:
- Required files: The names of the CSV files your system will process (e.g., users.csv, classes.csv, enrollments.csv).
- Column headers: The exact names of the column headers your system expects in each file (e.g., Student_ID, First_Name, Last_Name, SchoolID, SectionID). Your documentation should specify if your system can handle the PowerSchool default of prepending the table name to the column header (e.g., CC.ID instead of ID).
- Data requirements: Specify any data formatting needs, such as a date format or whether a field must contain a unique value.
- Create a configuration guide: Develop a clear, step-by-step document that walks the administrator through the process.
The PowerSchool administrator's process
1. Configure the SFTP connection
- Log in: The administrator logs in to the PowerSchool admin interface.
- Navigate: They go to System > System Settings > Plugin Management Configuration > Remote Connection Manager.
- Create a connection: The administrator creates a new connection using the SFTP credentials you provide. They will enter the hostname, port, username, password, and test the connection.
- Accept the host key: During the test connection, PowerSchool will prompt the administrator to accept the server's host key, completing the secure connection.
2. Configure and schedule the data exports
- Navigate: The administrator goes to Importing & Exporting > Data Export Manager or AutoSend Setup.
- Create export templates: They create separate export templates for each data type your system requires (users, classes, enrollments, etc.).
- Define data fields: For each template, the administrator selects the specific data source and fields that match your format specifications.
- Schedule the exports: The administrator creates a scheduled task for each template. They will specify:
- Frequency: Usually nightly.
- Time: Often outside of regular school hours (e.g., 3:00 AM) to avoid disrupting performance.
- Destination: They will select the SFTP connection that was configured in the previous step.
- Run a test: As part of the initial setup, the administrator may run the exports manually to ensure the process works correctly.
Example file specifications for your guide
The guide you provide to the district could include a section like this:
File Format Specifications
- users.csv
- district_user_id (Unique ID for the user)
- first_name
- last_name
- role (Teacher or Student)
- classes.csv
- class_id (Unique ID for the class)
- class_name
- school_id
- course_id
- teacher_id
- enrollments.csv
- class_id
- student_id
- Notes:
- The school_id should correspond to the ID from your schools.csv file.
- All files should be in UTF-8 encoding.
- Files should be sent within a single .zip file for each daily transfer.
- Other integrations via API are available based on agreement.