Saturday 28 September 2013

Bulk Upsert operation of SalesForce from SSIS package using Data Loader

Introduction:

In this post I will explain how to push data inside Salesforce through SSIS package using Bulk Load API. I will demonstrate it by using two example how to pull the data from Sql Server or any other database and how to pull data from CSV file.
 

Technology used:

SSIS, SaleForce Data Loader Tool.
 

Pre-requisites:

Sales Force Data Loader tool and the SSIS must be installed in your system.
 

Over View:

Data Loader has a command line utility called process.bat. This utility can load data inside SalesForce from different source systems like CSV file,Oracle, SQL Server etc..
 
To import data from different source system the JDBC driver is required for that to establish the connection.
 
Two xml files are required named:
  1. process-cnf.xml
  2. database-cnf.xml
The database-cnf file will have the source related information and the process-cnf will contain the salesforce related information.
 
The SSIS package will call the process.bat file which in turn use process-cnf and database-cnf and load data inside SalesForce.
 

Process

 
Step 1
Install Apex Loader. Go to the following path:
C:\Program Files (x86)\salesforce.com\Data Loader\bin
You will find the following bat files:
1.      encrypt.bat
2.      process.bat
According to your source system get the JDBC drive. For example for SQL Server have the following driver:com.microsoft.sqlserver.jdbc.SQLServerDriver
And modify the process.bat in the following way:
@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 ../myconfigdirAccountInsert
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
 
..\Java\bin\java.exe -cp ..\dataloader-27.0.1-uber.jar;"C:\Work Area\JDBC Driver\Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_4.0\enu\sqljdbc4.jar" -Dsalesforce.config.dir=%1 com.salesforce.dataloader.process.ProcessRunner %PROCESS_OPTION%
 
:end
 
Add the highlighted portion which is the driver path.
Step 2
Create a xml file named database-conf.xml. Please put the following XML code inside and provide the following information regarding your source:
1.      Connection URI
2.      User ID
3.      Password
4.      Source Query
5.      Source columns
<!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=<Connection URI>/>
<property name="username" value="UID"/>
<property name="password" value="PASSWORD"/>
</bean>
<bean id="queryAccount"
class="com.salesforce.dataloader.dao.database.DatabaseConfig"
singleton="true">
<property name="sqlConfig" ref="queryAccountSql"/>
<property name="dataSource" ref="dbDataSource"/>
</bean>
<bean id="queryAccountSql"
class="com.salesforce.dataloader.dao.database.SqlConfig" singleton="true">
<property name="sqlString">
<value>
       YOUR Query     </value>
</property>
<property name="columnNames">
<list>
<value>Column0</value>
            <value>column1</value>
</list>
</property>
</bean>
</beans>
Step3:
Create an encrypted SalesForce password file by using the following command:
encrypt.bat –e <password> "<filepath>\key.txt"
Step4:
Create a mapping file between source and target columns. The file should of SDL extension.
Please find the sample mapping file
#Mapping values
#Mon Jan 23 17:07:24 PST 2006
SLA__c=SLA__C
BillingCity=BILLINGCITY
=SYSTEMMODSTAMP
OwnerId=OWNERID
CustomerPriority__c=CUSTOMERPRIORITY__C
AnnualRevenue=ANNUALREVENUE
Description=DESCRIPTION
BillingStreet=BILLINGSTREET
ShippingState=SHIPPINGSTATE
=CREATEDBYID
=CREATEDDATE
Website=WEBSITE
BillingState=BILLINGSTATE
=LASTMODIFIEDBYID
=LASTMODIFIEDDATE
Phone=PHONE
NumberOfEmployees=NUMBEROFEMPLOYEES
Type=TYPE
ShippingCity=SHIPPINGCITY
Rating=RATING
Industry=INDUSTRY
ShippingStreet=SHIPPINGSTREET
UpsellOpportunity__c=UPSELLOPPORTUNITY__C
AccountNumber=ACCOUNTNUMBER
TickerSymbol=TICKERSYMBOL
Id=ID
BillingPostalCode=BILLINGPOSTALCODE
Site=SITE
ParentId=PARENTID
Name=NAME
BillingCountry=BILLINGCOUNTRY
SLAExpirationDate__c=SLAEXPIRATIONDATE__C
ShippingCountry=SHIPPINGCOUNTRY
Fax=FAX
Sic=SIC
NumberofLocations__c=NUMBEROFLOCATIONS__C
ShippingPostalCode=SHIPPINGPOSTALCODE
Active__c=ACTIVE__C
Ownership=OWNERSHIP
SLASerialNumber__c=SLASERIALNUMBER__C:
Step5:
Create another xml file named process-conf.xml. This file will contain all the sales force related information. Following is the script inside the process-conf file:
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
            <bean id="PanelBookingUpsertfromDB"
class="com.salesforce.dataloader.process.ProcessRunner"
singleton="false">
<description>Sample 'upsert'.</description>
<property name="name" value="PanelBookingUpsertfromDB"/>
<property name="configOverrideMap">
<map>
<entry key="sfdc.debugMessages" value="true"/>
<entry key="sfdc.debugMessagesFile" value="LOGPATH"/>
<entry key="sfdc.endpoint" value="SalesForce site address."/>
<entry key="sfdc.username" value="SalesForce ID"/>
<!-- 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="Place your encripted value."/>
<entry key="process.encryptionKeyFile" value="Encripted key path"/>
<entry key="sfdc.timeoutSecs" value="600"/>
<entry key="sfdc.loadBatchSize" value="10000"/>                                       
                                                <entry key="dataAccess.writeBatchSize" value="500" />
                                                <entry key="sfdc.bulkApiCheckStatusInterval" value="5000"/>
                                                <entry key="sfdc.useBulkApi" value="true"/>
<entry key="sfdc.externalIdField" value="Salesforce external field."/>
<entry key="sfdc.entity" value="SalesForce entity name."/>
<entry key="process.operation" value="upsert"/>
<entry key="process.mappingFile" value="mapping file path"/>
<entry key="dataAccess.name" value="queryAccount"/>
<entry key="dataAccess.type" value="databaseRead"/>
                                                <entry key="process.outputSuccess" value="Success CSV path"/>
                                                <entry key="process.outputError" value="Error CSV path."/>
<entry key="process.initialLastRunDate" value="2005-12-01T00:00:00.000-0800"/>
</map>
</property>
</bean>
</beans>
Step6:
Put the following command inside a batch file and execute the batch file from the SSIS package by using Execute process task. Which in turn load the data into SalesForce.

2 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Until now I have always felt that SSIS operations are a bit complicated and especially SSIS Upsert but I want to understand that what role do triggers play in the Upsert operations.

    ReplyDelete