SASIxp and SQL Server express

SASIxp is a widely used and one of the most popular Student Information System by Pearson School Systems.

I have been working with SASI for the last 10+ years. It's very good at what it is supposed to do, but, there are inherent limitations because of SASI using dBase files for data storage and not a real RDBMS.

We are always in need of getting the Data out of SASI and use it for input of other applications that need SASI data, or for running various customized reports.

One way to maximize your data usability is to extract the required pieces of information from SASI and load it up to a real RDBMS database. Thereafter, the data can be used in any way we want.

I will show the basics of getting SASI data loaded to Sql Server database.

I prefer to use Microsoft Sql Server in my example. There is a freely available SQL Server Express version available for download from Microsoft. This version has some limitations, like maximum database size limit of 4GB, but, it's perfect for our purposes as 4 gigs is more than what a 15-20K student population school system will need.

So, let's get started ...

First, download and install the Sql Server Express. Also, install the SQL Server Management studio express.

Next, login to the SQL Server in Management studio, create a database called SASI locally, you can use the system defaults for the purpose of creating the database.

Next step is to create a Linked Server to access SASI dBase files directly as normal database tables. Use the following script to create a Linked server:

EXEC sp_addlinkedserver
'sasifiles',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'D:\SASIxp\Datafiles',
NULL,
'dBase IV'
GO

exec sp_addlinkedsrvlogin
@rmtsrvname = 'sasifiles',
@useself = false,
@locallogin = NULL,
@rmtuser = NULL,
@rmtpassword = NULL
GO

Once you have the Linked Server created, you can run simple queries against your dBase files direcly from Managemnet Studio's query window.

For example, if you want to query the ASTU (student) table for School year 2008-2009 and for school code 101, you would run the following query:

select * from openquery(sasifiles, 'select * from ASTU8101')

As you can see, you SASI data just got much more accessible.

In the next post, I will show you how to get basic demographic and attendance data from SASI to SQL Server tables and run some Enrollment/Attendance reports right in your query window in Management Studio.