+ Start a Discussion
AmulAmul 

DATALOAD CLI INTEGRATION FOR SQL SERVER with Salesforce

I wanted to connect SQL sever with Salesforce using Dataload CLI
for which i have created following Database.conf file
<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:SQLdsn:@DESKTOP-A1RF5KV:SalesforceDB"/>
<property name="username" value=""/>
<property name="password" value=""/>
</bean>
<bean id="queryLeadAll" class="com.salesforce.dataloader.dao.database.DatabaseConfig" singleton="true">
<property name="sqlConfig" ref="queryLeadAllSql"/>
<property name="dataSource" ref="dbDataSource"/>
</bean>
<bean id="queryAccount" class="com.salesforce.dataloader.dao.database.DatabaseConfig" singleton="true">
<property name="sqlConfig" ref="queryAccountSql"/>
<property name="dataSource" ref="dbDataSource"/>
</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="updateAccount" class="com.salesforce.dataloader.dao.database.DatabaseConfig" singleton="true">
<property name="sqlConfig" ref="updateAccountSql"/>
<property name="dataSource" ref="dbDataSource"/>
</bean>
<bean id="deleteAccountAll" class="com.salesforce.dataloader.dao.database.DatabaseConfig" singleton="true">
<property name="sqlConfig" ref="deleteAccountAllSql"/>
<property name="dataSource" ref="dbDataSource"/>
</bean>
<bean id="queryLeadAllSql" class="com.salesforce.dataloader.dao.database.SqlConfig" singleton="true">
<property name="sqlString">
<value>
SELECT LASTNAME, FIRSTNAME, PHONE, LEADSTATUS, EMAIL, LEADID, COMPANY
FROM dbo.Lead
</value>
</property>
<property name="columnNames">
<list>
<value>lastname</value>
<value>firstname</value>
<value>phone</value>
<value>leadstatus</value>
<value>email</value>
<value>LeadId</value>
<value>company</value>
</list>
</property>
</bean>
<bean id="queryAccountSql" class="com.salesforce.dataloader.dao.database.SqlConfig" singleton="true">
<property name="sqlString">
<value>
SELECT ACCOUNT_NAME, BUSINESS_PHONE, ACCOUNT_EXT_ID, ANNUAL_REVENUE, LAST_UPDATED, ACCOUNT_NUMBER
FROM TableOwner.Accounts
WHERE LAST_UPDATED > @process.lastRunDate@
</value>
</property>
<property name="columnNames">
<list>
<value>account_name</value>
<value>business_phone</value>
<value>account_ext_id</value>
<value>annual_revenue</value>
<value>last_updated</value>
<value>account_number</value>
</list>
</property>
<property name="sqlParams">
<map>
<entry key="process.lastRunDate" value="java.sql.Timestamp"/>
</map>
</property>
</bean>
<bean id="updateAccountSql" class="com.salesforce.dataloader.dao.database.SqlConfig" singleton="true">
<property name="sqlString">
<value>
update TableOwner.Accounts accounts
set accounts.account_name = @account_name@,
accounts.business_phone = @business_phone@,
accounts.sfdc_account_id = @sfdc_account_id@,
accounts.annual_revenue = @annual_revenue@,
accounts.account_number = @account_number@
where
accounts.ACCOUNT_EXT_ID = @account_ext_id@
</value>
</property>
<property name="sqlParams">
<map>
<entry key="account_name" value="java.lang.String"/>
<entry key="business_phone" value="java.lang.String"/>
<entry key="sfdc_account_id" value="java.lang.String"/>
<entry key="annual_revenue" value="java.lang.Double"/>
<entry key="account_ext_id" value="java.lang.String"/>
<entry key="account_number" value="java.lang.String"/>
</map>
</property>
</bean>
<bean id="insertAccountSql" class="com.salesforce.dataloader.dao.database.SqlConfig" singleton="true">
<property name="sqlString">
<value>
INSERT INTO TableOwner.Accounts (
ACCOUNT_NAME, BUSINESS_PHONE, SFDC_ACCOUNT_ID, ANNUAL_REVENUE, ACCOUNT_EXT_ID, ACCOUNT_NUMBER)
VALUES (@account_name@, @business_phone@, @sfdc_account_id@, @annual_revenue@, @account_ext_id@, @account_number@)
</value>
</property>
<property name="sqlParams">
<map>
<entry key="account_name" value="java.lang.String"/>
<entry key="business_phone" value="java.lang.String"/>
<entry key="sfdc_account_id" value="java.lang.String"/>
<entry key="annual_revenue" value="java.lang.Double"/>
<entry key="account_ext_id" value="java.lang.String"/>
<entry key="account_number" value="java.lang.String"/>
</map>
</property>
</bean>
<bean id="deleteAccountAllSql" class="com.salesforce.dataloader.dao.database.SqlConfig" singleton="true">
<property name="sqlString">
<value>
DELETE FROM TableOwner.Accounts
</value>
</property>
</bean>
</beans>
and I have also created Process.conf file
<beans>
<bean id="LeadInsert" class="com.salesforce.dataloader.process.ProcessRunner" singleton="false">
<description>Inserts Lead names from SQL server into Lead object.</description>
<property name="name" value="LeadInsert"/>
<property name="configOverrideMap">
<map>
<entry key="process.encryptionKeyFile" value="C:\Users\abara\Desktop\SQL-DATALOAD CLI\key.txt"/>
<entry key="sfdc.debugMessages" value="false"/>
<entry key="sfdc.debugMessagesFile" value="C:\Users\abara\Desktop\SQL-DATALOAD CLI\csvInsertArtist.log"/>
<entry key="sfdc.endpoint" value="https://login.salesforce.com"/>
<entry key="sfdc.username" value="amulhai@gmail.com"/>
<entry key="sfdc.password" value="575a9df11d375816dee606ecb0ea1c91"/>
<entry key="sfdc.timeoutSecs" value="540"/>
<entry key="sfdc.loadBatchSize" value="1"/>
<entry key="sfdc.externalIdField" value="StakkonForce__Lead_Key__c"/>
<entry key="sfdc.entity" value="Lead"/>
<entry key="process.operation" value="upsert"/>
<entry key="process.mappingFile" value="C:\Users\abara\Desktop\SQL-DATALOAD CLI\leadmap.sdl"/>
<entry key="process.outputError" value="C:\Users\abara\Desktop\SQL-DATALOAD CLI\errorInsertArtist.csv"/>
<entry key="process.outputSuccess" value="C:\Users\abara\Desktop\SQL-DATALOAD CLI\successInsertArtist.csv"/>
<entry key="dataAccess.name" value="queryLeadAll"/> 
<entry key="dataAccess.type" value="databaseRead"/> 
<entry key="process.initialLastRunDate" value="2007-06-06T00:00:00.000-0800"/>
</map>
</property>
</bean>
</beans>
following is my Mapping file
#Mapping values
#Wed Jul 13 00:17:08 IST 2016
lastname=lastname
firstname=firstname
phone=Phone
leadstatus=Status
email=email
LeadId=StakkonForce__Lead_Key__c
company=Company
I am running following command but getting following error.
C:\Program Files (x86)\salesforce.com\Data Loader\bin>Process.bat "C:\Users\abara\Desktop\SQL-DATALOAD CLI" LeadInsert
Error:
Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot load JDBC driver class 'sun.jdbc.odbc.JdbcOdbcDriver'
Any idea?