function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
CuriousGCuriousG 

connect to sql server via CLI and using apex data loader 20.0

i have done some searches on my subject and have found some useful code but mostly using older versions of apex data loader, i am using apex data loader 20.0.  I believe i have adjusted my process-conf.xml file correctly because the only error messages i get now are based on whats going on inside my database-conf.xml as i try to connect to SQL server. 

 

i have tried to use jdbc connectivity and jtds and im afraid I just am not experienced enough in getting this connected . . . so i have a couple questions if anyone can help me it would be greatly appreciated.

 

1.  I have the apex data loader in my program files and my path is ../salesforce.com/Apex Data Loader 20.0/bin/conf with my process-conf.xml and database-conf.xml files residing there.  Is this where either the executable jar file needs to reside as well, either the jtds or the sqljdbc?

 

2. using Apex Data Loader 20.0 has anyone successfully been able to extract data from salesforce to a SQL table?

 

3. If someone has a few links they could point me in that would be great too, i have seen some links from these forums but none using data loader 20.0, or is there a step by step connection to SQL through the CLI? document out there, i have seen the .pdfs but they didnt really go step by step in connecting via the CLI.

 

Im sure i have something wrong in my code and I feel like maybe I'm close but i was hopeing there was some code i could see to help me further, any help in this matter is greatly appreciated.

 

 

Here is the code for my database-conf.xml file, attempting to use jtds connectivity

 

 

<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
 <bean id="dbDataSource"
   class="com.salesforce.dataloader.process.ProcessRunner"
   destroy-method="close">
   <property name="driverClassName" value="net.sourceforge.jtds.jdbc.Driver"/>
     <property name="url" value="jdbc:jtds:sqlserver://My Database Server ;databaseName=My Database Name;"/>
       <property name="name" value="test"/>
       <property name="password" value="test"/>     
 </bean>
 <bean id="insertInfo"
      class="com.salesforce.dataloader.dao.database.DatabaseConfig"
      singleton="true">
    <property name="sqlConfig" ref="insertInfoSql1"/>
    <property name="dataSource" ref="dbDataSource"/>
 </bean>
 <bean id="insertInfoSql1"
      class="com.salesforce.dataloader.dao.database.SqlConfig" singleton="true">
    <property name="sqlString">
      <value>          
            INSERT INTO Life_IVR_Test.Account
                 (
                      ID,
                      ISDELETED,
                      NAME
                    )   
              VALUES
                      (
                          @ID@,
                      @ISDELETED@,
                      @NAME@
                       )
      </value>
    </property>
    <property name="sqlParams">
        <map>
            <entry key= "ID" value= "java.lang.String"/>
            <entry key= "ISDELETED" value= "java.lang.String"/>
            <entry key= "NAME" value= "java.lang.String"/>
           
        </map>
    </property>
 </bean>

</beans>

 

 

 

 

 

 

 

CuriousGCuriousG

does anyone have a good database-conf.xml file that extracts data from salesforce to SQL, i am clearly doing something wrong and i cant quite figure it out, any help would be appreciated.

jcsjcs

Hi, I'm doing something slightly different, but have been able to load data from SF to SQL Server 2008 R2 using the SQL jbdc jar.

 

1.  I have the apex data loader in my program files and my path is ../salesforce.com/Apex Data Loader 20.0/bin/conf with my process-conf.xml and database-conf.xml files residing there.  Is this where either the executable jar file needs to reside as well, either the jtds or the sqljdbc?

 

As far as I understand, the jar file can be anywhere, you just need to state where it is. Using the Microsoft example, the modification to process.bat is like this (using your path, note that some resources note issues with long file names, so I've put sqljdbc.jar in a folder called C:\sqljdbc)

 

..\_jvm\bin\java.exe -classpath ..\DataLoader.jar;C:\sqljdbc\sqljdbc.jar -Dsalesforce.config.dir=%1 com.salesforce.dataloader.process.ProcessRunner %PROCESS_OPTION%

 

2. using Apex Data Loader 20.0 has anyone successfully been able to extract data from salesforce to a SQL table?

 

Yes, I've used this version. My main observation is that it seems to be best to use the actual Salesforce API names for fields everywhere, so in your example, IsDeleted rather than ISDELETED. That works even if the SQL column is isdeleted. When I tried to use all upper case references everywhere, I ended up with inserted rows that were entirely NULL. If you try to match SQL column names exactly then I find that the errors related to missing fields show up and rows are not inserted.

 

I've also sent my example via private message--if that helps you to get your solution working, perhaps you could post your final version and explain what needed to change.

mitkushmitkush

Hello,

 

Thanks for great information, Is it possible i can connect Salesforce database from SQL Server 2008 R2 management studio or Azure connetivity?.. as i needs to create some realtime data request to SF database. for some integration purpose

 

Cheers!!!

 

Mit

jcsjcs

Hi,

 

There are probably some other solutions, but in the past I've used DBAmp for this and it allows you to work with the Salesforce database as a linked server in Management Studio. You can do many SQL type operations (realtime) via the DBAmp provider, or use SOQL if you need better performance, but you can also just query the replicated database that DBAmp can manage, which is usually just as well.

 

 Having just setup the download of several Salesforce objects using this CLI approach, I can say that DBAmp's object synching is far easier to deal with, of course that comes at a license cost--given that I only have a short term need for this I'm trying the CLI. 

tggagnetggagne

I would love to see a working example.  Especially if it's relatively simple.

 

The documentation doesn't make it clear (to me) how a) test the db connection, b) connect an export from process-conf to SQL inside database-conf or c) why it's necessary for one bean to reference a sql bean and a connection bean and not just have the sql bean reference it itself.

 

 

MartyChangGSCMartyChangGSC

Hello, tggagne, I documented what I believe are the necessary steps in a blog post, "Data Loader bean for SQL Server 2012 Express".