Forum Discussion

ssmith-nova's avatar
ssmith-nova
New Contributor III
12 days ago

Get Transformation Errors from a Batch Harvest job

I am running some data loads using Batch Harvest. I want to be able to capture the Validate Transformation and Validate Intersections errors to include in an email.  

I am able to get the Validate Intersections errors from the application table.  Does anyone know how I would get the Validate Transformation errors?   If I run the entire job within a BR then I can get the Transformation Errors, but I would rather run it through the Batch Harvest.  

I appreciate any input.

Thank you.

Scott

Data Integration
Transformation

1 Reply

Sort By
  • ssmith-nova's avatar
    ssmith-nova
    New Contributor III
    10 days ago

    I am still looking for a better way, but here are some queries that can provide this information out of the application tables:

    --Get the member name and the number of records that have no mapping
select dim, src, cnt from (
(select 'Entity'  as dim, Et as src, count(*) as cnt from vStageSourceAndTargetData as a INNER Join WorkFlowProfileHierarchy As b On  b.ProfileKey = a.Wfk  where EtT = '~' AND b.ProfileName LIKE 'SCPC.Import' and a.TmT = '2025M2' group by Et) union all
(select 'Account' as dim, Ac as src, count(*) as cnt from vStageSourceAndTargetData as a INNER Join WorkFlowProfileHierarchy As b On  b.ProfileKey = a.Wfk  where AcT = '~' AND b.ProfileName LIKE 'SCPC.Import' and a.TmT = '2025M2' group by Ac) union all
(select 'IC'      as dim, Ic as src, count(*) as cnt from vStageSourceAndTargetData as a INNER Join WorkFlowProfileHierarchy As b On  b.ProfileKey = a.Wfk  where IcT = '~' AND b.ProfileName LIKE 'SCPC.Import' and a.TmT = '2025M2' group by Ic) union all
(select 'U1'      as dim, U1 as src, count(*) as cnt from vStageSourceAndTargetData as a INNER Join WorkFlowProfileHierarchy As b On  b.ProfileKey = a.Wfk  where U1T = '~' AND b.ProfileName LIKE 'SCPC.Import' and a.TmT = '2025M2' group by U1) union all
(select 'U2'      as dim, U2 as src, count(*) as cnt from vStageSourceAndTargetData as a INNER Join WorkFlowProfileHierarchy As b On  b.ProfileKey = a.Wfk  where U2T = '~' AND b.ProfileName LIKE 'SCPC.Import' and a.TmT = '2025M2' group by U2) union all
(select 'U3'      as dim, U3 as src, count(*) as cnt from vStageSourceAndTargetData as a INNER Join WorkFlowProfileHierarchy As b On  b.ProfileKey = a.Wfk  where U3T = '~' AND b.ProfileName LIKE 'SCPC.Import' and a.TmT = '2025M2' group by U3) union all
(select 'U4'      as dim, U4 as src, count(*) as cnt from vStageSourceAndTargetData as a INNER Join WorkFlowProfileHierarchy As b On  b.ProfileKey = a.Wfk  where U4T = '~' AND b.ProfileName LIKE 'SCPC.Import' and a.TmT = '2025M2' group by U4) union all
(select 'U5'      as dim, U5 as src, count(*) as cnt from vStageSourceAndTargetData as a INNER Join WorkFlowProfileHierarchy As b On  b.ProfileKey = a.Wfk  where U5T = '~' AND b.ProfileName LIKE 'SCPC.Import' and a.TmT = '2025M2' group by U5) union all
(select 'U6'      as dim, U6 as src, count(*) as cnt from vStageSourceAndTargetData as a INNER Join WorkFlowProfileHierarchy As b On  b.ProfileKey = a.Wfk  where U6T = '~' AND b.ProfileName LIKE 'SCPC.Import' and a.TmT = '2025M2' group by U6) union all
(select 'U7'      as dim, U7 as src, count(*) as cnt from vStageSourceAndTargetData as a INNER Join WorkFlowProfileHierarchy As b On  b.ProfileKey = a.Wfk  where U7T = '~' AND b.ProfileName LIKE 'SCPC.Import' and a.TmT = '2025M2' group by U7) union all
(select 'U8'      as dim, U8 as src, count(*) as cnt from vStageSourceAndTargetData as a INNER Join WorkFlowProfileHierarchy As b On  b.ProfileKey = a.Wfk  where U8T = '~' AND b.ProfileName LIKE 'SCPC.Import' and a.TmT = '2025M2' group by U8)) x

