Behind the Blackboard! Using SIS to Change Record DSK - Behind the Blackboard Skip Navigation
Download PDF  Icon Download PDF    Print article

Using SIS to Change Record DSK

Date Published: Jul 31,2020


CategoryPlanned First Fix Release:Functioning as Designed; Product:Data Management & Integrations; Version:Learn 9.1, 3900.0.0,Learn 9.1 Q4 2019 (3800.0.0),Learn 9.1 Q2 2017 (3200.0.0),Learn 9.1 Q4 2017 (3300.0.0),Learn 9.1 Q2 2018 (3400.0.0),Learn 9.1 Q4 2018 (3500.0.0),Learn 9.1 Q2 2019 (3700.0.0),Learn 9.1 Q4 2016 (3100.0.0-rel.107+401e,SaaS,Learn 9.1 Q4 2015 (9.1.201510.1171621),Learn 9.1 Q2 2016 (3000.1.0-rel.52+991d)
Article No.: 000057507
Product:
Blackboard Learn
Release:
9.1;SaaS

Introduction:

Disclaimer:

Clients are responsible for their own data. Blackboard Inc. Enterprise Support does not provide assistance with generating SIS feed files and will not run them on the client's behalf. Blackboard Inc. is not responsible for loss of data which occurs as a result of the use or misuse of SIS feed files.

Contents:

  1. Important things to note
  2. Creating an SIS integration
  3. Gathering Data for Feed files
    1. Database
    2. REST API URLs
    3. ICM or Custom Queries/Reports
  4. Generate a Feed File
  5. Upload the Feed File
  6. Common Issues
  7. Resources

A. Important things to note:

1. SIS feed files use EXTERNAL_PERSON_KEY and/or EXTERNAL_COURSE_KEY values; these values may be different from the USERNAME/COURSE_ID and are not typically displayed in the GUI. In the database, these columns are listed as COURSE_MAIN.BATCH_UID or USERS.BATCH_UID respectively. These values are directly represented in their corresponding REST API calls as "externalId".

2. When handling active Merged Course Enrollment records the corresponding Parent Enrollment record should be omitted. In the event that both enrollments are included, whichever enrollment comes later in the feed file will take precedence. It is not possible for a parent enrollment to have a different DSK than it’s active child enrollment nor may a parent enrollment be disabled if the child enrollment is enabled.

 

B. Create an SIS Snapshot Integration 

NOTE: If have an existing Integration which is in 'Active' status, you may skip this step. 
  • Navigate to [ System Admin > Data Integration  Student Information System Integrations ]
  • Click on the "Create Integration" drop down on that screen and select 'Snapshot Flat File'
  • Fill in these settings to the following screen:
    • Name = < An easily identifiable name of your choosing >
    • Integration Status = Active
    • Feed File Delimiter = < Your Personal Preference** >
** This will need to match the data in your feed files, Pipe and Comma are the most commonly used delimiters. To check or change any of the settings from step C. at any time, simply click on the Chevron next to the Integration's name and choose the "Edit" option.

C. Gathering Data for Feed files

If your institution has OpenDb ( for Managed Hosting ) or DDA ( for SaaS ) you can query your database for a list of the relevant EXTERNAL_PERSON_KEY/EXTERNAL_COURSE_KEY values. If your institution does not have either of those, EXTERNAL_PERSON_KEY/EXTERNAL_COURSE_KEY may be obtained via REST API URLs. If you would like to obtain OpenDb or DDA functionality, please reach out to your Account Manager.

1. Database

Blackboard Support does not provide assistance with or troubleshooting for Custom Query Design. Most of the commonly used data for feed files comes from the COURSE_MAIN, USERS, and COURSE_USERS tables and the Open Database Schema for your version of Learn is accessible at https://help.blackboard.com/Learn/Administrator/Hosting/Databases/Open_Database_Schema. Some common examples of queries to locate relevant data are provided, 'as is', for your convenience.
 

Courses

Users

Enrollments

Example Query:SELECT
 cm.batch_uid as EXTERNAL_COURSE_KEY,
 ds.batch_uid as DATA_SOURCE_KEY
FROM course_main cm
LEFT JOIN data_source ds ON cm.data_src_pk1 = ds.pk1
WHERE
cm.course_id like '%.202010'
SELECT 
u.batch_uid,
ds.batch_uid as DATA_SOURCE_KEY
FROM users u
LEFT JOIN data_source ds ON u.data_src_pk1 = ds.pk1
WHERE
u.user_id like '%test_user%'
SELECT
cm.batch_uid as EXTERNAL_COURSE_KEY,
u.batch_uid as EXTERNAL_PERSON_KEY,
ds.batch_uid as DATA_SOURCE_KEY
From course_users cu
LEFT JOIN users u ON cu.users_pk1 = u.pk1
LEFT JOIN course_main cm ON cu.crsmain_pk1 = cm.pk1
LEFT JOIN data_source ds ON cu.data_src_pk1 = ds.pk1
WHERE
cm.course_id like '%.202010'
Common Search Criteria:COURSE_MAIN.START_DATE
COURSE_MAIN.END_DATE 
COURSE_MAIN.COURSE_ID
DATA_SOURCE_KEY.BATCH_UID

** Sometimes it may be useful to locate Courses by (if applicable:
TERM.NAME
TERM.START_DATE
TERM.END_DATE

To do so, you will first need to join the COURSE_TERM and TERM tables to your query:

LEFT JOIN course_term ct ON cm.pk1 = ct.crsmain_pk1
LEFT JOIN term t ON ct.term_pk1 = t.pk1
USERS.USER_ID
USERS.FIRSTNAME
USERS.LASTNAME
USERS.EMAIL
USERS.AVAILABLE_IND
USERS.LAST_LOGIN_DATE
DATA_SOURCE_KEY.BATCH_UID
Any of the Prior Criteria.
COURSE_USERS.ROLE
COURSE_USERS.LAST_ACCESS_DATE
COURSE_USERS.CHILD_CRSMAIN_PK1 

 

** To effectively use COURSE_USERS.CHILD_CRSMAIN_PK1,
you would generally need to join the COURSE_MAIN table to the COURSE_USERS.CHILD_CRSMAIN_PK1 in some manner.
It may however be useful to include a
"IS NULL" or "IS NOT NULL"
condition on that value to filter your results as desired.

2. REST API URL

If the Database is not available or if you just want to get a few values quickly, you can get most of the information that you need via REST API URL. To request data from the REST API URLs, log into your environment and then input the corresponding URL into your browser's address bar. The resulting data will be rendered in JSON format, however it is generally fairly human readable. The documentation located at https://developer.blackboard.com/portal/displayApi includes the basic structure for REST API URLs. 

Important Notes:
  • A Browser's Address Bar only supports HTTP GET requests; to use POST/PUT/PATCH/DELETE requests, you would need some other tool, which is not covered here.
  • Some REST API URL functionality may not be available in earlier versions of Learn.
  • As REST API URL results are restricted by the System Role of the user making the request much of the data will require logging into the environment as a user with a System Administrator Role.
  • REST API URL results are limited to 100 records per page, excess results will be paginated and the JSON will include a URL to get to the next page of data.
Example of a REST API URL to get a USER's record:https://test.blackboard.com/learn/api/public/v1/users/userName:jmith 
Example of a REST API URL to get a COURSES's record:https://test.blackboard.com/learn/api/public/v1/courses/courseId:math101 
Example of a REST API URL to get the records for the USERS who are Enrolled in a COURSE:https://test.blackboard.com/learn/api/public/v1/courses/courseId:math101/users?expand=user** If the users value is not expanded, only the users' primary keys will be returned, which is not very helpful for this task.
Example of a REST API URL to get records for the COURSES in which a USER is Enrolled:https://test.blackboard.com/learn/api/public/v1/users/userName:jsmith/courses?expand=course** If the course value is not expanded, only the course' primary keys will be returned, which is not very helpful for this task.
Example of a REST API URL to get a Data Source record based on the Data Source Primary Key:https://test.blackboard.com/learn/api/public/v1/dataSources/_2_1Other REST API URLs will return the Data_Source_Key primary key values for each record (when applicable).

3. ICM or Custom Queries/Reports

Warrentiable queries and reports are available through your Account Manager, by engaging Blackboard Integration and Customization Management (ICM.) ICM is the consulting arm of Blackboard, Inc and can work with you to 'flesh out' your needs and devise custom reports, code etc to your specifications.

D. Generate Feed File:

GENERATE A FEED FILE:
An SIS Feed File ( also known as a Flat File ) is composed of plaintext which is saved in a .txt or .csv file. Flat Files can be any text editor. Alternatively, .csv files may also be created or edited in a spreadsheet program which may make it easier to manage the data. When creating a feed file, you will need to seperate your data using the same delimiter ( comma, tab, pipe, etc... ) that your SIS integration is set up for. Each SIS Operation Type will support certain headers and data ( as details in the help.blackboard documentation further below ) and a given Feed File may only be used for a single SIS Opperation Type. SIS feed files process in the order of the data included within them with duplications of data potentially overriding prior entries or causing errors to occur.

When generating a Feed File, there are two main components: the Header Row and the Data Rows.

The Headers Row identifies what columns of information your data rows will include and in what order.
  • The Headers which are available to be used, are determined by Operation that you will be using the Feed File for
  • The Headers row must be the first line in the feed file
  • Headers must be separated using the whichever delimiter the Integrations is set up for ( do not include spaces )
  • Headers are case-insensitive, however using all uppercase letters is considered a best-practice
  • Using only the minimum headers possible is considered a best-practice
NOTE: Some of the headers are for columns which may not be directly changed; the DATA_SOURCE_KEY is on such column. To change a record's DSK, you must include the NEW_DATA_SOURCE_KEY header and populate the corresponding rows with the relevant data.

Example Feed Files for changing DSKs:

Include only the text from one of the white boxes.
Update Course Record:EXTERNAL_COURSE_KEY|DATA_SOURCE_KEY|NEW_DATA_SOURCE_KEY
math101|SYSTEM|Deletion_DSK_2020
Update User Record:EXTERNAL_PERSON_KEY|DATA_SOURCE_KEY|NEW_DATA_SOURCE_KEY
testUser01|SYSTEM|Deletion_DSK_2020
Update Membership/Enrollment Record:EXTERNAL_COURSE_KEY|EXTERNAL_PERSON_KEY|DATA_SOURCE_KEY
math101|testUser01|SYSTEM|Deletion_DSK_2020

Common Issues:

Affected Data TypeExample MessagePossible Cause
Any"Error: Feed File Invalid. Upload a valid Feed File."The feed file may be delimited with the wrong character for that integration. Alternatively the file may have the wrong headers for that opperation type / the wrong opperation type was selected for the file.
Course"Required field not set. CourseId value must be set. Required field not set. Course name value must be set and not be blank."A non-existant EXTERNAL_COURSE_KEY was included in the feed file data. Thus SIS is expecting to create a new Course record instead of modifying an existing one. 
User"ERROR: null value in column "user_id" violates not-null constraint"A non-existant EXTERNAL_PERSON_KEY was included in the feed file data. Thus SIS is expecting to create a new User record instead of modifying an existing one.
Membership"Invalid data for attribute: userBatchUid. Skipping entire record. Reason: The user with UserBatchUid 'testUser01' can not be found."A non-existant EXTERNAL_PERSON_KEY was included in the feed file data.

Resources

Info on the configuration, advances settings, and process of using an integration:https://help.blackboard.com/Learn/Administrator/SaaS/Integrations/Student_Information_System/SIS_Integration_Types/Snapshot_Flat_File/Snapshot_Configuration
Info on the custom field mapping for an integration:https://help.blackboard.com/Learn/Administrator/SaaS/Integrations/Student_Information_System/SIS_Integration_Types/Snapshot_Flat_File/Snapshot_Custom_Field_Map
Info on the format/ data fields for a Snapshot Flat File:https://help.blackboard.com/Learn/Administrator/SaaS/Integrations/Student_Information_System/SIS_Integration_Types/Snapshot_Flat_File/Snapshot_Format
Examples of Snapshot Flat Files for different operations:https://help.blackboard.com/Learn/Administrator/SaaS/Integrations/Student_Information_System/SIS_Integration_Types/Snapshot_Flat_File/Snapshot_Examples
Info on using the REST API to get info from your Environment:https://developer.blackboard.com/portal/displayApi


The information contained in the Knowledge Base was written and/or verified by Blackboard Support. It is approved for client use. Nothing in the Knowledge Base shall be deemed to modify your license in any way to any Blackboard product. If you have comments, questions, or concerns, please send an email to kb@blackboard.com. © 2021 Blackboard Inc. All rights reserved