PowerSchool Scheduled data export, minimal install

There are several reasons we need to export data from PowerSchool and many times the built-in AutoSend feature will not do.  The AutoSend feature can only export data on active students for either the district or the school, sometimes we need a more specific set of kids as well as the fields that we can pull.

This post will focus on a method that should be able to be run on any Windows 2008 r2 server and above without an extra installs.  It uses PowerShell, .NET 4 and ODP.NET.  Also note that this is what you use once you have your sql query and you just want to set it to run on a schedule to get a .csv or some other delimited file.

I chose this method instead of others because PowerSchool has to be installed on Windows Servers and quite frankly we're a Windows Shop when it comes to servers.  You can run this directly on the server that runs PowerSchool but I chose to use a server we use to warehouse, scrub and analyze a lot of our data.  We also have lots of other servers that run specific applications for transportation/routing, food service, library systems and etc. and all of these are Windows boxes that have the base requirements.

Once I decided this would be my best method I used this post on the Hey, Scripting Guy! Blog to build my basic .ps1 scripts using ODP.NET.  I wasn't as concerned with speed but more the 'simplify' part.  So in summary of what that post talks about is:

1. ODP.NET  from Oracle 
2. Make sure you have .Net 4
3. the ability to run .Net 4 in PowerShell
• Configure PowerShell to use newer .net.  To do this, you need to create a config file, named PowerShell.Exe.Config according to (http://tfl09.blogspot.com/2010/08/using-newer-versions-of-net-with.html)
4. bypassing the block (run PowerShell as Admin):  Set-ExecutionPolicy RemoteSigned
5. run this script by:  .\query1.ps1 

So get ODP.NET:

1. create a folder called c:/autoexport or somewhere else on your Windows 7+ or 2008r2+ box.
2. download the proper ODP.NET from Oracle 
3. extract that in your new folder under another folder called c:/autoexport/odp

Check if you have .NET 4

Configure PowerShell to use newer .NET.  To do this, you need to create a config file, named PowerShell.Exe.Config according to (http://tfl09.blogspot.com/2010/08/using-newer-versions-of-net-with.html),  then put this in the same folder as powershell.exe (%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe)

then bypassing the block (run PowerShell as Admin):
 Set-ExecutionPolicy RemoteSigned

Heres the sample script for Naviance Student import:
To run this in PowerShell you need to do the ol' 'dot slash' in front of the actual script:  ./queryFor_NavianceStudents.ps1

now that you have your delimited export file, you can use PowerShell and WinSCP to upload it.  I'll probably write that part up too soon.

Wrap those up in a .bat file then schedule that with task scheduler!