Structured Query Language (SQL) with WinSVC

Use SQL with WinSVC to securely transfer course data between your SIS and Naviance if your SIS does not offer a Secure File Transfer Protocol (SFTP) option.

Currently, Naviance only supports this option for course data files.

Ensure the person setting up SQL with WinSVC:

• Has a technical understanding of SQL.
• Can correlate the data fields from Naviance to your SIS.

Prepare to Use SQL with WinSVC

1. Review the course catalog, course mapping, and student course data import fields to determine the data you will export from your SIS.
2. Match the Naviance Course Catalog, Course Mapping, and Student Course Data import fields to your SIS fields and create your data export files. You must save the data files as a CSV or tab-delimited file, less than 10MB. Zip the file if needed.

3. Use the data import feature in Naviance to map fields and set the column order for each import type.

   

   You must map fields and set column orders again if you add data fields to your file or change the column order.

Set Up SQL with WinSVC to Export Data

1. Contact Naviance Support to obtain SFTP credentials.
2. Use Microsoft SQL Server Management Studio to create SQL statements and generate a CSV or tab-delimited batch file containing course catalog, course mapping, or student course data. Follow the Naviance recommendations when naming your files.
   • Enter sqlcmd to run the SQL and output to a file.
   • Enter sed to delete the extra line added by sqlcmd.
   • Use a zip file generator to zip the file.
   • Copy the file to a secure file directory location using the UNC path.
3. Use the task scheduler to set up a job that runs the batch file at a frequency that works for your district or school.

4. Set up WinSCP on your district or school server and configure it to send files to Naviance via SFTP.

5. Set up another batch file and scheduled a task to send the file to Naviance at a time that is best for your district or school.

Set Up SQL with WinSVC to Import Data

1. Contact Naviance Support to obtain SFTP credentials.

2. To set up SQL to import course data from Naviance:

   • Enter the file name from Naviance.
     • For districts: NavianceStudentCoursePlansNameofDistrict_YearStart-YearEnd.csv
     • For schools: NavianceStudentCoursePlansNameofSchool_YearStart-YearEnd.csv
3. Access the exported CSV file from the Naviance SFTP using your Naviance SFTP credentials.
4. Bulk load the CSV file into a template table using Student Number and Course ID.

5. Complete a bulk insert into the appropriate tables.

   

   Since the file includes all course requests, Naviance recommends verifying if course requests already exist in your SIS. If yes, do not import course requests again.
6. Set up a scheduled task to send the file to your SIS at a time best for your district or school.