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
QingsongQingsong 

How to use Data Loader Command Line extract data into MS SQL 2005

 

 
I am using apex data loader 11.0 command line to extract data to CSV file, it's works, I would like to extract data from salesforce directly into MS SQL 2005, I was spent a lot of time to try and search the knowadge in this community but still not able to get this works, below is my code in database-con.xml:
Code:
<!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=CRMReport;"/>
    <property name="username" value="test"/>
    <property name="password" value="test"/>
</bean>


<bean id="tmpAccount"
      class="com.salesforce.lexiloader.dao.database.DatabaseConfig"
      singleton="true">
    <property name="sqlConfig" ref="queryAccount"/>
    <property name="dataSource" ref="dbDataSource"/>
</bean>
<bean id="queryAccount"
      class="com.salesforce.lexiloader.dao.database.SqlConfig" singleton="true">
    <property name="sqlString">
        <value>
 select Id, IsDeleted, MasterRecordId, 
  Name, Type, RecordTypeId, ParentId, 
  BillingStreet, BillingCity, BillingState, 
  BillingPostalCode, BillingCountry, ShippingStreet, 
  ShippingCity, ShippingState, ShippingPostalCode, 
  ShippingCountry, Phone, Fax, 
  Website, Industry, AnnualRevenue, 
  NumberOfEmployees, 
  Description, CurrencyIsoCode, 
  OwnerId, CreatedDate, CreatedById, 
  LastModifiedDate, LastModifiedById, 
  SystemModstamp, LastActivityDate, 
  Region__c, Annual_Procurment__c, 
  Trial_Balance_Code__c, Customer_Group__c, 
  SAP_VEGA_1_Account_Number__c, 
  SAP_VEGA_2_Account_Number__c, 
  QAD_Account_Number__c, 
  BAAN_Account_Number__c, Brief_Name__c,Customer_PBU__c
 from tmpAccount
        </value>
    </property>
</bean>
</beans>


 
and then I have process-conf.xml as well:
 
Code:
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
    <!-- Extract Account to CSV File -->
    <bean id="csvAccountExtractProcess"
          class="com.salesforce.lexiloader.process.ProcessRunner"
          singleton="false">
      <description>csvAccountExtract job gets account info from salesforce and saves info into a CSV file."</description>
        <property name="name" value="csvAccountExtract"/>
        <property name="configOverrideMap">
            <map>
                <entry key="sfdc.entity" value="Account"/>
                <entry key="process.operation" value="extract"/>
                <entry key="process.mappingFile" value="C:\Program Files\salesforce.com\Apex Data Loader 11.0\Delphi\Conf\ExtractAccountMap.sdl"/>
                <entry key="dataAccess.type" value="databaseWrite"/>
                <entry key="dataAccess.name" value="tmpAccount"/>
                <entry key="sfdc.extractionSOQL" value="Select Id, IsDeleted, MasterRecordId, Name, Type, 
                     RecordTypeId, ParentId, BillingStreet, BillingCity, 
                     BillingState, BillingPostalCode, BillingCountry, 
                     ShippingStreet, ShippingCity, ShippingState, ShippingPostalCode, 
                     ShippingCountry, Phone, Fax, Website, Industry, AnnualRevenue, 
                     NumberOfEmployees, Description, CurrencyIsoCode, OwnerId, 
                     CreatedDate, CreatedById, LastModifiedDate, LastModifiedById, 
                     SystemModstamp, LastActivityDate, Region__c, Annual_Procurment__c, 
                     Trial_Balance_Code__c, Customer_Group__c, 
                     SAP_VEGA_1_Account_Number__c, SAP_VEGA_2_Account_Number__c, 
                     QAD_Account_Number__c, BAAN_Account_Number__c, Brief_Name__c, 
                     Customer_PBU__c FROM Account"/>
            </map>
        </property>
    </bean>
</beans>

 

