Using Dataloader and ANT to backup Salesforce data on FTP or SFTP server – Video

Tutorial on how to use command line dataloader to save exported Salesforce backup file on FTP or SFTP server

We will be using command line dataloader from Salesforce and ANT script to extract data from Salesforce and backup on FTP server. I would recommend to go through this article first to get familiar with basics of commandline dataloader and followed by this article to get some insight that how we can use ANT with dataloader.

There can be hundreds of use cases where we need to backup data from salesforce and we have thousands of options. We can use Jitterbit, Mulesoft, Dataloader.io or some big ETL tools like BOOMI or informatica. However sometimes clients are not willing to pay hefty  amount on licensing cost of ETL tools and I love open source. In this article, we will be using open source solution to very common problem of backing up data on FTP server.

Complete code is available on my github account as well in case you need it.

Use Dataloader and ANT to back CSV file on FTP Server
Use Dataloader and ANT to back CSV file on FTP Server

Considering you know how to use command line dataloader, first challenge would be how to get it worked using ANT. Below code snippet show how we can create ANT macro to invoke dataloader to extract data.

<macrodef name="export_Account">
        <sequential>
            <java classname="com.salesforce.dataloader.process.ProcessRunner" classpath="D:\Program Files (x86)\salesforce.com\Data Loader\dataloader-33.0.0-uber.jar" failonerror="true">
                <sysproperty key="salesforce.config.dir" value="config"/>
                <arg line="process.name=Account"/>
            </java>
        </sequential>
    </macrodef>

Once data is extracted, we can copy csv file created by dataloader to ftp server using below code

<ftp server="${ftpServer}" port="${ftpPort}" userid="${ftpUserName}" password="${ftpPassword}" passive="yes" ` binary="no">
        <fileset dir="${localFolder}">
          <include name="**/*.csv"/>
        </fileset>
      </ftp>

parameters like “ftpServer”, “ftpPort” are coming from build.properties file. You can use FTP task from ANT to move local file to FTP server. Complete detail of capability and syntax of this task can be found on official site.

Complete code of build.xml

<?xml version="1.0" encoding="UTF-8"?>
<!-- 
@Author : Jitendra Zaa 
@Blog : http://JitendraZaa.com 
@Description : This ANT script depends on external dependency that can be downloaded from "http://commons.apache.org/proper/commons-net/download_net.cgi". : once downloaded, jar files can be added in any folder which should be available to Windows shell. : Some suggested path - ANT bin directory, Java lib directory. Or in any directive that is added in PATH env variable. : Getting started with Dataloader - http://www.jitendrazaa.com/blog/salesforce/tutorial-of-command-line-dataloader-salesforce/ : Dataloader and ANT - http://www.jitendrazaa.com/blog/salesforce/automate-command-line-dataloader-using-ant-with-dynamic-arguments-and-query/ -->
<project name="Export" default="all">
    <property file="build.properties"/>
    <property environment="env"/>
    <macrodef name="export_Account">
        <sequential>
            <java classname="com.salesforce.dataloader.process.ProcessRunner" classpath="D:\Program Files (x86)\salesforce.com\Data Loader\dataloader-33.0.0-uber.jar" failonerror="true">
                <sysproperty key="salesforce.config.dir" value="config"/>
                <arg line="process.name=Account"/>
            </java>
        </sequential>
    </macrodef>
    <target name="all">
        <export_Account />
    <ftp server="${ftpServer}" port="${ftpPort}" userid="${ftpUserName}" password="${ftpPassword}" passive="yes" ` binary="no">
        <fileset dir="${localFolder}">
          <include name="**/*.csv"/>
        </fileset>
      </ftp>
    </target>
</project>

sample configuration settings in build.properties

ftpServer=YOUR FTP SERVER
ftpPort=21
ftpUserName=USERNAME
ftpPassword=FTP PASSWORD
localFolder=C:\\Users\\Jitendra\\Desktop\\SFDC to FTP

Using SFTP with ANT

  • Download jsch.jar0.1.42 from here
  • For SFTP, we would need to use SCP ANT task

Sample ANT target

<target name="saveFiletoSFTP" description="Saving file produced by Dataloader to SFTP">
    <scp file="sample.csv" sftp="true" trust="true" todir="userid:password@host:/to/dir/" />
</target>

Video Demo:

Command line dataloader related configurations – process-conf.xml

<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
<bean id="Account" class="com.salesforce.dataloader.process.ProcessRunner" singleton="false">
		<description>Export Account Data</description>
		<property name="name" value="Account"/>
		<property name="configOverrideMap">

