Trying to figure out best way to trigger a task to be run based on when a record shows up in an audit table

• Monday, May 14, 2012 6:41 PM

   

   
  

  0

  We have a data warehouse that gets loaded every morning and when it completes it updates a record in an audit table. I am trying to figure out how to go about checking that value every ten minutes for an end date and then once I get that I know it is safe to run jobs against that server for running Crystal Reports into pdf files.

  Currently I have a daily, weekly, monthly, and a couple other jobs scheduled in task scheduler on a BusinessObjects server that are set to run at 4am with the assumption the nightly ETLs have run. But this gets broken if the jobs runs long or fail. So I want to check the audit log for a succesful completion and the name of the ETL so I can determine which task to fire off.

  I was trying to do it with Powershell but the task will not run. Belwo is my code for that process. So no I am looking for an alternative way to accomplish this.

  #The following command needs to run one time only on each machine this script will run in order to create events 
      #new-eventlog -logname 'Crystal Batch' -Source 'Clarity Execution Script' 
  #Log the start of this process
  write-eventlog -logname 'Crystal Batch' -Source 'Clarity Execution Script' -Message 'Script Started' -id 100
  write-eventlog -logname 'Crystal Batch' -Source 'Clarity Execution Script' -Message 'Check for completed Executions' -id 201
  $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
  $SqlConnection.ConnectionString = "Server=EPIC;Database=CLARITY;Integrated Security=True"
  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
  $SqlCmd.CommandText = "usp_Get_CompletedBatches"
  $SqlCmd.Connection = $SqlConnection
  $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
  $SqlAdapter.SelectCommand = $SqlCmd
  $DataSet = New-Object System.Data.DataSet
  $SqlAdapter.Fill($DataSet)
  $SqlConnection.Close()
  $DataSet.Tables[0]
  write-eventlog -logname 'Crystal Batch' -Source 'Clarity Execution Script' -Message 'Execution check has completed' -id 202
  #loop thru the rows returned and check to see which if any execution has completed
  #If a completed execution is not returned leave the task running, otherwise run the batch and reset the task start again the next day.
  foreach($Row in $DataSet.Tables[0].Rows) 
  {
      SWITCH ($Row[1])
      {
          "DAILY"   
              {
                  # Run the daily batch 
                  write-eventlog -logname 'Crystal Batch' -Source 'Clarity Execution Script' -Message 'Running Daily Batch' -id 311
                  & "D:\Epic\Analytics Tools\Epic Crystal\DAILY.bat" 
                  write-eventlog -logname 'Crystal Batch' -Source 'Clarity Execution Script' -Message 'Daily Batch Completed' -id 312
                  
                  
                  write-eventlog -logname 'Crystal Batch' -Source 'Clarity Execution Script' -Message 'Create Audit Record' -id 201
                  
                  #Create a record in the [CHW].[CrystalBatchAudit] table. Pass in the execution name $Row[0] and the Batch name $Row[1], the date is set in the procedure to Getdate()
                  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand("CHW.usp_Insert_CrystalBatchAudit", $SqlConnection)
                  $SqlConnection.Open()
                  $SqlCmd.Commandtype =  [System.Data.CommandType]'StoredProcedure'
                  $SqlCmd.parameters.add("@Exec_Name", $Row[0]) 
                  $SqlCmd.parameters.add("@BatchName", $Row[1]) 
                  $rdr = $Sqlcmd.ExecuteReader()
                  $SqlConnection.Close()
                  
                  write-eventlog -logname 'Crystal Batch' -Source 'Clarity Execution Script' -Message 'Audit Record Created' -id 202
                  
                  #Need to set the task to not run again today
              }
              
          "WEEKLY" 
              {
                  # Run the Weekly batch 
                   write-eventlog -logname 'Crystal Batch' -Source 'Clarity Execution Script' -Message 'Running Weekly Batch' -id 321
                  & "D:\Epic\Analytics Tools\Epic Crystal\WEEKLY.bat" 
                  write-eventlog -logname 'Crystal Batch' -Source 'Clarity Execution Script' -Message 'Weekly Batch Completed' -id 322
                  
                  
                  write-eventlog -logname 'Crystal Batch' -Source 'Clarity Execution Script' -Message 'Create Audit Record' -id 201
                  
                  
                  #Create a record in the [CHW].[CrystalBatchAudit] table. Pass in the execution name $Row[0] and the Batch name $Row[1], the date is set in the procedure to Getdate()
                  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand("usp_Insert_CrystalBatchAudit", $SqlConnection)
                  $SqlConnection.Open()
                  $SqlCmd.Commandtype =  [System.Data.CommandType]'StoredProcedure'
                  $SqlCmd.parameters.add("@Exec_Name", $Row[0]) 
                  $SqlCmd.parameters.add("@BatchName", $Row[1]) 
                  $rdr = $Sqlcmd.ExecuteReader()
                  $SqlConnection.Close()
                  write-eventlog -logname 'Crystal Batch' -Source 'Clarity Execution Script' -Message 'Audit Record Created' -id 202
                  
                  #Need to set the task to not run again today
              }
              
  		"MONTHLY" 
              {
                  # Run the Monthly batch 
                  write-eventlog -logname 'Crystal Batch' -Source 'Clarity Execution Script' -Message 'Running Monthly Batch' -id 331
                  & "D:\Epic\Analytics Tools\Epic Crystal\Monthly.bat" 
                  write-eventlog -logname 'Crystal Batch' -Source 'Clarity Execution Script' -Message 'Monthly Batch Completed' -id 332
                  
                  
                  #Create a record in the [CHW].[CrystalBatchAudit] table. Pass in the execution name $Row[0] and the Batch name $Row[1], the date is set in the procedure to Getdate()
                  write-eventlog -logname 'Crystal Batch' -Source 'Clarity Execution Script' -Message 'Create Audit Record' -id 201
                  
                  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand("usp_Insert_CrystalBatchAudit", $SqlConnection)
                  $SqlConnection.Open()
                  $SqlCmd.Commandtype =  [System.Data.CommandType]'StoredProcedure'
                  $SqlCmd.parameters.add("@Exec_Name", $Row[0]) 
                  $SqlCmd.parameters.add("@BatchName", $Row[1]) 
                  $rdr = $Sqlcmd.ExecuteReader()
                  $SqlConnection.Close()
                  write-eventlog -logname 'Crystal Batch' -Source 'Clarity Execution Script'  -Message 'Audit Record Created' -id 202
                  
                  #Need to set the task to not run again today
                  
              }
           default
              {
              # No record found. Let the process Run again until it find one
              write-eventlog -logname 'Crystal Batch' -Source 'Clarity Execution Script' -Message 'No Record found. Run again in ten minutes' -id 110
              }
           
           
      }
  }

  
  
  • Reply
  • Quote