Certica Products

What to do when there are eFinance ETL errors and no eSchoolPLUS ETL errors

Updated Mar 24, 2021

What to do when there are eFinance ETL errors and no eSchoolPLUS ETL errors

1. Description

eFinance ETL errors prevent both CRL and HR-Finance Scorecards from being delivered. Use this Solution when a district has both eSchoolPLUS and eFinance (like Judson ISD) and there is an ETL error in the eFinance validation and you want to get their CRL Scorecards to them as quickly as possible and not have the eFinance ETL error hold both CRL and eFinance Scorecards up.

2. Solution

There are two parts to the solution. The first part is to run the CRL ETL so that that CRL Scorecards are delivered quickly:

1.) Disable the eFinance SIS in the ETL_CONTROL table, then

2.) Disable the eFinance Observation in NextGen, then

3.) Re-run the eSchoolPLUS data acquisition.

Exact steps:

1. Disable the eFinance SIS in the [DATA_GATEWAY_ETL].[dbo].[ETL_CONTROL] table

Edit the [DATA_GATEWAY_ETL].[dbo].[ETL_CONTROL] table

Filter by NCESLEAID and SIS='EFINANCE'

Set IS_ACTIVE to 0 where SIS=EFINANCE

2. Disable the eFinance Observation Schedule

If it is set to run on a daily schedule, then log into NextGen

Find district --> Details --> Projects/Observations --> Uncheck HR-Finance Validations --> Save

If it is set to run on a non-daily schedule, then log into Certify and disable it.

3. Re-run ETL - copy/paste the following code into SQL Server Management Studio and edit as needed

--1. Update ETL_CONTROL

--Update NCESLEAID

--USE DATA_GATEWAY_ETL

--GO

--update ETL_CONTROL

--set JOB_START = JOB_START - 1

--where NCESLEAID = '4824990'

--update ETL_CONTROL

--set job_Status = 'Completed'

--where NCESLEAID = '4824990'

--2. Re-run ETL

--Update ETL information

--USE DATA_GATEWAY

--GO

--exec uspSubmitRunEtlRequest'DATA_GATEWAY', 105310, 'Judson', '4824990', 'TX'

--3. Monitor progress

--Update NCESLEAID

--USE DATA_GATEWAY_ETL

--GO

--select * from ETL_LOG where NCESLEAID = '4824990'

--order by ID desc

--4. Update ETL_CONTROL so the next ETL run is successful

--Update NCESLEAID

--USE DATA_GATEWAY_ETL

--GO

--update ETL_CONTROL

--set JOB_START = JOB_START - 1

--where NCESLEAID = '4824990'

--update ETL_CONTROL

--set job_Status = 'Completed'

--where NCESLEAID = '4824990'

The second part of the solution happens after the eFinance ETL issue is resolved. These steps will result in the HR-Finance Scorecards being delivered:

1.) Enable eFinance SIS in the [DATA_GATEWAY_ETL].[dbo].[ETL_CONTROL] table, (change from “0” to “1”) then

2.) Run the five eFinance ETLs individually highlight below, (be sure to confirm the NCESLEAID numbers are correct and to update the School Year to current school year [2020-2021 = 2021]) 

**Script new Stored Procedure --> CREATE TO --> New Query Editor Window**

**Highlight the first EXEC statement and edit as necessary (NCES, School Year)**

EXAMPLE: EXEC [uspETL3_upd_DistrictEmployee_EFINANCE] @NCESLEAID = '4815000', @SIS_VERSION = 'TX001', @TARGET_DB = '[4815000_CRL_SUMMARY]', @TARGET_TBL = 'DISTRICT_EMPLOYEE',@SCHOOL_YR='2021'

3.) Re-enable eFinance Observation.

If it is a daily schedule then enable it in NextGen.

If it is a non-daily schedule then log into Certify and enable the HR-Finance Observation Schedule. 

Previous Article What to do when a Certify Administrator leaves the district
Next Article How To Update A Certify License Key Before The Old One Expires