You need to sign in to do that
Don't have an account?
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:
<!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:
<!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:
@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:
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.
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
then the mapfile should be same with database-conf and process-conf, it's CASE SENSITIVE, below is my ExtractAccountMap.sdl
All Answers
then the mapfile should be same with database-conf and process-conf, it's CASE SENSITIVE, below is my ExtractAccountMap.sdl
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:
melchisholm22,
I guess it needs to be changed accordingly in the database-conf.xml file as well.
Did you cross check the same?
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
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:
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.
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
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