+ Start a Discussion
JustinWilliams2381JustinWilliams2381 

Trying to figure what an ETL suddenly started dropping email addresses

A few weeks after a successful ETL was developed to insert thousands of leads a day it started inserting without the email.  The email used to be in there, the source data has an email, and my etl developer says his results show he is passing an email address.

 

I setup a debug log on his User ID but reading it is difficult.  I search for 'Email' in several of the logs that run every 30 minutes.  The problem is the part that looks like it will give me insight into the issue is 'abbreviated'.  

13:00:32.817 (817773000)|VARIABLE_ASSIGNMENT|[5]|lead|{"Project_Mercury_Foll (8 more) ...":false,"Month_Became_Hot__c":"01Jan","RecordTypeId":"01270000000UKWbAAO","LastModifiedByID":"00570000002DffpAAC","LastTransferDate":"2013-01-14T19:00:32.000Z","Owner_ID__c":"00570000002DffpAAC","Demo_Attendee_1_Titl (4 more) ...":"populated from title (6 more) ...","Full_Name__c":"Sam Sacks","Unqualified__c":0,"LastModifiedDate":"2013-01-14T19:00:32.000Z","Gg_Accountability__c":false,"LeadSource":"GB Non-Member Sub","LastName":"Sacks","Firm_Name__c":"populated from compa (13 more) ...","Qualified__c":0,"New_Email_Address__c":false,"Gg_Efficiency__c":false,"DoNotCall":false,"Email_Address_Status (3 more) ...":0,"i__CreatedForUser__c":"00570000002DffpAAC","CreatedByID":"00570000002DffpAAC","SFGA__CorrelationID_ (2 more) ...":"none","Project_Marketing_Em (20 more) ...":false,"Email_to_Fax__c":"083006@send.trustfax (4 more) ...","Other_Questions_Requ (6 more) ...":false,"Project_Emailing__c":false,"Multiple_Project_Mar (21 more) ...":false,"cbSubEx__c":false,"Fred_Upgrade__c":false,"Activity_monitor__c":"_IM1_/img/samples/fl (34 more) ...","Date_of_Request_Mont (4 more) ...":"00","X2012_Driver_Status_ (2 more) ...":"D - Non-Driver - New","Log_a_Call__c":"_HL_ENCODED_/00T/e?t (170 more) ...","Hot_This_Month__c":"T","HasOptedOutOfEmail":false,"Last_GB_Update__c":"2013-01-14T18:59:16.000Z","Country":"USA","Online_Demo_Request_ (2 more) ...":false,"Activity_monitor_gro (5 more) ...":"0 - No Activity","Greengrade__c":false,"Numeric_Score__c":0.0,"CreatedById":"00570000002DffpAAC","IsDeleted":false,"cbTextura__c":false,"Gg_Profitability__c":false,"Id":"00Q7000000lwNTHEA2","Area_Code__c":"800","Any_Activity__c":1,"SFGA__Web_Source__c":"none","Month_Pardot_Score_G (7 more) ...":"Error","Accuracy__c":false,"Project_Mailing__c":false,"GB_Record_ID__c":"273030","IsConverted":false,"Street":"150 Dedham Street","Phone":"8002533539","Month_Created__c":"01Jan","HasOptedOutOfFax":false,"GradeBeam_Status__c":"Active-Fax","Hot_Last_Month__c":"F","OwnerId":"00570000002DffpAAC","Not_in_US__c":false,"SFGA__Correlation_Da (5 more) ...":"none","LEED_Postcard__c":false,"Days_since_created__ (1 more) ...":0,"LEED_AP__c":false,"Enterprise__c":false,"IsUnreadByOwner":true,"Paper_Savings__c":"18,000 pages","Automatic_Follow_up_ (12 more) ...":false,"General_Postcard__c":false,"SystemModstamp":"2013-01-14T19:00:32.000Z","FirstName":"Sam","Company":"Duraflex, Inc.","Gg_Auditability__c":false,"New_User__c":false,"cbMyShopDocs__c":false,"cbGradeBeam__c":false,"LastModifiedById":"00570000002DffpAAC","Website":"http://www.dur-a-fle (5 more) ...","Gg_Accessibility__c":false,"Fax":"5083840064","Automatic_Follow_Up_ (15 more) ...":false,"City":"Norfolk","Demo_Attendee_1_Name (3 more) ...":"populated from name  (5 more) ...","Resolved__c":0,"cbALL__c":false,"Status":"Open","PostalCode":"02056","State":"MA","Project_Marketing__c":false,"cbGreengrade__c":false,"CreatedDate":"2013-01-

 

I just have a hard time reading this.  Is there a better way to use the debug log to see why the ETL isn't populating the email field?

crop1645crop1645

Lots of possibilities here to think about - these are things that execute after the ETL load occurs:

 

1. Is there a workflow with a field update that sets email to null if some condition is true?

 

2. is there an apex trigger on lead that sets email to null if some condition is true?

 

3. Is there a @future method that updates email to null after an insert or update?

 

4. How is this ETL constructed - what technology is it using?  If it relies on some 'map' between the source data model and the SFDC schema for lead, perhaps, the map entry for 'email' was dropped and the ETL middleware simply doesn't map the source value to the SFDC API call it is making

JustinWilliams2381JustinWilliams2381

Update:

 

My developer and I ran some tests.  He pushed some data from his local drive and the email filled the email properly.  When the ETL runs on schedule from the production environment the email does NOT populate.  Could some kind of formatting issue keep a value from being populated but allow the rest of the record to save?  We also noticed the other ETL we use that creates contact records also does not populate emails anymore.  They both stopped filling in emails from around the same time, the 23rd or 24th of Decemer 2012.  there weren't any changes to how SF handles emails around then was there? 

crop1645crop1645

Justin

 

SFDC Lead.email field hasn't changed at all -- it is way too fundamental

 

Does the production ETL mapping rules map source file email to Lead.email ?

Does the profile used by the Production ETL not have field visibility (see Setup | Security Settings | Field Security) to Lead.email ?   Perhaps the ETL developere's profile is different than the prodcution ETL system's profile

 

eric