I downloaded the Microsoft SQL Server 2005 JDBC 1.2 into my C:\MSJDBC folder, and changed the original proccess.bat file to:
Code:
@echo off
if not [%1]==[] goto run
echo.
echo Usage: process ^<configuration directory^> ^[process name^]
echo.
echo      configuration directory -- directory that contains configuration files,
echo          i.e. config.properties, process-conf.xml, database-conf.xml
echo.
echo      process name -- optional name of a batch process bean in process-conf.xml,
echo          for example:
echo.
echo              process ../myconfigdir AccountInsert
echo.
echo          If process name is not specified, the parameter values from config.properties
echo          will be used to run the process instead of process-conf.xml,
echo          for example:
echo.
echo              process ../myconfigdir
echo.

goto end

:run
set PROCESS_OPTION=
if not [%2]==[] set PROCESS_OPTION=process.name=%2

..\_jvm\bin\java.exe -classpath ..\DataLoader.jar;C:\MSJDBC\sqljdbc_1.2\enu\sqljdbc.jar -Dsalesforce.config.dir=%1 

com.salesforce.lexiloader.process.ProcessRunner %PROCESS_OPTION%

:end

 
and then we I execute the extract in command line:
 
process.bat ./conf csvAccountExtractProcess
 
I was get below errors:
 

177924 [csvAccountExtract] DEBUG com.salesforce.lexiloader.client.PartnerClient - Beginning web service operation: query
320410 [csvAccountExtract] ERROR com.salesforce.lexiloader.dao.database.Database Reader  - Database error encountered while writing row#1 through row#215 (execute batch update). Database configuration: tmpAccount.  Sql error: The SELECT statement is not permitted in a batch..com.microsoft.sqlserver.jdbc.SQLServerException: The SELECT statement is not permitted in a batch.
 at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatementBatch(Unknown Source)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtBatchExecCmd.doExecute(Unknown Source)
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(Unknown Source)
        at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:294)
        at com.salesforce.lexiloader.dao.database.DatabaseWriter.writeRowList(DatabaseWriter.java:180)
        at com.salesforce.lexiloader.action.visitor.QueryVisitor.writeExtraction(QueryVisitor.java:205)
        at com.salesforce.lexiloader.action.visitor.QueryVisitor.visit(QueryVisitor.java:111)
        at com.salesforce.lexiloader.action.ExtractAction.execute(ExtractAction.java:108)
        at com.salesforce.lexiloader.controller.Controller.executeAction(Controller.java:126)
        at com.salesforce.lexiloader.process.ProcessRunner.run(ProcessRunner.java:136)
        at com.salesforce.lexiloader.process.ProcessRunner.main(ProcessRunner.java:228)
 at com.salesforce.lexiloader.process.ProcessRunner.main(ProcessRunner.java:228)

320410 [csvAccountExtract] INFO com.salesforce.lexiloader.action.progress.NihilistProgressAdapter  - Processed 214 of 214 total records. Rate: 24851000 records per hour. Estimated time to complete: 0 minutes and 0 seconds.  There are 0 successes and 214 errors.
320410 [csvAccountExtract] INFO com.salesforce.lexiloader.action.progress.NihilistProgressAdapter  - The extract has fully completed.  There were 0 successful extracts and 214 errors.

Looks like the SELECT statement have some problems, I am looking the solution here, any leads very be high appericated, Thanks
 


Message Edited by Qingsong on 04-15-2008 06:57 PM

Message Edited by Qingsong on 04-15-2008 07:09 PM

Message Edited by Qingsong on 04-15-2008 07:13 PM
Best Answer chosen by Admin (Salesforce Developers) 
QingsongQingsong
Good news, I was figure out what's the root cause, for extract data into SQL, It has to be a INSERT statement not a SELECT statement, so I did the change in database-conf.xml as below:
Code:
<!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=CRMReport;"/>
    <property name="username" value="test"/>
    <property name="password" value="test"/>
</bean>


<bean id="tmpAccount"
      class="com.salesforce.lexiloader.dao.database.DatabaseConfig"
      singleton="true">
    <property name="sqlConfig" ref="queryAccount"/>
    <property name="dataSource" ref="dbDataSource"/>