--Get the number of unique member values that have no mapping
select dim, cnt from (
(select 'Entity'  as dim, count(distinct Et) as cnt from vStageSourceAndTargetData as a INNER Join WorkFlowProfileHierarchy As b On b.ProfileKey = a.Wfk  where EtT = '~' AND b.ProfileName LIKE 'SCPC.Import' and a.TmT = '2025M2') union all
(select 'Account' as dim, count(distinct Ac) as cnt from vStageSourceAndTargetData as a INNER Join WorkFlowProfileHierarchy As b On b.ProfileKey = a.Wfk  where AcT = '~' AND b.ProfileName LIKE 'SCPC.Import' and a.TmT = '2025M2') union all
(select 'IC'      as dim, count(distinct Ic) as cnt from vStageSourceAndTargetData as a INNER Join WorkFlowProfileHierarchy As b On b.ProfileKey = a.Wfk  where IcT = '~' AND b.ProfileName LIKE 'SCPC.Import' and a.TmT = '2025M2') union all
(select 'U1'      as dim, count(distinct U1) as cnt from vStageSourceAndTargetData as a INNER Join WorkFlowProfileHierarchy As b On b.ProfileKey = a.Wfk  where U1T = '~' AND b.ProfileName LIKE 'SCPC.Import' and a.TmT = '2025M2') union all
(select 'U2'      as dim, count(distinct U2) as cnt from vStageSourceAndTargetData as a INNER Join WorkFlowProfileHierarchy As b On b.ProfileKey = a.Wfk  where U2T = '~' AND b.ProfileName LIKE 'SCPC.Import' and a.TmT = '2025M2') union all
(select 'U3'      as dim, count(distinct U3) as cnt from vStageSourceAndTargetData as a INNER Join WorkFlowProfileHierarchy As b On b.ProfileKey = a.Wfk  where U3T = '~' AND b.ProfileName LIKE 'SCPC.Import' and a.TmT = '2025M2') union all
(select 'U4'      as dim, count(distinct U4) as cnt from vStageSourceAndTargetData as a INNER Join WorkFlowProfileHierarchy As b On b.ProfileKey = a.Wfk  where U4T = '~' AND b.ProfileName LIKE 'SCPC.Import' and a.TmT = '2025M2') union all
(select 'U5'      as dim, count(distinct U5) as cnt from vStageSourceAndTargetData as a INNER Join WorkFlowProfileHierarchy As b On b.ProfileKey = a.Wfk  where U5T = '~' AND b.ProfileName LIKE 'SCPC.Import' and a.TmT = '2025M2') union all
(select 'U6'      as dim, count(distinct U6) as cnt from vStageSourceAndTargetData as a INNER Join WorkFlowProfileHierarchy As b On b.ProfileKey = a.Wfk  where U6T = '~' AND b.ProfileName LIKE 'SCPC.Import' and a.TmT = '2025M2') union all
(select 'U7'      as dim, count(distinct U7) as cnt from vStageSourceAndTargetData as a INNER Join WorkFlowProfileHierarchy As b On b.ProfileKey = a.Wfk  where U7T = '~' AND b.ProfileName LIKE 'SCPC.Import' and a.TmT = '2025M2') union all
(select 'U8'      as dim, count(distinct U8) as cnt from vStageSourceAndTargetData as a INNER Join WorkFlowProfileHierarchy As b On b.ProfileKey = a.Wfk  where U8T = '~' AND b.ProfileName LIKE 'SCPC.Import' and a.TmT = '2025M2')) x

--Get the number of records that have no mapping
select 
sum(case when EtT = '~' then 1 else 0 end) as Entity,
sum(case when AcT = '~' then 1 else 0 end) as Account,
sum(case when IcT = '~' then 1 else 0 end) as IC,
sum(case when U1T = '~' then 1 else 0 end) as UD1,
sum(case when U2T = '~' then 1 else 0 end) as UD2,
sum(case when U3T = '~' then 1 else 0 end) as UD3,
sum(case when U4T = '~' then 1 else 0 end) as UD4,
sum(case when U5T = '~' then 1 else 0 end) as UD5,
sum(case when U6T = '~' then 1 else 0 end) as UD6,
sum(case when U7T = '~' then 1 else 0 end) as UD7,
sum(case when U8T = '~' then 1 else 0 end) as UD8
from vStageSourceAndTargetData as a INNER Join WorkFlowProfileHierarchy As b On b.ProfileKey = a.Wfk  where b.ProfileName LIKE 'SCPC.Import' and a.TmT = '2025M2' AND (
EtT = '~' OR AcT = '~' OR IcT = '~' OR U1T = '~' OR U2T = '~' OR U3T = '~' OR U4T = '~' OR U5T = '~' OR U6T = '~' OR U7T = '~' OR U8T = '~')