Loading...

Tech Talk Live Blog

Connecting PowerShell to PowerSchool

Shawn Mellinger


Currently, there are many different products that connect to Active Directory (AD) to get information on students. This is often used for authentication, mobile device management, and user configuration. So keeping student information in Active Directory up to date is critical. It is difficult to keep track of these changes manually, and reports will only tell you what needs to be changed, but does not make those changes for you. Also, I prefer automating changes as it takes away the possibility for errors made during updates and greatly reduces the amount of time spent on AD updates.

I have already written and deployed a PowerShell script to automate our employee information. It connects to the SQL database of our employee information and can create, modify, or remove users based on changes in the database. This can be used as the basis for a script to sync student information to AD as well. However, we and many of our schools use PowerSchool, which uses an Oracle database. I will freely admit that I am less familiar with Oracle databases, although I know enough that I can get the information out that I need.

The first and frankly largest challenge I faced was connecting to the Oracle database. Connecting to a SQL database in PowerShell was not very difficult, I could use the System.Data.SqlClient .NET Data Provider and use the SqlConnection, SqlDataAdapter, and SqlCommand types. I prefer this because it is portable and can run anywhere I put the script without requiring installation of any software. Many suggestions for connecting to Oracle databases in PowerShell recommended downloading the Oracle Data Access Components and using ODP.NET to connect. I tried this method and was able to successfully connect, but do not like the need to install a large package or copy files to a server before I run the script.

I did some more digging and found another method based off a stackoverflow post. This referenced that Oracle has published the Oracle.ManagedDataAccess ODP.NET Managed Driver to NuGet. This means that I could check to see if the package was already installed, and if it was not installed then automatically download and install the package. This provides the portability that I desired.

With that in mind I used the following code to check if the ManagedDataAccess was downloaded. If it is not downloaded, then download, install, and add the dll Type.

 

# Load Oracle.ManagedDataAccess. Download the package and install if it does not already exist

$version = ‘18.3.0’

 

try {

 

if (! $(Test-Path “.\NugetPackages\Oracle.ManagedDataAccess.$version\lib\net40\Oracle.ManagedDataAccess.dll”))

{

$ManagedDataAccess = Install-Package Oracle.ManagedDataAccess -Destination “.\NugetPackages” -Force -Source ‘https://www.nuget.org/api/v2’ -ProviderName NuGet -RequiredVersion $version -ErrorAction SilentlyContinue

}

Add-Type -Path “.\NugetPackages\Oracle.ManagedDataAccess.$version\lib\net40\Oracle.ManagedDataAccess.dll”

}

catch

{

Write-Error (“Error loading ManagedDataAccess: {0}`n{1}” -f $_.Exception.ToString())

}

 

Once that is added I can set the connection string and SQL statement that I will pass into the function.

 

$ConnectionString=”User Id=Username;Password=Password;Data Source=myserver:1521/PSPRODDB”

$SQL=”Your SQL statement here”

 

I can then write my function to connect using those parameters, execute the query, and return a dataset of student information based off the query.

 

[OracleConnection]$conn = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($ConnectionString)

[OracleDataAdapter]$da = new-object Oracle.ManagedDataAccess.Client.OracleDataAdapter

[OracleCommand]$cmd = $conn.CreateCommand()

$cmd.CommandText = $SQL

$da.SelectCommand = $cmd

[DataSet] $ds = new-object DataSet

 

Try {

 

$conn.Open()

$da.Fill($ds)

 

}

Catch {

$ErrorMessage = $_.Exception.Message

$FailedItem = $_.Exception.GetType().FullName

Write-Error $ErrorMessage $FailedItem

}

 

Finally {

 

$conn.Dispose()

 

If ($conn.State -ne “Closed”) {

$conn.close()

}

 

}

 

With the resulting dataset I can compare the information in PowerSchool against the information I have for those students in Active Directory. This provides an automated update that I can run on a scheduled basis and check over the logs at my leisure.

Tech Talk Live Blog Comment Guidelines:

One of our main goals at Tech Talk Live is to build a community. It is our hope that this blog can be a forum for discussion around our content. We see commenting as an integral part of this community. It allows everyone to participate, contribute, connect, and share relevant personal experience that adds value to the conversation. Respect counts. We believe you can disagree without being disagreeable. Please refrain from personal attacks, name calling, libel/defamation, hate speech, discriminatory or obscene/profane language, etc. Comments should keep to the topic at hand, and not be promotional or commercial in nature. Please do not link to personal blog posts, websites, or social media accounts that are irrelevant to the conversation. This is considered self-promotion. We welcome links that help further the conversation and reserve the right to delete those we deem unnecessary. The appearance of external links on this site does not constitute official endorsement on behalf of Tech Talk Live or Lancaster-Lebanon Intermediate Unit 13. You are solely responsible for the content that you post – please use your best judgment. We reserve the right to remove posts that do not follow these guidelines.

Leave a Reply

Your email address will not be published. Required fields are marked *

CONTACT

Tech Talk Live is the only conference of its kind in the region specifically designed for IT pros in education.


techtalklive@iu13.org
1020 New Holland Avenue, Lancaster, PA 17601

(717) 606-1770