Apatar not clearing data in Salesforce table

stangerb · Joined: 13 Aug 2009 Posts: 4

I have and MS SQL component as my source, attached to the Transform component, which is in turn connected to a Salesforce component. On the Salesforce component, I have configured as Insert Mode, with the "Clear the selected table before any data written" box checked.

When I run the project however, Apatar is not clearing the data in the Salesforce table.

Any thoughts?

rtest · Joined: 16 Dec 2008 Posts: 71

Hello,

Can you please share what object in SFDC using as TARGET.

Need to do some debugging:

1) Have you tried to delete a single record manually using SFDC "UI".

There could be a chance of having any work flow/trigger/validation rule or might be not having right for deleting the records set by Admin either on object level or user that you using in the mapper file do not have right for deletion.

2) if you are able to delete the record from SFDC UI using same user then go to your mapper file and do "Preview Result" on

* Transform
* SFDC (Object)

individually. And check in parallel your error output file and see what error message you are getting.

Thanks,
Rohit Sharma

stangerb · Joined: 13 Aug 2009 Posts: 4

Rohit,

Thanks for the reply. I'm running the job under my Sys Admin username in SF, and can delete records from the SFDC. There are no triggers or validations on the custom object I am inserting records into.

It's not that records aren't deleted. Sometimes a few are deleted. Sometimes none are deleted. If I run it several times

Preview Results on the Transform object yields expected results - 5977 records & no errors in the log.

However, on the SFDC object, the debug log says 8024 records were processed (no errors) but 7983 records show up in the preview window.

I'd copy the whole debug log, but every time I try to select the text in the debug window, the cursor almost instantly returns to the bottom & I can't select or copy.

Really frustrated. Any thoughts?

rtest · Joined: 16 Dec 2008 Posts: 71

Hello,

For pt:

stangerb · Joined: 13 Aug 2009 Posts: 4

Temp Folder Deleted.

Count in SFDC before job runs 5996

MS SQL 5993 records
Transform Object 5993 records
SFDC 9989 records

5996+5993-9989=2000 records deleted.

I changed the MS SQL query to only return one record. I ran the project several times and was able to finally get all of the records to be deleted incrementally as below:

1st Run Start 9989 records End 7990 records = 1999 record net change = 2000 records deleted - 1 Added

2nd Run Start 7990 records End 5991 records = 1999 record net change = 2000 records deleted - 1 Added

3rd Run Start 5991 records End 3992 records = 1999 record net change = 2000 records deleted - 1 Added

4th Run Start 5992 records End 1993 records = 1999 record net change = 2000 records deleted - 1 Added

5th Run Start 1993 records End 1 record = 1992 record net change = 1993 records deleted - 1 Added

Is there some sort of delete governor in Apatar or Salesforce limiting deletes to 2000 records?

I also zipped & sent the two log files you requested to the email address you gave me.

Thanks for the help.

rtest · Joined: 16 Dec 2008 Posts: 71

It shows that Apatar - delete functionality is working.

You need to check in your org : if any trigger does exist on any other object which maintain lookup/master detail relationship with your custom object.

By Default SFDC delete the data in a batch size of "200" in a single process.

