-- Copyright 2022 Colorado Mountain College -- --Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), --to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, --and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: -- --The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. -- --THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, --FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, --WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. -- Canvas Database load script -- -- The stored procedure loads the tables from the downloaded and unpacked files. Since the files -- have the same column order as the tables (both are based on the JSON schema) we can leverage this to -- dynamically create the insert statement. -- -- The stored procedure can be called from a scheduled task or a SQL Server integration services task after the data has been downloaded from Canvas. -- -- -- USE CanvasAnalytics2 GO CREATE PROC [dbo].[LoadCanvasAnalytics] AS BEGIN -- Fetch the files to be processed -- We are reading in the file names from the provided path. These should be the unpacked files. --************************IMPORTANT: change the path to the correct location ***************************** DECLARE @files TABLE ( FileName varchar(100), Depth int, FileType int ) INSERT INTO @files EXEC Master.dbo.xp_DirTree '\\cs-powerbi1\f$\imports\CanvasData\UnpackedFiles', 1, 1 --Step through the files that we just fetched DECLARE @fileName AS varchar(100) DECLARE files_cursor CURSOR FOR SELECT FileName FROM @files OPEN files_cursor FETCH NEXT FROM files_cursor INTO @fileName WHILE @@FETCH_STATUS = 0 BEGIN -- The pathname is used in the bulk insert statement --************************IMPORTANT: change the path to the correct location ***************************** DECLARE @pathName AS varchar(500) SET @pathName = '\\cs-powerbi1\f$\imports\CanvasData\UnpackedFiles\' --from which we can also derive the table in the database since they have the same name as the file DECLARE @tableName AS varchar(100) SET @tableName = SUBSTRING(@fileName, 1, CHARINDEX('.', @fileName) - 1) --debug: SELECT @tableName --fetch the number of columns that we need to load DECLARE @columnCount AS int SELECT @columnCount = COUNT(1) FROM sys.columns a JOIN sys.tables b ON a.object_id = b.object_id WHERE b.name = @tableName --select @columnCount -- create the column defenition that we will load the file data into. Note that the file data should have the same ordinal position as the table data -- so when we load the data we do not need to worry about column names, only that there are an equal number of columns DECLARE @columnNames AS varchar(max) SET @columnNames = '' DECLARE @columnCounter as int SET @columnCounter = 1 WHILE @columnCounter <= @columnCount BEGIN SET @columnNames = @columnNames + 'C' + CAST(@columnCounter AS varchar(3)) + ' nvarchar(max), ' SET @columnCounter = @columnCounter + 1 END --debug: select @columnNames --create the create table syntax for the stage table DECLARE @execCreateTableStatement AS nvarchar(max) SET @execCreateTableStatement = 'CREATE TABLE [dbo].[DATA_LOAD_STAGE] ( ' + @columnNames + ')' --debug: PRINT @execCreateTableStatement --Create our stage table to load data into. We recreate the table for each table load. IF EXISTS(SELECT NAME FROM SYS.TABLES WHERE NAME LIKE 'DATA_LOAD_STAGE') BEGIN DROP TABLE [dbo].[DATA_LOAD_STAGE] END --debug: PRINT 'File Name: ' + @fileName --debug: PRINT 'Create Table Statement: ' + @execCreateTableStatement EXECUTE sp_executesql @execCreateTableStatement --load the data into the stage table DECLARE @execLoadStageTableStatement AS nvarchar(max) SET @execLoadStageTableStatement = 'BULK INSERT [dbo].[DATA_LOAD_STAGE] FROM ''' + @pathName + @fileName + ''' WITH (FIELDTERMINATOR = ''\t'', ROWTERMINATOR = ''0x0A'', FIRSTROW = 2)' --debug: PRINT 'Load Stage Table Statememnt: ' + @execLoadStageTableStatement EXECUTE sp_executesql @execLoadStageTableStatement --debug: select * from [dbo].[DATA_LOAD_STAGE] -- build the column statement for loading from the stage table to the final target table SET @columnCounter = 1 DECLARE @columnString AS varchar(max) SET @columnString = '' DECLARE @dataType AS varchar(50) DECLARE @dataLength AS varchar(50) WHILE @columnCounter <= @columnCount BEGIN SET @columnNames = 'C' + CAST(@columnCounter AS varchar(3)) SET @dataType = (SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tableName AND ORDINAL_POSITION = @columnCounter) SET @dataLength = (SELECT CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tableName AND ORDINAL_POSITION = @columnCounter) SET @columnString = @columnString + ' ''' + @columnNames + ''' = ' + 'CAST( (CASE WHEN ' + @columnNames + ' = ''\N'' THEN NULL ELSE ' + @columnNames + ' END) AS ' + CAST(@dataType AS varchar(50)) + (CASE WHEN @dataType = 'varchar' THEN (CASE WHEN @dataLength = '-1' THEN '(max)' ELSE '('+ @dataLength +')' END ) ELSE '' END) + ' ) ,' SET @columnCounter = @columnCounter + 1 END --remove the trailing comma on the columns statement SET @columnString = SUBSTRING( @columnString, 1, LEN(@columnString) - 1) --debug: PRINT 'Column String: ' + @columnString --build the insert string DECLARE @execTruncatStatement AS nvarchar(100) SET @execTruncatStatement = 'TRUNCATE TABLE ' + @tableName DECLARE @execInsertIntoStatemenet AS nvarchar(max) SET @execInsertIntoStatemenet = 'INSERT INTO [dbo].[' + @tableName + '] SELECT ' + @columnString + ' FROM [dbo].[DATA_LOAD_STAGE]' --debug: PRINT 'Truncate Statement: ' + @execTruncatStatement --debug: PRINT 'Execute Insert Statement: ' + @execInsertIntoStatemenet EXECUTE sp_executesql @execTruncatStatement EXECUTE sp_executesql @execInsertIntoStatemenet FETCH NEXT FROM files_cursor INTO @fileName END CLOSE files_cursor DEALLOCATE files_cursor END