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.