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.
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
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
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.
Superb post. This post is going to help me for sure. Thanks for writing this
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.
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.
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!
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/
Hi Jitendra,
Following error in copy csv file created by dataloader to ftp server
java.lang.NumberFormatException: For input string “21”
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
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?
Hi Jitendra,
can we send the csv file to SFTP using the above mention notes/steps ?
Yes, I have updated this post to show how file can be saved on SFTP
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.
Great work, Jitendra,
Can I follow this post to daily back up on FTP server. Thanks.
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.
Hi Jitendra,
I only want to say THANKS!!!!!!!
It was very useful!!!!
Regards.
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
Even I need help for the same. Please let me know if you get some link or idea for the same.
Will it work for jitter bit as well ?
I don’t believe, Jitterbit is different application with different configurations