Data Loader Command Line Tool slow

Question

I am trying to automate a data load using the Command Line tools and have everything working smoothly but it is taking 10x longer than if I do it through the Data Loader GUI.

Below is an exert from my process-conf.xml

<bean id="csvUpsertOrderItem"
      class="com.salesforce.dataloader.process.ProcessRunner"
      singleton="false">
    <description>Upsert Transaction Headers into Orders standard object.</description>
    <property name="name" value="csvUpsertOrderItem"/>
    <property name="configOverrideMap">
        <map>
            <entry key="sfdc.debugMessages" value="false"/>
            <entry key="sfdc.endpoint" value="CUSTOM ENDPOINT"/>
            <entry key="sfdc.username" value="USERNAME"/>
            <entry key="sfdc.password" value="ENCRYPTED PASSWORD"/>
            <entry key="process.encryptionKeyFile" value="C:\Program Files (x86)\salesforce.com\Data Loader\bin\key.txt"/>
            <entry key="sfdc.timeoutSecs" value="540"/>
            <entry key="sfdc.loadBatchSize" value="2000"/>
            <entry key="sfdc.entity" value="OrderItem"/>
            <entry key="process.operation" value="upsert"/>
            <entry key="sfdc.useBulkApi" value="true"/>
            <entry key="sfdc.bulkApiSerialMode" value="true"/>
            <entry key="sfdc.externalIdField" value="SlId__c"/>
            <entry key="process.mappingFile" value="C:\Users\User\Google Drive\Automation\OrdersLine21Jan.sdl"/>
            <entry key="process.outputError" value="C:\Users\User\downloads\Logs\errorUpsertOrderItem.csv"/>
            <entry key="process.outputSuccess" value="C:\Users\User\downloads\Logs\successUpsertOrderItem.csv"/>
            <entry key="dataAccess.name" value="C:\Users\User\Google Drive\Automation\JAEG_TransactionDetails.csv" />
            <entry key="dataAccess.type" value="csvRead" />
        </map>
    </property>     </bean>

From my research, it seems to be something to do with either the debug log (I think most likely) or batch size.

I have set the sfdc.debugMessages to 'false' so it is not writing the files but it does seem to write it to the command screen. I feel this could be causing the problem, is there a default log setting? Maybe a process command setting?

In the data loader document http://resources.docs.salesforce.com/200/6/en-us/sfdc/pdf/salesforce_data_loader.pdf it says the max sfdc.loadBatchSize is 200 but through the UI it sets it to 2000 when batch is true. If that does restrict it, that could explain it.

I just cant find anything recent about this problem, anyone had any luck doing this at full pace recently?

Logging.properties file

############################################################

# Default Logging Configuration File

#

# You can use a different file by specifying a filename

# with the java.util.logging.config.file system property.

# For example java -Djava.util.logging.config.file=myfile

############################################################

############################################################

# Global properties

############################################################

# "handlers" specifies a comma separated list of log Handler

# classes. These handlers will be installed during VM startup.

# Note that these classes must be on the system classpath.

# By default we only configure a ConsoleHandler, which will only

# show messages at the INFO and above levels. handlers= java.util.logging.ConsoleHandler

# To also add the FileHandler, use the following line instead.

#handlers= java.util.logging.FileHandler, java.util.logging.ConsoleHandler

# Default global logging level.

# This specifies which kinds of events are logged across

# all loggers. For any given facility this global level

# can be overriden by a facility specific level

# Note that the ConsoleHandler also has a separate level

# setting to limit messages printed to the console. .level= INFO

############################################################

# Handler specific properties.

# Describes specific configuration info for Handlers.

############################################################

# default file output is in user's home directory. java.util.logging.FileHandler.pattern = %h/java%u.log java.util.logging.FileHandler.limit = 50000 java.util.logging.FileHandler.count = 1 java.util.logging.FileHandler.formatter = java.util.logging.XMLFormatter

# Limit the message that are printed on the console to INFO and above.

java.util.logging.ConsoleHandler.level = INFO

java.util.logging.ConsoleHandler.formatter =

java.util.logging.SimpleFormatter

Answer 1

In the folder that contains the process-conf file, do you also have a file called log-conf.xml? Because if you don't, I know DataLoader starts logging everything, and creates a huge file...

Do you also have a config file in the folder? If not specified in the bean, that will also set parameters...

