Transportation Extract Configuration

Tool Search: Data Extract Utility

The information within this guide outlines how to use the Data Extract Utility tool to create a data extract to be used with a transportation vendor. The secure FTP credentials for this process must be obtained from your transportation vendor. These credentials usually include username, password, server (IP Address or server name), port number, and sometimes the path directory.

Creating a New Extract

  1. Select the New icon. A Detail Information section will appear.
  2. Enter the Extract Instance Name.
  3. Choose the other necessary items for adding the extract by populating and marking the appropriate fields, as defined above.
  4. Click the Save icon when finished. You can modify the extract to determine when it should be generated. 

At this time, a user can select the available buttons to test the extract, test the connection to the database or run the extract by selecting one of the appropriate buttons. Extracts will display in a new window in the selected format. 

  • The Test Extract button generates the containing information requested by SQL or Ad hoc. Data is not written to the database or stored in any location.
  • The Test Connection button checks the connection to the entered server by attempting to write a 1-byte file to the location setup. When it is successful, a pop-up displays indicating the connection is good; if it is not successful, it provides the reason the connection failed.
  • The Run Extract button runs and generates the extract, sends and stores the file where needed (in case of an issue when it is an auto-run extract).

Extracting the Transportation Data

screenshot of the transporation extract setup in the data extract utiility

In the SQL/Prism textbox, enter “EXEC get_transportation” or any of the formats listed in the Examples.

This stored procedure contains two parameters that can be passed into it:

  1. Date – If no date is passed, the query will pull all active data for today’s date. If a date is added, it will pull all active data for that date. Example: If prior to the first day of school a district wishes to extract active data for the first day of school the query can be modified. i.e. EXEC get_transportation @date = ‘07/15/2023’ Having the date entered will pull student’s and data that are active as of that date.
  2. Include SSN – If no value is passed, the query will include both the students SSN and the primary contacts SSN. If a value of 0 is passed, the query will not return SSN values. Those fields will be null. i.e EXEC get_transportation @includeSSN = 0. A value of 1 will include SSN’s and a value of 0 will return no SSN’s.
    Note: The columns will still exist, but that data may not.

A single parameter can be passed, both parameters can be passed, or no parameters can be passed.

Examples

ExampleDescription

EXEC get_transportation @date = ‘07/15/2023', @includeSSN = 0

Will extract all active data as of 07/15/2023 and not include SSN’s.

EXEC get_transportation @includeSSN = 0


Will extract all active data for today’s date and no SSN’s.

EXEC get_transportation @includeSSN = 1


Will extract all active data for today’s date and also include SSN’s.

EXEC get_transportation @date = ‘07/15/2023'


Will extract all active data for 07/15/2023 and SSN’s.

EXEC get_transportation


Will extract all active data for today’s date and SSN’s.

Data Fields

The data extracted will include the following fields in this order:

FieldDescription
p.studentnumber AS StudentDistrictIDLocal student number
CASE WHEN @includeSSN = 1 THEN i.ssn ELSE NULL END AS StudentSSNStudent SSN
p.stateID AS StudentStateIDStudent state ID
CASE WHEN e.enddate < GETDATE() THEN 0 ELSE 1 END StudentEnrollmentStatus0 = No longer enrolled student.
1 = Actively enrolled student.
s.number AS StudentSiteCodeSchool number
d.number AS StudentDistNumDistrict number
COALESCE(rm.[name], 'NHR') AS StudentHomeroomHomeroom room number
i4.lastname AS StudentHomeRoomTeacherLastnameHomeroom teacher last name
i4.firstname AS StudentHomeRoomTeacherFirstnameHomeroom teacher first name
i.lastname AS StudentLastNameStudent last name
i.firstname AS StudentFirstNameStudent first name
i.middlename AS StudentMiddleNameStudent middle name
LTRIM(RTRIM(CONVERT(CHAR(24),i.birthdate,101))) AS StudentBirthdateStudent birthdate
i.gender AS StudentGenderStudent gender
e.grade AS StudentGradeLevelStudent grade
i.raceEthnicity AS StudentRaceStudent race
t.transportationcode AS StudentTransportationCodeStudent current transportation code
c1.email As StudentEmailStudent email
i2.lastname AS StudentPrimaryContactLastNamePrimary contact last name
i2.firstname AS StudentPrimaryContactFirstNamePrimary contact first name
r.[name] AS StudentPrimaryContactRelationshipRelationship to student
CASE WHEN @includeSSN = 1 THEN i2.ssn ELSE NULL END AS StudentPrimaryContactSSNPrimary contact SSN
c2.email As StudentPrimaryContactEmailPrimary contact email
c2.workphone As StudentPrimaryWorkPhonePrimary contact work phone
a.county AS StudentPrimaryContactCountyPrimary contact county
CASE WHEN a.postOfficeBox = 1 THEN 'P.O. Box '+a.number-
ELSE a.number+' '+COALESCE(a.prefix+' ','')+COALESCE(a.street+' ','')+COALESCE(a.tag+' ','')+COALESCE(a.dir+' ','')+-
COALESCE(CASE WHEN COALESCE(a.apt,'') <> '' AND LEFT(a.apt,1)<>'#' THEN '#'+a.apt END,'') END AS StudentPrimaryContactStreetAddressPrimary address
a.city AS StudentPrimaryContactCityPrimary city
a.state AS StudentPrimaryContactStatePrimary state
a.zip AS StudentPrimaryContactZipPrimary zip code
COALESCE(h.phone,'') AS StudentPrimaryContactPhoneNumberPrimary home phone