MS SQL Canvas Data Database Generation and Load Scripts

FelixHaeuptle
Community Explorer

I was recently tasked by the college leadership to figure out a way to get more data out of Canvas. How hard could that be right? After poking at the request for a few weeks like it was a dead rat, I decided to give Canvas Data a try. The result was the creation of two SQL scripts. Impressive right? The first file creates the database based on the JSON file that is part of the Canvas Data download. The second file creates the stored procedure that loads the unpacked Canvas Data files into the tables. The hardest part of the setup is getting the Canvas Data files using Canvas Data CLI but there are good instructions on that. Once the data has been downloaded onto a local server, the database creation script can be updated to point to the JSON file location and executed. It creates all the tables and columns and primary key indexes based on the provided schema. The second script creates the stored procedure that loads the tables. The code is documented (mostly) and explains how all this happens (mostly). This should shorten the development cycle for MS SQL shops significantly and the code could probably be adapted to other SQL environments. Hopefully this helps get people over the hump of creating the initial database. 

The CanvasDatabaseGenerationPublish.txt and CanvasDatabaseLoadPublish.txt files should have a .sql extension. They are SQL script files.  

If you see areas that I can improve please let me know. I like to learn new things and improve my skills as well as the code. Also, I know my spelling is horrible. I became a programmer because my spelling is so bad that it makes perfectly legible and functional code.

 

Cheers,

Felix

Labels (3)