Apex Code Development You need to sign in to do that
Don't have an account?
Dataloader Command Line Interface with MSSQL
Does anyone understand how to connect Apex DataLoader with a Microsoft SQL Database to moved data in either direction? Right now, I have my database-conf.xml set up 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="com.microsoft.sqlserver.jdbc.SQLServerDriver"/> <property name="url" value="jdbc:sqlserver://Localhost;databaseName=dltest;selectMethod=cursor;"/> <property name="username" value="test"/> <property name="password" value="test"/> </bean> <bean id="queryAccount" class="com.salesforce.dataloader.dao.database.SqlConfig" singleton="true"> <property name="sqlConfig" ref="queryAccountSql"/> <property name="dataSource" ref="dbDataSource"/> </bean> </beans>
The process-conf.xml file looks like this.
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd"> <beans> <bean id="upsertDLTest" class="com.salesforce.dataloader.process.ProcessRunner" singleton="false"> <description>.</description> <property name="name" value="upsertDLTest"/> <property name="configOverrideMap"> <map> <entry key="sfdc.debugMessages" value="true"/> <entry key="sfdc.debugMessagesFile" value="DEBUG LOG FILE"/> <entry key="sfdc.endpoint" value="ENDPOINT URL"/> <entry key="sfdc.username" value="MY USERNAME"/> <!-- password below has been encrypted using key file, therefore it will not work without the key setting: process.encryptionKeyFile the password is not a valid encrypted value, please generate the real value using encrypt.bat utility --> <entry key="sfdc.password" value="ENCRYPTED VALUE"/> <entry key="process.encryptionKeyFile" value="ENCRYPTION KEY FILE LOCATION"/> <entry key="sfdc.timeoutSecs" value="600"/> <entry key="sfdc.loadBatchSize" value="200"/> <entry key="sfdc.entity" value="DLTest__c"/> <entry key="process.operation" value="upsert"/> <entry key="process.mappingFile" value="MAPPING FILE"/> <entry key="process.statusOutputDirectory" value="STATUS LOG FOLDER"/> <entry key="dataAccess.name" value="queryAccount"/> <entry key="dataAccess.type" value="databaseRead"/> </map> </property> </bean>
I'd appreciate any help anyone can give me. Thanks!
Well I feel extremely dumb but I have solved the issue. I simply needed to actually make the SQL server listen on TCP/IP. In the future, the connection URL is valid as follows:
If anyone needs help getting their DataLoader connected to a MSSQL server, please let me know. I'd be glad to help.
All Answers
Hello,
In the process-conf.xml at line:
<entry key="process.mappingFile" value="MAPPING FILE"/>
i could not find where is "MAPPING FILE" defined. Please provide the path like: "C:\dataloader\samples\conf\accountmastermap.sdl"
For details please refer: https://community.informatica.com/servlet/JiveServlet/previewBody/2233-102-1-2479/6steps.pdf
Also you may try using Jitterbit Dataloader. More user friendly than the Data loader in general. You can define your DB connections from within the UI.
@Vinita_SFDC, I replaced all the paths to the files with capital letter names because I understand how the mapping file and log files work. Rather, I need information about connecting the dataloader to a MSSQL database so that DataLoader can be run on a schedule from the command line. No UI version of an uploader is going to the job I need.
Now I'm mostly having issues with the connection. I have DataLoader connected to the jdbc driver, but it can't make a connection. The URL I'm using is
It still wont connect and gives me this log:
Well I feel extremely dumb but I have solved the issue. I simply needed to actually make the SQL server listen on TCP/IP. In the future, the connection URL is valid as follows:
If anyone needs help getting their DataLoader connected to a MSSQL server, please let me know. I'd be glad to help.
Do you have any idea for doing the same with an oracle DB.
Would appreciate any help.
Regards
Ravi Prakash Lal
can you help me to solve the solution for insert data to salesforce from ms sql server i am trying and getting many error like TCP/IP Blocked or if you have any example of this
My process-conf.xml file is
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
<bean id="InsertContact" class="com.salesforce.dataloader.process.ProcessRunner" singleton="false">
<description>Created by Dataloader Cliq.</description>
<property name="name" value="InsertContact"/>
<property name="configOverrideMap">
<map>
<entry key="dataAccess.name" value="InserttoContact"/>
<entry key="dataAccess.readUTF8" value="true"/>
<entry key="dataAccess.type" value="databaseRead"/>
<entry key="dataAccess.writeUTF8" value="true"/>
<entry key="process.enableExtractStatusOutput" value="true"/>
<entry key="process.enableLastRunOutput" value="true"/>
<entry key="process.lastRunOutputDirectory" value="C:\dataloader\cliq_process\InsertContact\log"/>
<entry key="process.mappingFile" value="C:\dataloader\cliq_process\InsertContact\config\InsertContact.sdl"/>
<entry key="process.operation" value="insert"/>
<entry key="process.statusOutputDirectory" value="C:\dataloader\cliq_process\InsertContact\log"/>
<entry key="sfdc.bulkApiCheckStatusInterval" value="5000"/>
<entry key="sfdc.bulkApiSerialMode" value="5000"/>
<entry key="sfdc.debugMessages" value="false"/>
<entry key="sfdc.enableRetries" value="true"/>
<entry key="sfdc.endpoint" value="https://www.salesforce.com/services/Soap/u/34.0"/>
<entry key="sfdc.entity" value="Contact"/>
<entry key="sfdc.extractionRequestSize" value="500"/>
<entry key="sfdc.insertNulls" value="false"/>
<entry key="sfdc.loadBatchSize" value="100"/>
<entry key="sfdc.maxRetries" value="3"/>
<entry key="sfdc.minRetrySleepSecs" value="2"/>
<entry key="sfdc.noCompression" value="false"/>
<entry key="sfdc.password" value="c8e820ac0e039b5566da93f262dff793bcc0c206d487b24345a6d63927fb491774a5b06a2f891b8f"/>
<entry key="sfdc.proxyHost" value=""/>
<entry key="sfdc.proxyNtlmDomain" value=""/>
<entry key="sfdc.proxyPassword" value="f80a8187cf4eec85"/>
<entry key="sfdc.proxyPort" value=""/>
<entry key="sfdc.proxyUsername" value=""/>
<entry key="sfdc.timeoutSecs" value="60"/>
<entry key="sfdc.useBulkApi" value="false"/>
<entry key="sfdc.username" value="sarbjeetheera143@gmail.com"/>
</map>
</property>
</bean>
</beans>
AND database-conf.xml file is
<!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="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
<property name="url" value="jdbc:sqlserver://localhost:1433;databaseName=Employee;"/>
<property name="username" value="student"/>
<property name="password" value="student"/>
</bean>
<bean id="InserttoContact"
class="com.salesforce.dataloader.dao.database.DatabaseConfig"
singleton="true">
<property name="sqlConfig" ref="InsertContact"/>
<property name="dataSource" ref="dbDataSource"/>
</bean>
<bean id="InsertContact"
class="com.salesforce.dataloader.dao.database.SqlConfig" singleton="true">
<property name="sqlString">
<value>
SELECT FirstName,LastName From Contact
</value>
</property>
<property name="sqlParams">
<map>
<entry key="FirstName" value="java.lang.String"/>
<entry key="LastName" value="java.lang.String"/>
</map>
</property>
</bean>
</beans>
reply soon my id is sarbjeetheera@gmail.com