+ Start a Discussion
Bradley DelauneBradley Delaune 

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!

Best Answer chosen by Admin (Salesforce Developers) 
Bradley DelauneBradley Delaune

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:

<property name="url" value="jdbc:sqlserver://localhost:1433;databaseName=YOUR_DATABASE_NAME;"/>

 If anyone needs help getting their DataLoader connected to a MSSQL server, please let me know.  I'd be glad to help.

All Answers

Vinita_SFDCVinita_SFDC

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.

Bradley DelauneBradley Delaune

@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.

Bradley DelauneBradley Delaune

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 

jdbc:sqlserver://localhost:1433;databaseName=dltest;

 It still wont connect and gives me this log: 

2013-07-18 10:06:00,022 ERROR [accountMasterProcess] database.DatabaseContext initConnection (DatabaseContext.java:87) - Database error encountered during connecting for database configuration: queryAccountAll.  Sql error: Cannot create PoolableConnectionFactory (The TCP/IP connection to the host (local), port 1433 has failed. Error: "null. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".).
org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (The TCP/IP connection to the host (local), port 1433 has failed. Error: "null. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".)
	at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
	at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)
	at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
	at com.salesforce.dataloader.dao.database.DatabaseContext.initConnection(DatabaseContext.java:80)
	at com.salesforce.dataloader.dao.database.DatabaseContext.checkConnection(DatabaseContext.java:72)
	at com.salesforce.dataloader.dao.database.DatabaseReader.checkConnection(DatabaseReader.java:235)
	at com.salesforce.dataloader.process.ProcessRunner.run(ProcessRunner.java:134)
	at com.salesforce.dataloader.process.ProcessRunner.run(ProcessRunner.java:100)
	at com.salesforce.dataloader.process.ProcessRunner.main(ProcessRunner.java:253)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host (local), port 1433 has failed. Error: "null. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
	at com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:241)
	at com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2243)
	at com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:491)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1309)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:991)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:827)
	at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1012)
	at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
	at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
	at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1556)
	at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545)
	... 8 more
2013-07-18 10:06:00,032 FATAL [main] process.ProcessRunner topLevelError (ProcessRunner.java:238) - Unable to run process accountMasterProcess
java.lang.RuntimeException: com.salesforce.dataloader.exception.DataAccessObjectInitializationException: Database error encountered during connecting for database configuration: queryAccountAll.  Sql error: Cannot create PoolableConnectionFactory (The TCP/IP connection to the host (local), port 1433 has failed. Error: "null. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".).
	at com.salesforce.dataloader.process.ProcessRunner.run(ProcessRunner.java:162)
	at com.salesforce.dataloader.process.ProcessRunner.run(ProcessRunner.java:100)
	at com.salesforce.dataloader.process.ProcessRunner.main(ProcessRunner.java:253)
Caused by: com.salesforce.dataloader.exception.DataAccessObjectInitializationException: Database error encountered during connecting for database configuration: queryAccountAll.  Sql error: Cannot create PoolableConnectionFactory (The TCP/IP connection to the host (local), port 1433 has failed. Error: "null. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".).
	at com.salesforce.dataloader.dao.database.DatabaseContext.initConnection(DatabaseContext.java:88)
	at com.salesforce.dataloader.dao.database.DatabaseContext.checkConnection(DatabaseContext.java:72)
	at com.salesforce.dataloader.dao.database.DatabaseReader.checkConnection(DatabaseReader.java:235)
	at com.salesforce.dataloader.process.ProcessRunner.run(ProcessRunner.java:134)
	... 2 more
Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (The TCP/IP connection to the host (local), port 1433 has failed. Error: "null. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".)
	at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
	at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)
	at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
	at com.salesforce.dataloader.dao.database.DatabaseContext.initConnection(DatabaseContext.java:80)
	... 5 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host (local), port 1433 has failed. Error: "null. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
	at com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:241)
	at com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2243)
	at com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:491)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1309)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:991)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:827)
	at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1012)
	at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
	at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
	at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1556)
	at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545)
	... 8 more

 

Bradley DelauneBradley Delaune

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:

<property name="url" value="jdbc:sqlserver://localhost:1433;databaseName=YOUR_DATABASE_NAME;"/>

 If anyone needs help getting their DataLoader connected to a MSSQL server, please let me know.  I'd be glad to help.

This was selected as the best answer
Ravi Prakash Lal 9Ravi Prakash Lal 9
Hi Bradley,

Do you have any idea for doing the same with an oracle DB.

Would appreciate any help.

Regards
Ravi Prakash Lal
Sarbjeet Heera 5Sarbjeet Heera 5
Hi Bradley Delaune,
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