Hosted and SaaS customers who provide a nightly extract of SIS (and/or HR) data to Terra Dotta for their systems integration must provision a text file (or text files) and schedule the upload of the file to Terra Dotta via SFTP (SSH). The SSH account will be provided through the client technical implementation case.
File Format Specifications
- All core required fields present and populated. (In deferred-fetch, required core information and custom information are sent in separate files keyed on UUUID.)
- Tab-delimited (tsv) with any empty/null columns delimited with the appropriate number of tabs to maintain column alignment.
- Double quotes may be used as field qualifiers but are not preferred. If included, qualifiers must be used for all fields including the header row, and the qualifier cannot be present in any field's value.
- A header row with column descriptors is required. Headers should not contain spaces or special characters except for underscores.
- Header row names should not use any SQL reserved words. (A list of these words can be found here http://msdn.microsoft.com/en-us/library/ms189822.aspx)
- Field values must be trimmed. No space or other padding of field values is permitted.
- Fields are single-valued. Any multivalued data will be imported as single values (will not be parsed). Core fields may not contain multiple values.
- Single record per user. (UUUIDs may not be duplicated in any single file)
- Row delimiter can be either LF or CRLF but must be consistent.
Data Mappings Template
The client must provide a data layout for the file, to be approved by Terra Dotta through the integration project (technical integration case).
The functional system administrator should work with institutional IT to complete the mappings template and provide it to Terra Dotta via the case. Terra Dotta will use this template to set up the data integration and create the mappings for the custom applicant parameters the client has decided to provision. Please note that those clients integrating for ISSS and the SEVIS Module must provide certain data elements from their campus data systems. These requirements are reflected below in the SEVIS Module specific Mappings Template worksheet.
The layout should follow the format of this TD SIS Interface Data Layout EXAMPLE Worksheet. (Be sure to review the SAMPLE worksheet first, as it explains the formatting.)The functional system administrator should work with institutional IT to complete the mappings template and provide it to Terra Dotta via the case. Terra Dotta will use this template to set up the data integration and create the mappings for the custom applicant parameters the client has decided to provision. Please note that those clients integrating for ISSS and the SEVIS Module must provide certain data elements from their campus data systems. These requirements are reflected below in the SEVIS Module specific Mappings Template worksheet.
You can download a blank worksheet here: https://university.terradotta.com/?go=sistemplate (Please allow macros for the workbook.)
You can download a blank SEVIS Module worksheet here: https://university.terradotta.com/?go=sistemplateisss (Please allow macros for the workbook.)
To complete the initial integration, the client must provision a sample file containing at least a few test records and the finalized attribute set to the SSH directory.
File Naming Conventions
StandardClients delivering a complete applicant data source should name the file:
sis_user_info.txt - the student/applicant file
hr_user_info_core.txt - the faculty/staff file
- OR -
sis_hr_user_info.txt - if the file contains combined SIS and HR user records, with a column flagging HR users
Deferred-Fetch
Clients delivering a core-only applicant data source should name the core file:
sis_user_info_core.txt - if the file contains core information for students only
sis_hr_user_info_core.txt - if the file contains combined SIS and HR user records, with a column flagging HR users
The custom file should be named sis_user_info_custom.txt.
In developing the core file feed the client must include at minimum the required core parameters of First Name, Last Name, Email, and UUUID. It is preferable that they also include, in the core file, any other core parameter elements that they wish to have available in the applicant profile (Middle Name, DOB, Gender, Confidentiality Indicator)
The custom data file should not include First Name, Last Name, or Email but should always include the UUUID column. If the additional core parameter elements were not included in the the core file, they may be included in the custom file.
Field Length/Data Type/ Requirements
Core Data Fields
PLEASE DO NOT upload test feed files containing personal data to the case system or send files via email, as these are not fully secure channels of communication. Instead, please use the SFTP site that has been provisioned for your integration project.
Further instructions on feed scheduling and other concerns will be handled through the implementation case.
| Field | Type | Max Length | Requirements |
| SIS_User_First_Name | Unicode string[DT_WSTR] (nvarchar) | 50 | Single-valued; data population require |
| SIS_User_Last_Name | Unicode string[DT_WSTR] (nvarchar) | 50 | Single-valued; data population required |
| SIS_User_Middle_Name | Unicode string[DT_WSTR] (nvarchar) | 50 | Single-valued |
| SIS_User_Name | Unicode string[DT_WSTR] (nvarchar) | 250 | Unique, Universal, Unchanging ID |
| SIS_User_Email | Unicode string[DT_WSTR] (nvarchar) | 250 | Single-valued; data population required |
| SIS_User_DOB | Unicode string[DT_WSTR] (datetime) | * | * A variety of date masks are acceptable. Preferred: dd-MON-yyyy or yyyy-mm-dd |
| SIS_User_Sex | Unicode string[DT_WSTR] (char) | 1 | M or F expected; O or NULL for other is valid |
| SIS_User_Confidentiality_Ind | Unicode string[DT_WSTR] (char) | 1 | Optional. Y or N data preferred. SA can accept other single-character values if necessary. See Appendix A for more information about this special field.
|
Custom Mapped Applicant Parameters
| Field | Type | Max Length |
| All Custom fields | Unicode string[DT_WSTR] (nvarchar) | 500 |
Address Fields
The following are the available fields and their lengths. An address block need not contain all of the available data fields.
The following are the available fields and their lengths. An address block need not contain all of the available data fields.
| Field | Type | Max Length |
| Address Line 1 | Unicode string[DT_WSTR] (nvarchar) | 100 |
| Address Line 2 | Unicode string[DT_WSTR] (nvarchar) | 100 |
| Address Line 3 | Unicode string[DT_WSTR] (nvarchar) | 100 |
| City | Unicode string[DT_WSTR] (nvarchar) | 50 |
| Province | Unicode string[DT_WSTR] (nvarchar) | 100 |
| State | Unicode string[DT_WSTR] (nvarchar) | 2 |
| Zip | Unicode string[DT_WSTR] (nvarchar) | 50 |
| Country | Unicode string[DT_WSTR] (nvarchar) | 100 |
| Phone | Unicode string[DT_WSTR] (nvarchar) | 20 |
| Fax | Unicode string[DT_WSTR] (nvarchar) | 20 |
| Mobile | Unicode string[DT_WSTR] (nvarchar) | 20 |
| Addressee Name | Unicode string[DT_WSTR] (nvarchar) | 100 |
| Addressee Relationship | Unicode string[DT_WSTR] (nvarchar) | 100 |
PLEASE DO NOT upload test feed files containing personal data to the case system or send files via email, as these are not fully secure channels of communication. Instead, please use the SFTP site that has been provisioned for your integration project.
Further instructions on feed scheduling and other concerns will be handled through the implementation case.