Stefbauer.files.wordpress.com



CREATE Procedure [dw_dbo].[bus_val_execute] @table_name varchar(50), @master_audit_key int, @pkg_audit_key int, @job_date datetime, @table_type varchar(1), @group_no intAS/*************************************************************** Purpose: This procedure will execute business validation queries for tables. The procedure is called once per table. Queries to be executed are stored in dw_dbo.bus_val_message. All rows that require further investigation will be inserted into the dw_dbo.bus_val_result table. A report will be generated at the end of each daily load containing information about items that need to be researched. Parameters: table_name - the table name master_audit_key - the audit_key of the master package that is executing pkg_audit_key - the audit_key of the package that is executing job_date - the date and time that the package started table_type - the type of table (Fact or Dimension) group_no - the query group number to be executed during this run bus_val_message table. table_type: F = Fact D = Dimension E = Exception tables O = Other group_no: 0 = do not execute this query 1 = queries in group 1 will be processed together 2 = queries in group 2 will be processed together 3 = queries in group 3 will be processed together 500000 = Exception Queries in group 500000 will be processed together Appended audit_key information to fact table queries in order to pick up only current data.*******************************************************************************/SET NOCOUNT ONSET XACT_ABORT OFF/********************************************************************************* CREATE WORK TABLES********************************************************************************/CREATE TABLE #queries_to_execute ( query_id int, column_name varchar(50), validation_msg varchar(50), msg_cat varchar(50), severity int, zero_rows_ok bit, query varchar(8000), has_executed char(1) )CREATE TABLE #query_results ( query_id int, row_count int )CREATE TABLE #audit_key ( audit_key int )/********************************************************************************* DECLARE WORK VARIABLES********************************************************************************/DECLARE @project_key int, @school_key int, @student_key int, @audit_key int, @query_count int, @queries_processed int, @sql varchar(8000), @row_count int, @job_dt int, @query_id int, @severity int, @zero_rows_ok bit, @validation_msg varchar(50), @msg_cat varchar(50), @column_name varchar(50), @log_msg varchar(100), @err_nbr int, @err_msg varchar(100), @max_audit_key int, @append_string varchar(30), @max_audit_key_sql varchar(100)/********************************************************************************* INITIALIZE WORK VARIABLES********************************************************************************/SET @project_key = 0SET @school_key = 0SET @student_key = 0SET @audit_key = 0SET @query_count = 0SET @queries_processed = 0SET @row_count = 0SET @append_string = ''-- convert job_date from a datetime to job_dt, an intSET @job_dt = cast(convert(varchar, @job_date, 112) as int)/********************************************************************************* MAIN********************************************************************************/ -- write log message indicating that validation has started for this tableSET @log_msg = @table_name + ' validation started'INSERT INTO DW_ETL.SSIS_LoggingDB.dw_dbo.process_log(log_dt, school_key, project_key, process_desc,log_msg, msg_type_ind)SELECT GETDATE(), @school_key, @project_key, OBJECT_NAME(@@procid), @log_msg, 'I' SELECT @err_nbr = @@errorIF @err_nbr <> 0BEGIN SET @err_msg = 'Error inserting informational message to process_log' GOTO errorsEND-- for Fact tables, identify the audit_key for the most recent execution and append that information-- to all queriesIF @table_type = 'F'BEGIN SELECT @max_audit_key_sql = 'insert into #audit_key SELECT max(audit_key) FROM dw_dbo.' + @table_name + ' a' EXEC (@max_audit_key_sql) SELECT @max_audit_key = audit_key FROM #audit_key -- build string to be appended to all fact table queries SET @append_string = ' AND a.audit_key = ' + convert(varchar(10), @max_audit_key)END-- populate a temporary table with information to be validatedINSERT INTO #queries_to_execute (query_id, column_name, validation_msg, msg_cat, severity, zero_rows_ok, query, has_executed) SELECT query_id, column_name, validation_msg, msg_cat, severity, zero_rows_ok, query + @append_string, 'N' FROM dw_dbo.bus_val_message WHERE table_name = @table_name AND group_no = @group_no AND table_type = @table_type-- get the number of individual queries that will be executed for our tableSELECT @query_count = count(*) from #queries_to_execute-- loop and execute each validation queryWHILE @queries_processed <> @query_countBEGIN SELECT top 1 @query_id = query_id, @column_name = column_name, @validation_msg = validation_msg, @msg_cat = msg_cat, @severity = severity, @sql = query FROM #queries_to_execute WHERE has_executed = 'N' -- populate the second temporary table with query_id and the record count INSERT INTO #query_results EXEC(@sql) -- select the record count SELECT @row_count = row_count FROM #query_results WHERE query_id = @query_id -- if our validation query found offending rows, write a message to the bus_val_result table IF (@row_count > 0) OR (@row_count = 0 and @zero_rows_ok = 'FALSE') BEGIN INSERT INTO dw_dbo.bus_val_result(job_dt, master_audit_key, pkg_audit_key, validation_msg, msg_cat, row_count, table_name, column_name, audit_key, rec_create_dt, rec_mod_dt, severity, query_id, project_key, school_key, student_key) SELECT @job_dt, @master_audit_key, @pkg_audit_key, @validation_msg, @msg_cat, @row_count, @table_name, @column_name, @pkg_audit_key, getdate(), null, @severity, @query_id, @project_key, @school_key, @student_key SELECT @err_nbr = @@error IF @err_nbr <> 0 BEGIN SET @err_msg = 'Error inserting query_id ' + convert(varchar(5), @query_id) + ' into bus_val_result' GOTO errors END END -- update the row to indicate that it has been processed UPDATE #queries_to_execute SET has_executed = 'Y' WHERE query_id = @query_id -- increment the queries_processed variable SET @queries_processed = @queries_processed + 1END-- write log message indicating that validation has completed for this tableSET @log_msg = @table_name + ' validation completed'INSERT INTO DW_ETL.SSIS_LoggingDB.dw_dbo.process_log(log_dt, school_key, project_key, process_desc,log_msg, msg_type_ind)SELECT GETDATE(), @school_key, @project_key, OBJECT_NAME(@@procid), @log_msg, 'I'SELECT @err_nbr = @@errorIF @err_nbr <> 0BEGIN SET @err_msg = 'Error inserting informational message to process_log' GOTO errorsENDDROP TABLE #queries_to_executeDROP TABLE #query_resultsRETURN/*************************************************************************** ** errors ***************************************************************************/errors:INSERT INTO DW_ETL.SSIS_LoggingDB.dw_dbo.process_log(log_dt, school_key, project_key, process_desc, log_msg, msg_type_ind)SELECT GETDATE(), @school_key, @project_key, OBJECT_NAME(@@procid), @err_msg, 'E'RAISERROR(@err_msg,13,1)RETURN @err_nbrGO ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download