My ETL processes for the Blackbaud Data Warehouse failed

The ETL process is the Extract, Transform, and Load process that updates the data warehouse with new records from the source database. We recommend monitoring the ETL process to make sure that it completes successfully each time that it is scheduled to run as well as monitoring the general overall process for any performance issues.
Follow these steps to check the status of the last time the ETL process ran to see if it was successful:
  1. Go to Adminstration > Data Warehouses > Blackbaud Data Warehouse
  2. Select the SQL Agent job tab
  3. Look at the Last execution status listed
The ETL History Tab
  • ​This tab will display information regarding each package in the ETL and if it was successful, and how long each process took to run.
  • You can customize the information that is displayed here by adding or removing columns, which will allow you to determine any potential issues with packages in the ETL process and provide for better overall monitoring of this process. Many times the ETL process will fail due to one or more specific SSIS packages.
  • The ETL history tab allows viewing which specific SSIS packages failed which then caused the entire ETL process to fail.
The SQL Agent Job Tab:
  • This tab shows the overall status of the ETL queue, the job name and if it was successful the last time it ran.
  • On this tab you will notice a link for that says, “Download status log”. This log is very useful for determining the cause of the ETL failure as it will list any errors that occurred. You can send this log to support when opening a case to help in the troubleshooting process.
The ETL Report tab:
  • This tab displays the start and end times of the ETL process and what type of refresh took place.
  • This report will only display information on the last successful ETL process. If the last ETL process failed, it will not be displayed here.

If CRM is installed locally at your organization and you have access to your own instance of SQL Server where the SQL Server Agent job is for the ETL process, follow these steps:

  1. View the history for the SQL Agent job for the ETL process in SQL Server Management Studio by right-clicking on the SQL Agent job for the ETL process and selecting view history. You will see the details of the history of the ETL process and also detailed error messages that can be used for troubleshooting. This information is extremely useful for support to have in order to troubleshoot the ETL failure.  
  2. Once you determine what time the ETL process failed by looking at either the ETL status log and\or the SQL Server Agent job history in SQL Server Management Studio, you can use those times to look at the application and possibly the system logs to see any error message that pertain to the ETL process and potentially any system events that may have occurred at that time as well that can potentially affect the ETL process. This information also is very valuable for support to have.
Review these resources for additional information about the Blackbaud Data Warehouse for Blackbaud CRM:

 

Steps to Duplicate


 

Environment

 Blackbaud CRM
 4.0

Was this article helpful?