Ending your search for using command line data loader with MySQL, Oracle, MS-SQL Server

I find Cliq for command line data loader very handy as in you can get ready with the basic configuration in minutes. This post describes how you can use CLI to import, export, insert and update data to and fro between salesforce.com and databases like MySQL, Oracle 10g and MS-SQL Server.

It is useful when you have an existing website that generates leads in your internal database and you want to import those leads to salesforce.com.

First of all, use cliq to create a export process and use the sample database-conf.xml provided with the Apex Data Loader.

1. Copy the database-conf.xml file from the samples directory in data loader setup folder and paste it in the same folder as process-conf.xml
2. Now modify the following properties in the process-conf.xml file:
1. Set dataAccess.type property in process-conf as databaseRead or databaseWrite(we are writing to our Access db here, so we will use databaseWrite)
2. Set dataAccess.Name property as name of the bean called in the database-conf.xml file(we are calling the bean insertAccount)
3. Create a DSN connection by going to control panel > Administrative Tools > Data Sources (ODBC)
4. Go to Data Sources(ODBC) and double click on MS Access Database.
5. Specify a Data Source Name, say dsn1.
6. Click on Select and choose your MS-Access database file(.mdb) 
7. Open database-conf.xml and modify as follows:
1. In dbDataSource bean, change the url property to jdbc:odbc:dsn1, it will look like: <property name="url" value="jdbc:odbc:dsn1"/>
2. Search for insertAccount bean in database-conf.xml and locate the ref for the property name(in our case it is insertAccountSql) as shown in the image.
3. In insertAccountSql bean change the sqlString property to the insert SQL command for your database, for eg:
4. INSERT INTO MyAccounts (Acc_id, ACCOUNT_NAME, BUSINESS_PHONE) VALUES (@ID@, @Name@, @phone)

Note: The values between @’s are the mapping values from .sdl file that would have come as the column headers in csv file, if we are not using dataAccess.type as databaseRead or databaseWrite.Also, since there is no SDL file being used in export process as in here, so the values will be the names of the fields in salesforce.

     8.  Okay!! We are almost done, now save all the files and go to command prompt.

     9.  Move to the folder for cliq process and run the DOS batch file(export_, in our case)

    10. Open your Access database table and check the values.

Here is how process-conf.xml will look like:

<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
    <bean id="export_" class="com.salesforce.dataloader.process.ProcessRunner" singleton="false">
        <description>Created by Dataloader Cliq.</description>
        <property name="name" value="export_"/>
        <property name="configOverrideMap">
            <map>
                <entry key="dataAccess.name" value="D:\SF Tools\cliq_process\export_\write\export_.csv"/>
                <entry key="dataAccess.readUTF8" value="true"/>
                <entry key="dataAccess.type" value="databaseWrite"/>
                <entry key="dataAccess.name" value="insertAccountSql"/>
                <entry key="dataAccess.writeUTF8" value="true"/>
                <entry key="process.enableExtractStatusOutput" value="true"/>
                <entry key="process.enableLastRunOutput" value="true"/>
                <entry key="process.lastRunOutputDirectory" value="D:\SF Tools\cliq_process\export_\log"/>
                <entry key="process.operation" value="extract_all"/>
                <entry key="process.statusOutputDirectory" value="D:\SF Tools\cliq_process\export_\log"/>
                <entry key="sfdc.endpoint" value="https://www.salesforce.com/services/Soap/u/21.0"/>
                <entry key="sfdc.entity" value="Account"/>
                <entry key="sfdc.extractionRequestSize" value="500"/>
                <entry key="sfdc.extractionSOQL" value="select id, name, phone from account"/>
                <entry key="sfdc.password" value="4eb77947398ff3a5d2ea19c5ae2f606717b24e228a14afbe491654"/>
                <entry key="sfdc.username" value="sankalp.jhingran@paper.com"/>
            </map>
        </property>
    </bean>
</beans>

Here is how the database-conf.xml will look like:

<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
<bean id="dbDataSource"
      class="org.apache.commons.dbcp.BasicDataSource"
      destroy-method="close">
    <property name="driverClassName" value="sun.jdbc.odbc.JdbcOdbcDriver"/>
    <property name="url" value="jdbc:odbc:dsn1"/>
    <property name="username" value="user"/>
    <property name="password" value="password"/>
</bean>

<bean id="insertAccount"
      class="com.salesforce.dataloader.dao.database.DatabaseConfig"
      singleton="true">
    <property name="sqlConfig" ref="insertAccountSql"/>
    <property name="dataSource" ref="dbDataSource"/>
</bean>
<bean id="insertAccountSql"
      class="com.salesforce.dataloader.dao.database.SqlConfig" singleton="true">
    <property name="sqlString">
        <value>
            INSERT INTO MyAccounts (Acc_id, ACCOUNT_NAME, BUSINESS_PHONE)
            VALUES (@ID@, @Name@, @phone)
        </value>
    </property>
    <property name="sqlParams">
        <map>
            <entry key="id"    value="java.lang.String"/>
            <entry key="Name"  value="java.lang.String"/>
            <entry key="Phone" value="java.lang.String"/>
        </map>
    </property>
</bean>
</beans>

For connecting to MySQL:

1. Download the MySQL JDBC connector http://www.mysql.com/downloads/connector/j/. Extract the jar file to the Data Loader home directory (the same dir as DataLoader.jar).
2. Change the URL property of dbDataSource as:
• <property name="url" value="jdbc:mysql://{MYSQL HOSTNAME}/{DATABASE NAME}"/>
• <property name="url" value="jdbc:mysql://localhost:3306/MyDB"/>

     3.  Edit export_.bat and add the mysql connector to the path as follows:

replace

call %DLPATH%\_jvm\bin\java.exe -cp %DLPATH%\DataLoader.jar

-Dsalesforce.config.dir=%DLCONF% com.salesforce.dataloader.process.ProcessRunner process.name=my_process

with

call %DLPATH%\_jvm\bin\java.exe -cp %DLPATH%\DataLoader.jar:%DLPATH%\mysql-connector-java-5.1.18-bin.jar

-Dsalesforce.config.dir=%DLCONF% com.salesforce.dataloader.process.ProcessRunner process.name=export_

For Oracle, use:

“jdbc:oracle:thin:@{serverName}:{portNumber}:sid”

NOTE: Instead of downloading JDBC drivers for the databases, every DB provides its ODBC connector for windows, so, you can use create a DSN for every DB like MySQL, Oracle, MS-SQL Server etc. and specify name of the DSN in the URL property.

I tried to make it as simple as possible, however scope of improvement is also there and hence feedback is highly appreciated.