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

	gci 'HKLM:\SOFTWARE\Microsoft\NET Framework Setup\NDP' | sort pschildname -des | foreach-object {$_.name; $_.GetValue("Version");}
	<# credit http://stackoverflow.com/questions/3487265/powershell-script-to-return-versions-of-net-framework-on-a-machine#comment3650817_3491193#>

view raw checkdotNET.ps1 hosted with ❤ by GitHub

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)

	<?xml version="1.0"?>
	<configuration>
	<startup useLegacyV2RuntimeActivationPolicy="true">
	<supportedRuntime version="v4.0.30319"/>
	<supportedRuntime version="v2.0.50727"/>
	</startup>
	</configuration>

view raw PowerShell.Exe.Config hosted with ❤ by GitHub

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

Heres the sample script for Naviance Student import:

	<# credit to http://blogs.technet.com/b/heyscriptingguy/archive/2012/12/04/use-oracle-odp-net-and-powershell-to-simplify-data-access.aspx#>
	#Just modify the Connection String here
	$conString="User Id=<your db query username>;Password=<your password>;Data Source=<your powershool db IP>/<db sid>"
	#Just modify the SQL here:
	$sqlString=@"
	/*
	Product: student auto import
	Data Type: SIS
	Entity: STUDENT
	Author:
	Modified:
	*/
	SELECT
	students.student_number as Student_ID,
	students.SCHED_YEAROFGRADUATION as Class_Year,
	students.Last_Name as Last_Name,
	students.SchoolID as Campus_ID,
	students.First_Name as First_Name,
	students.Gender as Gender,
	students.Ethnicity as Ethnicity,
	TO_CHAR(students.DOB, 'MM/DD/YYYY') as DOB,
	('FC' || students.student_number) as FC_User_Name, /* some method to create unique usernames for each student */
	'<some default password>' as FC_Password
	FROM
	PS.STUDENTS
	WHERE
	ps.STUDENTS.grade_level > '05' and ps.students.enroll_status = '0' and ps.students.SCHOOLID != '99'
	"@
	#location of your ODP.NET
	Add-Type -Path "F:\autoExport\odp.net\managed\common\Oracle.ManagedDataAccess.dll"
	function Get-OracleResultDa
	{
	param (
	[Parameter(Mandatory=$true)]
	[ValidateScript({$_ -match '\bdata source\b'})]
	[string]$conString,
	[ValidateScript({$_ -match '\bselect\b'})]
	[Parameter(Mandatory=$true)]
	[string]$sqlString
	)
	$resultSet=@()
	try {
	$con = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($conString)
	$cmd=$con.CreateCommand()
	$cmd.CommandText= $sqlString
	$da=New-Object Oracle.ManagedDataAccess.Client.OracleDataAdapter($cmd);
	$resultSet=New-Object System.Data.DataTable
	[void]$da.fill($resultSet)
	} catch {
	Write-Error ($_.Exception.ToString())
	} finally {
	if ($con.State -eq 'Open') { $con.close() }
	}
	$resultSet
	}
	#this is the line where specify the location for the export and you have to specify the objects you want after the 1st pipe
	Get-OracleResultDa $conString $sqlString | Select-Object Student_ID,Class_Year,Last_Name,Campus_ID,First_Name,Gender,Ethnicity,DOB,FC_User_Name,FC_Password | Export-Csv -NoTypeInformation F:\autoExport\exports\naviance_students.csv

view raw queryFor_NavianceStudents.ps1 hosted with ❤ by GitHub

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!