That could be issue with Apatar if they have set certain limit while deleting for salesforce (but I don't think so). Coz I was able to delete more than 15000 of record via enabling that checkbox {Clear previous data}.

is this possible for you to share with me your sfdc login detail (if its your dev org) and mapper file.

stangerb · Joined: 13 Aug 2009 Posts: 4

Rohit,

Thanks for the reply. For some reason, I did not get a notification that you had replied.

There are no triggers attached to the table. It's just a raw data table.

Unfortunately I don't have this in a dev database.

I'm at a total loss. Is there anyone I can contact at apatar?

Sergei · Joined: 26 Jul 2007 Posts: 969

rtest · Joined: 16 Dec 2008 Posts: 71

Hello,

can you share your custom obj original data in xls sheet with me.

Thanks,
Rohit Sharma

caos14159 · Joined: 20 May 2011 Posts: 1

Hello, I am having this same problem, was there ever a solution?

sumitvakil · Joined: 28 Aug 2011 Posts: 2

This is still a problem. I set up a very simple experiment. I created a clean org, and a single custom object with one text field only. Then I loaded 5,000 records. Next, I tried to insert 150 new records using the Apatar salesforce connector (Insert option), making sure to select the clear table before Insert option. Note that my data source is an Access database, but that shouldn't matter.

Only 2,000 records get deleted. It seems that a loop runs 10 times, deleting 200 records during each loop. I see a "Records Processed" message count up from 0 to 11.

Edit: Forgot to mention that this is a problem with deletes only. I have successfully used the Salesforce connector to upload 45,000 records.

The error console is as follows:

*********************************
Start date Wed Oct 05 23:43:11 PDT 2011
Version Apatar_v1.12.23
JRE Version 1.7.0
JRE Vendor Oracle Corporation
Encoding Cp1252
log4j.configuration = `null`
Start Reading from MasterOutput
Start Reading from MasterOutput
creating errors dump file
C:\src\ETL\Apatar\test\errors\errors20111005234528.csv
dump file `C:\src\ETL\Apatar\test\errors\errors20111005234528.csv` created
Exception in thread "AWT-EventQueue-0" java.lang.StringIndexOutOfBoundsException: String index out of range: -1
at java.lang.String.checkBounds(String.java:406)
at java.lang.String.<init>(String.java:578)
at com.apatar.ui.JErrorDebugDialog.readFromFile(JErrorDebugDialog.java:192)
at com.apatar.ui.JErrorDebugDialog.createDialog(JErrorDebugDialog.java:111)
at com.apatar.ui.JErrorDebugDialog.<init>(JErrorDebugDialog.java:78)
at com.apatar.ui.Actions$12.actionPerformed(Actions.java:325)
at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2018)
at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2341)
at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
at javax.swing.AbstractButton.doClick(AbstractButton.java:376)
at javax.swing.plaf.basic.BasicMenuItemUI.doClick(BasicMenuItemUI.java:833)
at javax.swing.plaf.basic.BasicMenuItemUI$Handler.mouseReleased(BasicMenuItemUI.java:877)
at java.awt.AWTEventMulticaster.mouseReleased(AWTEventMulticaster.java:289)
at java.awt.Component.processMouseEvent(Component.java:6504)
at javax.swing.JComponent.processMouseEvent(JComponent.java:3321)
at java.awt.Component.processEvent(Component.java:6269)
at java.awt.Container.processEvent(Container.java:2229)
at java.awt.Component.dispatchEventImpl(Component.java:4860)
at java.awt.Container.dispatchEventImpl(Container.java:2287)
at java.awt.Component.dispatchEvent(Component.java:4686)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4832)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4492)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4422)
at java.awt.Container.dispatchEventImpl(Container.java:2273)
at java.awt.Window.dispatchEventImpl(Window.java:2713)
at java.awt.Component.dispatchEvent(Component.java:4686)
at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:707)
at java.awt.EventQueue.access$000(EventQueue.java:101)
at java.awt.EventQueue$3.run(EventQueue.java:666)
at java.awt.EventQueue$3.run(EventQueue.java:664)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:87)
at java.awt.EventQueue$4.run(EventQueue.java:680)
at java.awt.EventQueue$4.run(EventQueue.java:678)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:677)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:211)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:128)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:117)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:113)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:105)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:90)

Thanks,

Sumit

sumitvakil · Joined: 28 Aug 2011 Posts: 2

So my workaround for this problem was to stop using Apatar's delete functionality. In my case, it is sufficient for me to just delete the existing data and load fresh data.

For the delete, I am successfully using the command line version of Salesforce's Data Loader. The data loader first retrieves all the ids for the tables I want to clear. Then it deletes records with these ids. It works great!

Once the data loader finishes, I run Apatar to insert new data.

Sumit