I have the command line setup on my machine for various projects - happy to to do a comparison test to check whether it is tool or machine specific - which version are you using?

AMENDED: I added the log-conf.xml and set the priorities to 'OFF'. Not sure why the setting in the process-conf doesnt override this, I am not sure.

Answer 2

Your bean actually looks legit, but I know this is a pain to get it working alright. I can't tell why it's wrong, unfortunately. In fact, I felt so much pain I went through quite some hoops to create a tool around the data loader functionality to make it work from the command line each time. It highly simplified the scripting of the Data Loader.

I hope you don't mind if I show you how you'd do it with NoFrillsTransformation. As a prerequisite, you will need to "install" NoFrillsTransformation. If you're on Windows (which I assume as you are using the DataLoader in the first place), this is fairly simple and is described in the Installation Guide.

First, create a sfdc_config.xml file which contains the general information on end points, credentials and such. It has the following syntax:

<?xml version="1.0" encoding="utf-8"?>
<SfdcConfig>
  <DataLoaderDir>[path to Data Loader]</DataLoaderDir>
  <LogFileDir>[log file path]</LogFileDir>
  <SuccessFileName>[full path to success log]</SuccessFileName>
  <ErrorFileName>[full path to error log]</ErrorFileName>
  <SfdcUsername>[Salesforce user name]</SfdcUsername>
  <SfdcPassword>[Salesforce Password + Token]</SfdcPassword>
  <SfdcEncryptedPassword>[encrypted password</SfdcEncryptedPassword>
  <SfdcEndPoint>[Salesforce endpoint]</SfdcEndPoint>
  <LoadBatchSize>[batch size]</LoadBatchSize>
  <KeepTempFiles>[true|false]</KeepTempFiles>
  <FailOnErrors>[true|false]</FailOnErrors>
  <UseBulkApi>[true|false]</UseBulkApi>
  <BulkApiSerialMode>[true|false]</BulkApiSerialMode>
</SfdcConfig>

These are the ones you need to fill in; you should have all of this information already at hand, otherwise you wouldn't have been able to create your bean XML. In case you need additional help, you can find more information on that inside the Wiki (above link).

Then you continue to describe as another XML file what you are trying to do, in this case you're upserting OrderEntity records. So, this will be your template:

<?xml version="1.0" encoding="utf-8" ?>
<Transformation>
  <Source config="delim=','">file://[path to your CSV file]</Source>
  <Target config="sfdc_config.xml">sfdc://OrderItem.upsert:SlId__c</Target>

  <Fields appendSource="true" />
</Transformation> 

After doing this, you can call NoFrillsTransformation.exe like so:

NoFrillsTransformation.exe upsert_orderentity.xml

If you have specified that NFT should use the bulk API, it will do so. Otherwise you can switch it on and off using the command line switches usebulkapi (usebulkapi=true) and bulkapiserialmode (bulkapiserialmode=true).

Disclaimer: I wrote this tool, and I cannot guarantee it does not contain any bugs. I can actually guarantee it does. But I successfully used it to migrate 60k Accounts, 300k Contacts, 150k E-Mails, 60k Tasks, 500 Users, and a lot more from SAP/CRM to Salesforce.

Answer 3

I just checked a process-conf.xml NoFrillsTransformation created, and the only four differences I could spot should not make a difference. I have the following three lines extra:

<entry key="process.statusOutputDirectory" value="C:\Projects\ssm.migration\output\Dev\contacts" />
<entry key="dataAccess.readUTF8" value="true"/>
<entry key="sfdc.debugMessagesFile" value="C:\Projects\ssm.migration\output\Dev\contacts\1v1is2w5.f3o.log"/>

Of which the readUTF8 is probably not applicable to you (and should definitely not matter), if you haven't already applied it. Your CSV will be ANSI. The other entries shouldn't hurt, but shouldn't matter either. Defining a debugMessagesFile with debugMessages set to false will not break things, so try that. Lastly, defining a valid statusOutputDirectory will also not hurt, albeit it doesn't do anything. Add that, too, and see if it makes a difference.

The only thing you had in addition is the encryptionKeyFile, which should not matter at all. Do you need it? Or can you do with just the encrypted password (which works just fine for me).

For further error checking, please attach a log file/log excerpt of your long running process. Does it actually run in Bulk API mode? Does it work if you switch off the serial mode and do parallel processing (I have the experience this is somewhat instable)?

Answer 4

I see you are using the sfdc.useBulkAPIparameter, which is advisable given you are working with 2 million records. However, the sfdc.loadBatchSize parameter may not exceed 200:

In a single insert, update, upsert, or delete operation, records moving to or from Salesforce are processed in increments of this size. The maximum value is 200. We recommend a value between 50 and 100. Sample value: 100

This may be contributing to your issues. As seen below, I have a bean working with Bulk API and my config file specifies 200.

My process-conf.xml

<bean id="closedWorkOrderUpdate" class="com.salesforce.dataloader.process.ProcessRunner" singleton="false">
  <description></description>
    <property name="name" value="closedWorkOrderUpdate"/>
    <property name="configOverrideMap">
        <map>
            <entry key="sfdc.debugMessagesFile" value="C:\Users\gazaino\Projects\DL\Opportunity\log\closedWorkOrderUpdate.log"/>
            <entry key="sfdc.entity" value="Opportunity"/>
            <entry key="process.operation" value="update"/>
            <entry key="sfdc.insertNulls" value="true" />
            <entry key="sfdc.useBulkApi" value="true" />
            <entry key="process.mappingFile" value="C:\Users\gazaino\Projects\DL\Opportunity\closedWorkOrderUpdate.sdl"/>
            <entry key="dataAccess.type" value="csvRead"/>
            <entry key="dataAccess.name" value="C:\Users\gazaino\Projects\DL\Opportunity\closedWorkorderExtract.csv"/>
            <entry key="process.statusOutputDirectory" value="C:\Users\gazaino\Projects\DL\Opportunity\Result"/>
            <entry key="process.outputError" value="C:\Users\gazaino\Projects\DL\Opportunity\Result\closedWorkOrderUpdateError.csv"/>
            <entry key="process.outputSuccess" value="C:\Users\gazaino\Projects\DL\Opportunity\Result\closedWorkOrderUpdateSuccess.csv"/>
        </map>
    </property>
</bean>

My config.properties

sfdc.endpoint=https://test.salesforce.com
sfdc.username=XXXXXXXXXXXXX
process.encryptionKeyFile=C:\\Users\\gazaino\\Projects\\DL\\key.txt
sfdc.password.int=XXXXXXXXX
sfdc.password=XXXXXXXXX
sfdc.password.prod=XXXXXXXX
sfdc.debugMessages=true
sfdc.enableLastRunOutput=false
sfdc.loadBatchSize=200
sfdc.timeoutSecs=600
sfdc.extractionRequestSize=500

CLI config.properties info:

File interactions and best practices

The previous description walked you through a simple example, using a version of the process-conf.xml file with one Process Bean listing. In reality, there are three places where you can specify configuration parameters - on the command line, as you did with the configuration directory and the Process Bean name, in the config.properties file, or in the process-conf.xml file. These three locations can all be used in running a single job.

Property value interactions

There are three places where you can define property-value pairs -

The command line

The command line requires two parameters - the configuration directory and the name of the process. You can also include any other property value pairs that could be listed in the process-conf.xml file. Any property-value pairs included as part of the command line are used in place of any other specifications in the other files. config.properties File located in the configuration file, which can contain any property-value pairs. Any property-value pairs listed in this file are used in place of their specification in the process-conf.xml file. process-conf.xml File that contains properties for all processes. Each process has it's own section in the file. To invoke a particular process defined in process-conf.xml pass the name of the process in the command via the process.name parameter. For example; jar -cp DataLoader.jar -Dsalesforce.config.dir=conf com.salesforce.lexiloader.process.ProcessRunner process.name=csvUpsertAccount. Otherwise, the process-conf.xml file will not be referenced and all required Data Loader values will need to be entered in the config.properties file.

Best practices

The scope of arguments in the command line is the individual process being called. The scope of the property-value pairs in the process-conf.xml file relate to the specific process that contains the pair. The property-value pairs in the optional config.properties file relate to all processes.

These differences naturally lead to a preferred set of practices. The process-conf.xml file acts as a sort of repository, with all the necessary configuration parameters for all possible processes. The config.properties file should be used to hold those parameters that remain the same over all or most of the processes, such as the sfdc.endpoint parameter. The parameters used as part of the command line are specific to individual runs of a single process.

Hope that helps.