</bean>
<bean id="queryAccount"
      class="com.salesforce.lexiloader.dao.database.SqlConfig" singleton="true">
    <property name="sqlString">
        <value>
 INSERT INTO dbo.tmpAccount (Id, IsDeleted, MasterRecordId, 
  Name, Type, RecordTypeId, ParentId, 
  BillingStreet, BillingCity, BillingState, 
  BillingPostalCode, BillingCountry, ShippingStreet, 
  ShippingCity, ShippingState, ShippingPostalCode, 
  ShippingCountry, Phone, Fax, 
  Website, Industry, AnnualRevenue, 
  NumberOfEmployees, 
  Description, CurrencyIsoCode, 
  OwnerId, CreatedDate, CreatedById, 
  LastModifiedDate, LastModifiedById, 
  SystemModstamp, LastActivityDate, 
  Region__c, Annual_Procurment__c, 
  Trial_Balance_Code__c, Customer_Group__c, 
  SAP_VEGA_1_Account_Number__c, 
  SAP_VEGA_2_Account_Number__c, 
  QAD_Account_Number__c, 
  BAAN_Account_Number__c, Brief_Name__c,Customer_PBU__c)
 VALUES(@Id@,@IsDeleted@,@MasterRecordId@,@Name@,@Type@,@RecordTypeId@,@ParentId@,
  @BillingStreet@,@BillingCity@,@BillingState@,@BillingPostalCode@,
  @BillingCountry@,@ShippingStreet@,@ShippingCity@,@ShippingState@,
  @ShippingPostalCode@,@ShippingCountry@,@Phone@,@Fax@,@Website@,
  @Industry@,@AnnualRevenue@,@NumberOfEmployees@,@Description@,
  @CurrencyIsoCode@,@OwnerId@,@CreatedDate@,@CreatedById@,
  @LastModifiedDate@,@LastModifiedById@,@SystemModstamp@,
  @LastActivityDate@,@Region__c@,@Annual_Procurment__c@,
  @Trial_Balance_Code__c@,@Customer_Group__c@,@SAP_VEGA_1_Account_Number__c@,
  @SAP_VEGA_2_Account_Number__c@,@QAD_Account_Number__c@,
  @BAAN_Account_Number__c@,@Brief_Name__c@,@Customer_PBU__c@)
        </value>
    </property>
    <property name="sqlParams">
        <map>
        <entry key="Id"    value="java.lang.String"/>
 <entry key="IsDeleted"    value="java.lang.String"/>
 <entry key="MasterRecordId"    value="java.lang.String"/>
 <entry key="Name"    value="java.lang.String"/>
 <entry key="Type"    value="java.lang.String"/>
 <entry key="RecordTypeId"    value="java.lang.String"/>
 <entry key="ParentId"    value="java.lang.String"/>
 <entry key="BillingStreet"    value="java.lang.String"/>
 <entry key="BillingCity"    value="java.lang.String"/>
 <entry key="BillingState"    value="java.lang.String"/>
 <entry key="BillingPostalCode"    value="java.lang.String"/>
 <entry key="BillingCountry"    value="java.lang.String"/>
 <entry key="ShippingStreet"    value="java.lang.String"/>
 <entry key="ShippingCity"    value="java.lang.String"/>
 <entry key="ShippingState"    value="java.lang.String"/>
 <entry key="ShippingPostalCode"    value="java.lang.String"/>
 <entry key="ShippingCountry"    value="java.lang.String"/>
 <entry key="Phone"    value="java.lang.String"/>
 <entry key="Fax"    value="java.lang.String"/>
 <entry key="Website"    value="java.lang.String"/>
 <entry key="Industry"    value="java.lang.String"/>
 <entry key="AnnualRevenue"    value="java.lang.String"/>
 <entry key="NumberOfEmployees"    value="java.lang.String"/>
 <entry key="Description"    value="java.lang.String"/>
 <entry key="CurrencyIsoCode"    value="java.lang.String"/>
 <entry key="OwnerId"    value="java.lang.String"/>
 <entry key="CreatedDate"    value="java.lang.String"/>
 <entry key="CreatedById"    value="java.lang.String"/>
 <entry key="LastModifiedDate"    value="java.lang.String"/>
 <entry key="LastModifiedById"    value="java.lang.String"/>
 <entry key="SystemModstamp"    value="java.lang.String"/>
 <entry key="LastActivityDate"    value="java.lang.String"/>
 <entry key="Region__c"    value="java.lang.String"/>
 <entry key="Annual_Procurment__c"    value="java.lang.String"/>
 <entry key="Trial_Balance_Code__c"    value="java.lang.String"/>
 <entry key="Customer_Group__c"    value="java.lang.String"/>
 <entry key="SAP_VEGA_1_Account_Number__c"    value="java.lang.String"/>
 <entry key="SAP_VEGA_2_Account_Number__c"    value="java.lang.String"/>
 <entry key="QAD_Account_Number__c"    value="java.lang.String"/>
 <entry key="BAAN_Account_Number__c"    value="java.lang.String"/>
 <entry key="Brief_Name__c"    value="java.lang.String"/>
 <entry key="Customer_PBU__c"    value="java.lang.String"/>
        </map>
    </property>
