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

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 : https://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 - https://www.jitendrazaa.com/blog/salesforce/tutorial-of-command-line-dataloader-salesforce/ : Dataloader and ANT - https://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

Posted

in

by


Related Posts

Comments

25 responses to “Using Dataloader and ANT to backup Salesforce data on FTP or SFTP server – Video”

  1. Caspar Harmer Avatar
    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.

  2. Sudipta Deb Avatar
    Sudipta Deb

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

  3. Rohit Avatar
    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?

    1. Jitendra Zaa Avatar

      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.

  4. Ankush Avatar
    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.

    1. Lochana Avatar
      Lochana

      Hi I have the similar requirement, did you figure out the solution

  5. Harabi Fenne Avatar
    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!

    1. Jitendra Zaa Avatar

      Your requirement says “AccountNumber” will be dynamic, so you will need to generate Process-config.xml dynamically. I have done something like this here – https://www.jitendrazaa.com/blog/salesforce/automate-command-line-dataloader-using-ant-with-dynamic-arguments-and-query/

  6. Krishan Avatar
    Krishan

    Hi Jitendra,
    Following error in copy csv file created by dataloader to ftp server

    java.lang.NumberFormatException: For input string “21”

  7. Krishan Avatar
    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

  8. Sheetal Avatar
    Sheetal

    Hi,

    Your post is very helpful. Could you kindly tell me if i can do an extract without writing a soql at sfdc.extractionSOQL but instead use a package.xml where i can extract all the objects, metadata etc?

  9. rohit Avatar
    rohit

    Hi Jitendra,

    can we send the csv file to SFTP using the above mention notes/steps ?

    1. Jitendra Zaa Avatar

      Yes, I have updated this post to show how file can be saved on SFTP

  10. Pallavi Sharma Avatar
    Pallavi Sharma

    Hi Jeetendra,

    I find this article very useful. However, I have 2 more slightly different requirements. I have to fetch data from FTP server and map it into salesforce. This file will be of type text. And same type of text file will need to be placed on that FTP server. Please suggest.

  11. Duc Nguyen Avatar
    Duc Nguyen

    Great work, Jitendra,
    Can I follow this post to daily back up on FTP server. Thanks.

  12. sandeep Avatar
    sandeep

    Hi Jitendra,
    I have a question, can we integrate SFTP server with salesforce and download PDF files from the server and add them as attachments to records in an object. Any help on this is greatly appreciated.

  13. Jerry B. Avatar
    Jerry B.

    Hi Jitendra,

    I only want to say THANKS!!!!!!!

    It was very useful!!!!

    Regards.

  14. ameya sane Avatar

    Hi Jitendra,
    Thanks a lot for this post..
    However, my requirement is the other way round..
    I need to get files from SFTP location and put them in salesforce.
    These files will go under ‘Files’ related list in Case object.
    Any guidance on how it could be done?

    Regards,
    Amey

    1. Keyur Avatar
      Keyur

      Even I need help for the same. Please let me know if you get some link or idea for the same.

  15. Nilesh Borse Avatar

    Will it work for jitter bit as well ?

    1. Jitendra Avatar

      I don’t believe, Jitterbit is different application with different configurations

  16. Anonymous Avatar
    Anonymous

    Can we also fetch filtered data from salesforce along with attachments e.g. I want to fetch all the opportunities created today with xyz criteria as well as all the related files to those opportunities and send it over to FTP. Can I do that via this approach?

  17. Kanchan Kumar Avatar
    Kanchan Kumar

    Hi Jitendra, Thank you for sharing your knowledge with us.

    I have a query that, is apache ant is for free to use for the same ?

  18. Kanchan Kumar Avatar
    Kanchan Kumar

    Hi Jitendra, Thank you for sharing your knowledge with us.

    I have a query, can we use this apache ant is for free or does it required licence if I want to use for my project

  19. Ajit Singh Avatar
    Ajit Singh

    Hi Jitendra, Thank you for sharing your knowledge with us. Can we do vice versa process like importing pdf file from ftp to salesforce object

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Discover more from Jitendra Zaa

Subscribe now to keep reading and get access to the full archive.

Continue Reading