How to Troubleshoot Issues with Failed ETL Processes for the Blackbaud Data Warehouse in Blackbaud Enterprise CRM
Published
LeAnn M. Hillman, Enterprise Support Specialist, Blackbaud CRM; A+ , Security+ , Network+
The ETL (Extract, Transform, and Load) process is what updates the data warehouse with information for new or changed records from the source database. From time to time you may encounter a failed ETL process for your Blackbaud Data Warehouse and wonder how to go about troubleshooting why the ETL process failed.
There are some tools you can use to begin the investigation, whether you are hosted by Blackbaud or if you self-host your own environment. This blog will discuss how to determine if your ETL job failed, and troubleshooting steps to take once you have determined that it did fail to then attempt to find the reason for the failure.
It is a good idea to monitor the ETL process to make sure that it completes successfully and to monitor the general overall process for any performance issues. There are several ways you can check to make sure that your ETL process ran successfully. The steps below can be taken if you are hosted by Blackbaud as well as if you are self-hosted.
1. The ETL History Tab: This tab will display information regarding each package in the ETL. If the process was successful, it will show how long each process took to run. Additionally, you can customize the information that is displayed here by adding or removing columns. This 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:
2. The SQL Agent Job Tab: This tab shows the overall status of the ETL queue, the job name and whether 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. This log can be sent to support when opening a case to help in the troubleshooting process:
3. The ETL Report tab: The ETL report will display 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:
The following steps can be taken if you host your own instance of CRM and have access to your own instance of SQL Server where the SQL Server Agent job is for the ETL process.
4. View the history for the SQL Agent job for the ETL process in SQL Server Management Studio: Right click on the SQL Agent job for the ETL process and select 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. This information can be submitted when a case is opened in order to speed up the troubleshooting process:
5. Windows Event Viewer Application and System Logs: Once it is determined 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, and is beneficial to be submitted when creating a case for support.
Additional Information on the Blackbaud Data Warehouse for Enterprise CRM:
https://www.blackbaud.com/files/support/guides/enterprise/400/datamart.pdf
https://www.blackbaud.com/files/support/guides/enterprise/400/bbinstal.pdf
https://www.blackbaud.com/files/support/guides/infinitytechref/infrefversions-developer-help.htm#WelcomeInfinityReference.htm%3FTocPath%3D_____1
1. The ETL History Tab: This tab will display information regarding each package in the ETL. If the process was successful, it will show how long each process took to run. Additionally, you can customize the information that is displayed here by adding or removing columns. This 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:
2. The SQL Agent Job Tab: This tab shows the overall status of the ETL queue, the job name and whether 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. This log can be sent to support when opening a case to help in the troubleshooting process:
3. The ETL Report tab: The ETL report will display 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:
The following steps can be taken if you host your own instance of CRM and have access to your own instance of SQL Server where the SQL Server Agent job is for the ETL process.
4. View the history for the SQL Agent job for the ETL process in SQL Server Management Studio: Right click on the SQL Agent job for the ETL process and select 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. This information can be submitted when a case is opened in order to speed up the troubleshooting process:
5. Windows Event Viewer Application and System Logs: Once it is determined 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, and is beneficial to be submitted when creating a case for support.
Additional Information on the Blackbaud Data Warehouse for Enterprise CRM:
https://www.blackbaud.com/files/support/guides/enterprise/400/datamart.pdf
https://www.blackbaud.com/files/support/guides/enterprise/400/bbinstal.pdf
https://www.blackbaud.com/files/support/guides/infinitytechref/infrefversions-developer-help.htm#WelcomeInfinityReference.htm%3FTocPath%3D_____1
News
Blackbaud CRM™ and Blackbaud Internet Solutions™ Blog
04/05/2016 5:23pm EDT
Leave a Comment