</bean>
</beans>


 
then the mapfile should be same with database-conf and process-conf, it's CASE SENSITIVE, below is my ExtractAccountMap.sdl
Code:
#Mapping values
#Mon Jan 23 17:07:24 PST 2006
Id=Id
IsDeleted=IsDeleted
MasterRecordId=MasterRecordId
Name=Name
Type=Type
RecordTypeId=RecordTypeId
ParentId=ParentId
BillingStreet=BillingStreet
BillingCity=BillingCity
BillingState=BillingState
BillingPostalCode=BillingPostalCode
BillingCountry=BillingCountry
ShippingStreet=ShippingStreet
ShippingCity=ShippingCity
ShippingState=ShippingState
ShippingPostalCode=ShippingPostalCode
ShippingCountry=ShippingCountry
Phone=Phone
Fax=Fax
Website=Website
Industry=Industry
AnnualRevenue=AnnualRevenue
NumberOfEmployees=NumberOfEmployees
Description=Description
CurrencyIsoCode=CurrencyIsoCode
OwnerId=OwnerId
CreatedDate=CreatedDate
CreatedById=CreatedById
LastModifiedDate=LastModifiedDate
LastModifiedById=LastModifiedById
SystemModstamp=SystemModstamp
LastActivityDate=LastActivityDate
Region__c=Region__c
Annual_Procurment__c=Annual_Procurment__c
Trial_Balance_Code__c=Trial_Balance_Code__c
Customer_Group__c=Customer_Group__c
SAP_VEGA_1_Account_Number__c=SAP_VEGA_1_Account_Number__c
SAP_VEGA_2_Account_Number__c=SAP_VEGA_2_Account_Number__c
QAD_Account_Number__c=QAD_Account_Number__c
BAAN_Account_Number__c=BAAN_Account_Number__c
Brief_Name__c=Brief_Name__c
Customer_PBU__c=Customer_PBU__c

 
Right now the only problem is the response time, looks like very slow, there are 214 records takes me 4minutes to run, not quite sure why, please advice...Thanks

All Answers

QingsongQingsong
Good news, I was figure out what's the root cause, for extract data into SQL, It has to be a INSERT statement not a SELECT statement, so I did the change in database-conf.xml as below:
Code:
<!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=CRMReport;"/>
    <property name="username" value="test"/>
    <property name="password" value="test"/>
</bean>


<bean id="tmpAccount"
      class="com.salesforce.lexiloader.dao.database.DatabaseConfig"
      singleton="true">
    <property name="sqlConfig" ref="queryAccount"/>
    <property name="dataSource" ref="dbDataSource"/>