<map>
			<entry key="sfdc.enableLastRunOutput" value="false"/>
			<entry key="sfdc.entity" value="Account"/>
			<entry key="process.operation" value="extract"/>
			<entry key="sfdc.extractionRequestSize" value="500"/>
			<entry key="sfdc.extractionSOQL" value="SELECT Id FROM Account"/>
			<entry key="dataAccess.name" value="C:\Users\Jitendra\Desktop\SFDC to FTP\AccountExport.csv"/>
			<entry key="dataAccess.type" value="csvWrite"/>
			<entry key="dataAccess.writeUTF8" value="true" />
		</map>

		</property>
	</bean>
</beans>

config.properties

sfdc.debugMessages=true
process.encryptionKeyFile=C:\\Users\\Jitendra\\Desktop\\SFDC to FTP\\Key.txt
sfdc.debugMessagesFile = C:\\Users\\Jitendra\\Desktop\\SFDC to FTP\\FTP.log
sfdc.endpoint=https://login.salesforce.com
sfdc.username=jitendra.zaa5.ftpAccount@gmail.com.
sfdc.password=encryptedpasswordsdsdsdsdsd
sfdc.loadBatchSize=200
sfdc.timeoutSecs=600

Related posts

  • Caspar Harmer

    Nice post – I’ll keep this technique in mind for future integrations. I’ve done something like this in the past using the mac terminal and the “expect” command and it was not 100% reliable – ant is going to be much better.

  • Sudipta Deb

    Superb post. This post is going to help me for sure. Thanks for writing this

  • Rohit

    Hi Jitendra, I want it to extract only the records that have been modified (or created) since this process ran the last time.How can we achieve this? Can you please help?

    • Hi Rohit, You can save current process running time in some txt file using ANT, then reaf file and assign to ANT variable and use this ANT variable in SOQL query.

  • Ankush

    Hi Jitendra,

    I am very impressed with this post.
    But i dint get the way to fetch file from FTP and place into our databse (SFDC object).
    My requirement is to read data from FTP server and insert into SFDC Account object.
    We are not getting where we need to mention those FTP details and etc.
    It would be better if u show the way for it.
    Thanks.

  • Harabi Fenne

    Hi Jitendra,

    I need to rename the csv file extracted into this format: AccountNumber_YYYYMMDD_HHMM.
    I find it straight forward to append the date and time (YYYYMMDD_HHMM) into the file name. However, the AccountNumber is different with each extraction depends on which Account Number is being extracted.
    Can you please help to advice on this? Thank you!

  • Krishan

    Hi Jitendra,

    I have error in uploading file to FTP server, please help.

    C:Salesforce data on FTP server>ant all
    Buildfile: C:Salesforce data on FTP serverbuild.xml

    all:
    [java] 2016-07-21 18:10:32,436 INFO [main] controller.Controller initLog (Controller.java:389) – Using built-in logging configuration, no log-conf.xml in C:Salesforce data on FTP serverlog-conf.xml
    [java] 2016-07-21 18:10:32,452 INFO [main] controller.Controller initLog (Controller.java:391) – The log has been initialized
    [java] 2016-07-21 18:10:32,499 INFO [main] process.ProcessConfig getBeanFactory (ProcessConfig.java:104) – Loading process configuration from config file: C:Salesforce data on FTP serverconfigprocess-conf.xml
    [java] 2016-07-21 18:10:32,718 INFO [main] support.AbstractApplicationContext prepareRefresh (AbstractApplicationContext.java:495) – Refreshing org.springframework.context.support.FileSystemXmlApplicationContext@8080bb: startup date [Thu Jul 21 18:10:32 IST 2016]; root of context hierarchy
    [java] 2016-07-21 18:10:32,907 INFO [main] xml.XmlBeanDefinitionReader loadBeanDefinitions (XmlBeanDefinitionReader.java:315) – Loading XML bean definitions from file [C:Salesforce data on FTP serverconfigprocess-conf.xml]
    [java] 2016-07-21 18:10:33,048 INFO [main] support.DefaultListableBeanFactory preInstantiateSingletons (DefaultListableBeanFactory.java:557) – Pre-instantiating singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@1d5872c: defining beans [Account]; root of factory hierarchy
    [java] 2016-07-21 18:10:33,126 INFO [Account] controller.Controller initConfig (Controller.java:327) – config dir created at C:Salesforce data on FTP serverconfig
    [java] 2016-07-21 18:10:34,784 INFO [Account] controller.Controller initConfig (Controller.java:355) – The controller config has been initialized
    [java] 2016-07-21 18:10:34,784 INFO [Account] process.ProcessRunner run (ProcessRunner.java:116) – Initializing process engine
    [java] 2016-07-21 18:10:34,784 INFO [Account] process.ProcessRunner run (ProcessRunner.java:119) – Loading parameters
    [java] 2016-07-21 18:10:34,784 INFO [Account] config.LastRun load (LastRun.java:96) – Last run info will be saved in file: C:Salesforce data on FTP serverconfigAccount_lastRun.properties
    [java] 2016-07-21 18:10:34,816 INFO [Account] process.ProcessRunner run (ProcessRunner.java:127) – Logging in to: https://login.salesforce.com
    [java] 2016-07-21 18:10:34,816 INFO [Account] client.PartnerClient login (PartnerClient.java:478) – Beginning Partner Salesforce login ….
    [java] [WSC][Launcher.main:112]Log file already exists, appending to D:SoftwaresSalesforceData Loader through command promptFTP.log
    [java] 2016-07-21 18:10:34,925 INFO [Account] client.PartnerClient loginInternal (PartnerClient.java:519) – Salesforce login to https://login.salesforce.com/services/Soap/u/34.0 as user krishan4184@salesforce.com
    [java] [WSC][Launcher.main:112]Log file already exists, appending to D:SoftwaresSalesforceData Loader through command promptFTP.log
    [java] 2016-07-21 18:10:37,327 INFO [Account] dao.DataAccessObjectFactory getDaoInstance (DataAccessObjectFactory.java:51) – Instantiating data access object: C:\Users\kg.yadav.HFCL\Desktop\SFDC to FTP\Export.csv of type: csvWrite
    [java] 2016-07-21 18:10:37,327 INFO [Account] process.ProcessRunner run (ProcessRunner.java:132) – Checking the data access object connection
    [java] 2016-07-21 18:10:37,343 INFO [Account] process.ProcessRunner run (ProcessRunner.java:137) – Setting field types
    [java] 2016-07-21 18:10:39,285 INFO [Account] process.ProcessRunner run (ProcessRunner.java:141) – Setting object reference types
    [java] 2016-07-21 18:10:44,409 INFO [Account] process.ProcessRunner run (ProcessRunner.java:145) – Creating Map
    [java] 2016-07-21 18:10:44,440 INFO [Account] action.OperationInfo instantiateAction (OperationInfo.java:95) – Instantiating action for operation: extract
    [java] 2016-07-21 18:10:44,440 INFO [Account] controller.Controller executeAction (Controller.java:120) – executing operation: extract
    [java] 2016-07-21 18:10:44,440 INFO [Account] action.AbstractAction execute (AbstractAction.java:120) – Loading: extract
    [java] 2016-07-21 18:10:45,706 INFO [Account] progress.NihilistProgressAdapter setSubTask (NihilistProgressAdapter.java:78) – Processed 16 of 16 total records. Rate: 0 records per hour. Estimated time to complete: 0 minutes and 0 seconds. There are 16 successes and 0 errors.
    [java] 2016-07-21 18:10:45,706 INFO [Account] progress.NihilistProgressAdapter doneSuccess (NihilistProgressAdapter.java:63) – The operation has fully completed. There were 16 successful extractions and 0 errors.

    BUILD FAILED
    C:Salesforce data on FTP serverbuild.xml:21: error during FTP transfer: java.net.UnknownHostException: “ftp.drivehq.com”
    at java.net.Inet6AddressImpl.lookupAllHostAddr(Native Method)
    at java.net.InetAddress$2.lookupAllHostAddr(Unknown Source)
    at java.net.InetAddress.getAddressesFromNameService(Unknown Source)
    at java.net.InetAddress.getAllByName0(Unknown Source)
    at java.net.InetAddress.getAllByName(Unknown Source)
    at java.net.InetAddress.getAllByName(Unknown Source)
    at java.net.InetAddress.getByName(Unknown Source)
    at org.apache.commons.net.SocketClient.connect(SocketClient.java:209)
    at org.apache.tools.ant.taskdefs.optional.net.FTP.execute(FTP.java:2438)
    at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:293)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:106)
    at org.apache.tools.ant.Task.perform(Task.java:348)
    at org.apache.tools.ant.Target.execute(Target.java:435)
    at org.apache.tools.ant.Target.performTasks(Target.java:456)
    at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1405)
    at org.apache.tools.ant.Project.executeTarget(Project.java:1376)
    at org.apache.tools.ant.helper.DefaultExecutor.executeTargets(DefaultExecutor.java:41)
    at org.apache.tools.ant.Project.executeTargets(Project.java:1260)
    at org.apache.tools.ant.Main.runBuild(Main.java:854)
    at org.apache.tools.ant.Main.startAnt(Main.java:236)
    at org.apache.tools.ant.launch.Launcher.run(Launcher.java:285)
    at org.apache.tools.ant.launch.Launcher.main(Launcher.java:112)

    My build.properties is below

    ftpServer=”ftp.drivehq.com”
    remotedir=”/My Documents”
    ftpPort=21
    ftpUserName=kg.yadav
    ftpPassword=b3cb6a02759ffbb28878818ccb64bd5f
    localFolder=C:\Users\kg.yadav.HFCL\Desktop\SFDC to FTP