7

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

  • I tried the following settings to see if they helped, no luck <entry key="sfdc.loadBatchSize" value="10000"/> <entry key="sfdc.entity" value="OrderItem"/> <entry key="process.operation" value="upsert"/> <entry key="sfdc.useBulkApi" value="true"/> <entry key="sfdc.bulkApiSerialMode" value="false"/> – Kris Moyse Feb 4 '16 at 21:29
  • 1
    Can you define 'slow'? How many records are you passing through your upsert? How long is it taking? Are there any process kicked off in your org that would make it take longer than usual to upsert this type of record? I'm doing a data migration in my org and it seems process at a normal rate - hour and a half for 200,000 records. – zainogj Feb 8 '16 at 15:31
  • I am loading over 2 million records which takes 30 mins through data loader but over 12 hours using Command Line. There is definitely a setting or default setting somewhere I need to change. – Kris Moyse Feb 9 '16 at 17:18
  • 2
    The source for DataLoader is available on GitHub. You could try raising the issue there. Or pull it down and step through to see what is going on. – Daniel Ballinger Feb 10 '16 at 0:12
2
+50

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.

  • I feel like that might be the problem but I dont have a version of the log-conf, do you have an example log-conf file? I have all that content in the process-conf.xml so didnt think I needed it. – Kris Moyse Feb 5 '16 at 21:19
  • 1
    I think you can just use the standard one in the sample file - but otherwise this is the one I am using: gist.github.com/britishboyindc/b15d8ce79884e4d60216 – BritishBoyinDC Feb 6 '16 at 4:19
  • I added that but didn't help out. Is there any alterations I need to make to it? – Kris Moyse Feb 9 '16 at 17:19
  • 1
    Not that I know of - I think I just copied from sample folder. – BritishBoyinDC Feb 9 '16 at 22:49
  • Come to think of it, I think I am having this same issue too. In STDOUT I'm getting all of the info logging. It's 25,000 records and it's taking well over an hour in batches of 200 (not bulk). If I weren't in the middle of this migration I would try this, hopefully sometime soon. Just for clarity, in @BritishBoyinDC gist you replaced the priority levels with 'OFF' what about the level value - leave it alone? – zainogj Feb 13 '16 at 23:47
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.

  • 2
    Don - cool, note DataLoader is now avail on Mac too with Summer 15 – cropredy Feb 3 '16 at 2:05
  • Hey, thanks for the hint. Haven't been checking for a while obviously. In that case, I'll try to make sure it will work for with the Mac Data Loader as well. Just need to get my hands on a Mac. On roadmap. – donmartin Feb 3 '16 at 7:20
  • I have everything set up so not really keen to use your .exe. I am loading over 2 million records which takes 30 mins through data loader but over 12 hours using Command Line. There is definitely a setting or default setting somewhere I need to change. – Kris Moyse Feb 4 '16 at 21:28
  • 1
    I understand. I have attached an alternative answer, perhaps you can find something in there. Good luck. – donmartin Feb 5 '16 at 8:50
1

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)?

1

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.

  • 1
    I did note that as well but dataloader UI goes to 2,000 if you use the bulk setting and it can go up to 10,000. developer.salesforce.com/forums/… – Kris Moyse Feb 9 '16 at 20:53
  • What is the difference between putting everything in the bean and in config.properties? – Kris Moyse Feb 9 '16 at 20:54
  • 1
    huh... great point. It's a centralized config file so you don't need to continue re-writing your CLI config parameters in your beans; shouldn't impact the speed. To my knowledge, the CLI uses the same jar file as the dataloader, so you should be able to use the same parameters you are using in the GUI. If that's not the case, you may want to try re-installing (I found that helps with other bugs) or opening a support ticket. Also - updating my answer with config.property file info. – zainogj Feb 9 '16 at 21:11
  • Ok makes sense. Im almost positive it is because it is writing the logs to the command line even though I have <entry key="sfdc.debugMessages" value="false"/>. – Kris Moyse Feb 9 '16 at 21:47
  • 1
    yea, I've got a similar bug with sfdc.enableLastRunOutput... my directory is loaded with last run files across all of my tables. Might be worth trying the config.properties file for that parameter, on v35 I've set debugMessages to false successfully. That makes sense about the debug log as well. Again, could be a bad install or maybe even try an older version – zainogj Feb 9 '16 at 21:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.