</bean>
<bean id="queryAccount"
      class="com.salesforce.lexiloader.dao.database.SqlConfig" singleton="true">
    <property name="sqlString">
        <value>
 INSERT INTO dbo.tmpAccount (Id, IsDeleted, MasterRecordId, 
  Name, Type, RecordTypeId, ParentId, 
  BillingStreet, BillingCity, BillingState, 
  BillingPostalCode, BillingCountry, ShippingStreet, 
  ShippingCity, ShippingState, ShippingPostalCode, 
  ShippingCountry, Phone, Fax, 
  Website, Industry, AnnualRevenue, 
  NumberOfEmployees, 
  Description, CurrencyIsoCode, 
  OwnerId, CreatedDate, CreatedById, 
  LastModifiedDate, LastModifiedById, 
  SystemModstamp, LastActivityDate, 
  Region__c, Annual_Procurment__c, 
  Trial_Balance_Code__c, Customer_Group__c, 
  SAP_VEGA_1_Account_Number__c, 
  SAP_VEGA_2_Account_Number__c, 
  QAD_Account_Number__c, 
  BAAN_Account_Number__c, Brief_Name__c,Customer_PBU__c)
 VALUES(@Id@,@IsDeleted@,@MasterRecordId@,@Name@,@Type@,@RecordTypeId@,@ParentId@,
  @BillingStreet@,@BillingCity@,@BillingState@,@BillingPostalCode@,
  @BillingCountry@,@ShippingStreet@,@ShippingCity@,@ShippingState@,
  @ShippingPostalCode@,@ShippingCountry@,@Phone@,@Fax@,@Website@,
  @Industry@,@AnnualRevenue@,@NumberOfEmployees@,@Description@,
  @CurrencyIsoCode@,@OwnerId@,@CreatedDate@,@CreatedById@,
  @LastModifiedDate@,@LastModifiedById@,@SystemModstamp@,
  @LastActivityDate@,@Region__c@,@Annual_Procurment__c@,
  @Trial_Balance_Code__c@,@Customer_Group__c@,@SAP_VEGA_1_Account_Number__c@,
  @SAP_VEGA_2_Account_Number__c@,@QAD_Account_Number__c@,
  @BAAN_Account_Number__c@,@Brief_Name__c@,@Customer_PBU__c@)
        </value>
    </property>
    <property name="sqlParams">
        <map>
        <entry key="Id"    value="java.lang.String"/>
 <entry key="IsDeleted"    value="java.lang.String"/>
 <entry key="MasterRecordId"    value="java.lang.String"/>
 <entry key="Name"    value="java.lang.String"/>
 <entry key="Type"    value="java.lang.String"/>
 <entry key="RecordTypeId"    value="java.lang.String"/>
 <entry key="ParentId"    value="java.lang.String"/>
 <entry key="BillingStreet"    value="java.lang.String"/>
 <entry key="BillingCity"    value="java.lang.String"/>
 <entry key="BillingState"    value="java.lang.String"/>
 <entry key="BillingPostalCode"    value="java.lang.String"/>
 <entry key="BillingCountry"    value="java.lang.String"/>
 <entry key="ShippingStreet"    value="java.lang.String"/>
 <entry key="ShippingCity"    value="java.lang.String"/>
 <entry key="ShippingState"    value="java.lang.String"/>
 <entry key="ShippingPostalCode"    value="java.lang.String"/>
 <entry key="ShippingCountry"    value="java.lang.String"/>
 <entry key="Phone"    value="java.lang.String"/>
 <entry key="Fax"    value="java.lang.String"/>
 <entry key="Website"    value="java.lang.String"/>
 <entry key="Industry"    value="java.lang.String"/>
 <entry key="AnnualRevenue"    value="java.lang.String"/>
 <entry key="NumberOfEmployees"    value="java.lang.String"/>
 <entry key="Description"    value="java.lang.String"/>
 <entry key="CurrencyIsoCode"    value="java.lang.String"/>
 <entry key="OwnerId"    value="java.lang.String"/>
 <entry key="CreatedDate"    value="java.lang.String"/>
 <entry key="CreatedById"    value="java.lang.String"/>
 <entry key="LastModifiedDate"    value="java.lang.String"/>
 <entry key="LastModifiedById"    value="java.lang.String"/>
 <entry key="SystemModstamp"    value="java.lang.String"/>
 <entry key="LastActivityDate"    value="java.lang.String"/>
 <entry key="Region__c"    value="java.lang.String"/>
 <entry key="Annual_Procurment__c"    value="java.lang.String"/>
 <entry key="Trial_Balance_Code__c"    value="java.lang.String"/>
 <entry key="Customer_Group__c"    value="java.lang.String"/>
 <entry key="SAP_VEGA_1_Account_Number__c"    value="java.lang.String"/>
 <entry key="SAP_VEGA_2_Account_Number__c"    value="java.lang.String"/>
 <entry key="QAD_Account_Number__c"    value="java.lang.String"/>
 <entry key="BAAN_Account_Number__c"    value="java.lang.String"/>
 <entry key="Brief_Name__c"    value="java.lang.String"/>
 <entry key="Customer_PBU__c"    value="java.lang.String"/>
        </map>
    </property>
</bean>
</beans>


 
then the mapfile should be same with database-conf and process-conf, it's CASE SENSITIVE, below is my ExtractAccountMap.sdl
Code:
#Mapping values
#Mon Jan 23 17:07:24 PST 2006
Id=Id
IsDeleted=IsDeleted
MasterRecordId=MasterRecordId
Name=Name
Type=Type
RecordTypeId=RecordTypeId
ParentId=ParentId
BillingStreet=BillingStreet
BillingCity=BillingCity
BillingState=BillingState
BillingPostalCode=BillingPostalCode
BillingCountry=BillingCountry
ShippingStreet=ShippingStreet
ShippingCity=ShippingCity
ShippingState=ShippingState
ShippingPostalCode=ShippingPostalCode
ShippingCountry=ShippingCountry
Phone=Phone
Fax=Fax
Website=Website
Industry=Industry
AnnualRevenue=AnnualRevenue
NumberOfEmployees=NumberOfEmployees
Description=Description
CurrencyIsoCode=CurrencyIsoCode
OwnerId=OwnerId
CreatedDate=CreatedDate
CreatedById=CreatedById
LastModifiedDate=LastModifiedDate
LastModifiedById=LastModifiedById
SystemModstamp=SystemModstamp
LastActivityDate=LastActivityDate
Region__c=Region__c
Annual_Procurment__c=Annual_Procurment__c
Trial_Balance_Code__c=Trial_Balance_Code__c
Customer_Group__c=Customer_Group__c
SAP_VEGA_1_Account_Number__c=SAP_VEGA_1_Account_Number__c
SAP_VEGA_2_Account_Number__c=SAP_VEGA_2_Account_Number__c
QAD_Account_Number__c=QAD_Account_Number__c
BAAN_Account_Number__c=BAAN_Account_Number__c
Brief_Name__c=Brief_Name__c
Customer_PBU__c=Customer_PBU__c

 
Right now the only problem is the response time, looks like very slow, there are 214 records takes me 4minutes to run, not quite sure why, please advice...Thanks
This was selected as the best answer
melchisholm22melchisholm22

Is it possible to extract data from Salesforce and insert it to MS SQL Server with different fieldnames?

 

For ex. Account object ---> Any SQL table

 

SF         =====     SQL

Name                     AccountName

Phone                 PhoneNumber

 

I created a mapping file that contains this:

##SF - SQL fields 

Name=AccountName

Phone=PhoneNumber 

 

When I run the batch command process, it inserts null values. If I change the AccountName to Name and PhoneNumber to Phone then it works.

 

I hope someone can point me to the right direction. :smileyhappy:

  

rajarakrajarak

melchisholm22,

 

I guess it needs to be changed accordingly in the database-conf.xml file as well.

Did you cross check the same?

 

 


melchisholm22 wrote:

Is it possible to extract data from Salesforce and insert it to MS SQL Server with different fieldnames?

 

For ex. Account object ---> Any SQL table

 

SF         =====     SQL

Name                     AccountName

Phone                 PhoneNumber

 

I created a mapping file that contains this:

##SF - SQL fields 

Name=AccountName

Phone=PhoneNumber 

 

When I run the batch command process, it inserts null values. If I change the AccountName to Name and PhoneNumber to Phone then it works.

 

I hope someone can point me to the right direction. :smileyhappy:

 


 

PallavDPallavD

Hi,

 

Could you please let me know how to do this thing other way around? I mean to say, how will I pull data from SQL and insert or update in the Salesforce objects.

 

Thanks in anticipation of your help in this regard.

 

Thanks and regards,

 

Pallav

Adrian_BAdrian_B

This is maddening, 

I've set up all the appropriate config files, yet, I can't get an output file to show my why rows are erroring out.

Here is an excerpt of my prcess-conf.xml file:

 

 

 

<bean id="databaseQuoteExtractProcess"          class="com.salesforce.dataloader.process.ProcessRunner"          
singleton="false">      
	<description>databaseQuoteExtract job gets quote info from salesforce and saves info into a database table."</description>        
	<property name="name" value="databaseQuoteExtract"/>        
	<property name="configOverrideMap">            
		<map> 
			<!-- sfdc parameters --> 
			<entry key="sfdc.connectionTimeoutSecs" value="60"/> 
			<entry key="sfdc.bulkApiCheckStatusInterval" value="5000"/>                			<entry key="sfdc.debugMessages" value="true"/> 
			<entry key="sfdc.debugMessagesFile" value="c:\sf_migration\Quote\Status\Logfiles\sfdcSoapTrace.log"/> 
			<entry key="sfdc.loadBatchSize" value="200"/> 
			<entry key="sfdc.maxRetries" value="3"/> 
			<entry key="sfdc.resetUrlOnLogin" value="true"/> 
			<entry key="sfdc.extractionRequestSize" value="500"/> 
			<entry key="sfdc.truncateFields" value="true"/> 
			<entry key="sfdc.minRetrySleepSecs" value="2"/> 
			<entry key="sfdc.enableRetries" value="true"/> 
			<entry key="sfdc.timeoutSecs" value="600"/> 
			<entry key="sfdc.insertNulls" value="true"/>                				<entry key="sfdc.endpoint" value="https://test.salesforce.com"/>                
			<entry key="sfdc.username" value="[Username]"/>                			                
			<entry key="sfdc.password" value="[Encrypted Password]"/> 
			<entry key="sfdc.entity" value="Quote"/>                
			<entry key="sfdc.extractionSOQL" value="Select Id, IsDeleted, Name, Subtotal FROM Quote WHERE Status = 'Closed' "/> 
			<!-- process parameters -->                
			<entry key="process.operation" value="extract"/>   
			<entry key="process.mappingFile" value="c:\sf_migration\Quote\Apex\quoteExtractMap.sdl"/> 
			<!--<entry key="process.outputSuccess" value="C:\sf_migration\Quote\Status\Success\"/>--> 
			<entry key="process.outputError" value="C:\Error.csv"/> 
			<!--<entry key="process.statusOutputDirectory" value="C:\Status\"/>--> 
			<!-- dataAccess parameters --> 
			<entry key="dataAccess.name" value="insertQuote"/> 
			<entry key="dataAccess.type" value="databaseWrite"/> <entry key="dataAccess.readBatchSize" value="200"/>                            
		</map>        
	</property>    
</bean>

This query returns records and attempts to insert them into SQL Server 2008. 

I'm receiving errors, but for the life of me I cannot generate an Error file. (There is no c:\Errors.csv). 

This makes it next to impossible to debug. 

 

If any of you have insight into debugging this process, please advise. 

 

I have tested the process using csv with an extract being generated,but still no Error or Success.csv file. 

 

 

rajarakrajarak

Adrian,

 

I remember running into the same issue.

I initially didn't get the extract.csv file itself.

I had permission issue with the destination drive and I had created the extract.csv file manually  and then it got the output.

 

Try creating the error.csv file (if you don't have it already) and see if the content gets generated in it.

 

Hope it helps

Adrian_BAdrian_B

Thanks for advice rajarak,

 

 

I gave that a try, however, nothing was written to Error.csv. Interetingly enough the sfdcSoapTrace.log file does get generated, but it does not appear to yield any error information what-so-ever. Surely someone else has run into this issue. 

Could this have something to do with the databaseWrite operation? 

 

Thanks for the help,

 

Adrian