This is in continuation from
my previous post where I had explained how
to fetch data from SalesForce using the SOAP API. Now we are going to see
how to fetch data using Bulk API.
Prerequisites:
User Name
Password
Security Token (needed for the Api calls as the password is considered as password+security token)
Client Secret (Required for REST Call)
Client Id (Required for REST Call)
Call Back (This is required if we are authenticating from the web, else if we have a console application this could be some dummy value)
Steps:
Prerequisites:
User Name
Password
Security Token (needed for the Api calls as the password is considered as password+security token)
Client Secret (Required for REST Call)
Client Id (Required for REST Call)
Call Back (This is required if we are authenticating from the web, else if we have a console application this could be some dummy value)
Steps:
1.
Create
a console application
2.
Create a text file called “Request.txt” with the following
contents
select Id,CaseNumber from
Case
3. In the main class (Program.vb) add the below
methods
At a high level below is the process
·
You will need to create
a Job in SalesForce. This will create an entry in the SalesForce Bulk Data load
jobs. Once the job is created you get a Job
Id.
You can see the entry in SalesForce
·
Add the batch – this gets the file with the
select query and adds it as part of the input stream. Pass the job id obtained
after adding the job
·
You need to post and get
the Batch Id
·
Finally when the batch gets
executed, you can retrieve the Results
id.
·
Using the Job id, batch
id and results id retrieve the results and output it to a file.
Note: I have not included the logic needed to wait / check if the
batch has been run or not.
4. Run the console application
Sample Code:
Add the below methods to a console application
Add the below methods to a console application
Private Sub QueryUsingBulkAPI()
Dim jobId As String = String.Empty
Dim resultId As String = String.Empty
Dim batchId As String = String.Empty
'Used to
create the Job in SalesForce--
'the Job
can be query, insert, delete etc...This is the operation parameter
'eg for
querying Case
CreateJob(loginRes.sessionId, "query", "Case", jobId)
'e.g for
inserting contact
'Dim
jobId As String = CreateJob(loginRes.sessionId, "insert",
"Contact")
Dim inputFileData() As Byte
If (jobId.Length > 0) Then
'data
loading using a sampe file
' Open a
file that is to be loaded into a byte array
Dim oFile As System.IO.FileInfo
'oFile =
New System.IO.FileInfo("data.csv")
oFile = New System.IO.FileInfo("request.txt")
Dim oFileStream As System.IO.FileStream = oFile.OpenRead()
Dim lBytes As Long = oFileStream.Length
If (lBytes > 0) Then
Dim fileData(lBytes - 1) As Byte
'
Read the file into a byte array
oFileStream.Read(fileData, 0,
lBytes)
oFileStream.Close()
'Get the
file where the Query is present
inputFileData = fileData
End If
'Adds
the batch to SalesForce
AddBatch(loginRes.sessionId,
inputFileData, jobId, batchId, resultId)
'Check
Status and Get BatchId and ResultsId
GetStatusAndRetrieveBatchIdAndResultsId(loginRes.sessionId, jobId,
batchId, resultId)
'Get
Results Id
RetrieveResults(loginRes.sessionId,
jobId, batchId, resultId)
End If
End Sub
''' <summary>
'''
Creates a job in Salesforce
''' </summary>
''' <param name="sfSessionId"></param>
''' <param name="sfOperation"></param>
''' <param name="sfObjectName"></param>
''' <param name="jobId"></param>
''' <remarks></remarks>
Public Sub CreateJob(ByVal sfSessionId As String,
ByVal sfOperation As String,
ByVal sfObjectName As String,
ByRef jobId As String)
Dim str As String = ""
Dim reqURL As String = ""
Dim bytes() As Byte
Dim reqDoc As XmlDocument
Dim responseXmlDocument As XmlDocument = New XmlDocument
'''Make
changes to ContentType field if you want to
'''post
it as CSV or XML
str = "" + "<?xml
version=""1.0""
encoding=""UTF-8""?>"
&
"<jobInfo
xmlns=""http://www.force.com/2009/06/asyncapi/dataload"">" + " <operation></operation>" &
" <object></object>" &
" <contentType>CSV</contentType>" &
"</jobInfo>"
'Eg for
XML content type
'"
<contentType>XML</contentType>" &
reqURL = "https://na1.salesforce.com/services/async/23.0/job"
reqDoc = New XmlDocument
reqDoc.LoadXml(str)
' added
XML modifications
reqDoc.GetElementsByTagName("operation")(0).InnerText
= sfOperation
reqDoc.GetElementsByTagName("object")(0).InnerText
= sfObjectName
bytes =
System.Text.Encoding.ASCII.GetBytes(reqDoc.InnerXml)
'bytes =
System.Text.Encoding.UTF8.GetBytes(reqDoc.InnerXml)
Using responseStream As Stream = Post(bytes, reqURL, sfSessionId, "POST", "text/csv; charset=UTF-8")
responseXmlDocument.Load(responseStream)
'Get
jobId
jobId = IIf((Not (responseXmlDocument) Is Nothing),
responseXmlDocument.GetElementsByTagName("id").Item(0).InnerText, "")
End Using
End Sub
''' <summary>
''' Adds
the Batch to SalesForce
''' </summary>
''' <param name="sfSessionId"></param>
''' <param name="fileBytes"></param>
''' <param name="sfJobId"></param>
''' <param name="sfBatchId"></param>
''' <param name="sfResultId"></param>
''' <remarks></remarks>
Public Sub AddBatch(ByVal sfSessionId As String,
ByVal fileBytes() As Byte,
ByVal sfJobId As String,
Optional ByVal sfBatchId As String = Nothing,
Optional ByVal sfResultId As String = Nothing)
Dim requestURI As String = ("https://login.salesforce.com/services/async/23.0/job/" _
+ (sfJobId + "/batch"))
Post(fileBytes, requestURI,
sfSessionId, "POST", "text/csv;
charset=UTF-8")
End Sub
''' <summary>
''' Once
the batch is added get the BatchId and then
''' once
the processing is done , get the Results ID
''' </summary>
''' <param name="sfSessionId"></param>
''' <param name="sfJobId"></param>
''' <param name="batchId"></param>
''' <param name="resultId"></param>
''' <remarks></remarks>
Public Sub GetStatusAndRetrieveBatchIdAndResultsId(ByVal sfSessionId As String,
ByVal sfJobId
As String,
ByRef batchId
As String,
ByRef
resultId As String)
Dim responseXmlDocument As XmlDocument = New XmlDocument
Dim reqURL As String = ("https://na1.salesforce.com/services/async/23.0/job/" _
+ sfJobId + "/batch")
'Get
BatchId
Using responseStream As IO.Stream = Post(Nothing, reqURL, sfSessionId, "GET", "text/csv;
charset=UTF-8")
responseXmlDocument.Load(responseStream)
batchId = IIf((Not (responseStream) Is Nothing),
responseXmlDocument.GetElementsByTagName("id").Item(0).InnerText, "")
End Using
'Get
ResultId
reqURL = ("https://na1.salesforce.com/services/async/23.0/job/" _
+ (sfJobId + "/batch/" +
batchId + "/result"))
Using responseStream As IO.Stream = Post(Nothing, reqURL, sfSessionId, "GET", "text/csv;
charset=UTF-8")
responseXmlDocument.Load(responseStream)
resultId = IIf((Not (responseStream) Is Nothing),
responseXmlDocument.GetElementsByTagName("result").Item(0).InnerText, "")
End Using
End Sub
''' <summary>
''' Post
the rest call with batch id and results id to get the output
''' </summary>
''' <param name="sfSessionId"></param>
''' <param name="sfJobId"></param>
''' <param name="sfBatchId"></param>
''' <param name="sfResultId"></param>
''' <remarks></remarks>
Public Sub RetrieveResults(ByVal sfSessionId As String,
ByVal sfJobId As String,
ByVal sfBatchId As String,
ByVal sfResultId As String)
Dim reqURL As String = ("https://na1.salesforce.com/services/async/23.0/job/" _
+ (sfJobId + "/batch/" +
sfBatchId + "/result/" + sfResultId))
Dim localFile As String = "output.csv"
'Create
the output file
Using responseStream As IO.Stream = Post(Nothing, reqURL, sfSessionId, "GET", "text/csv;
charset=UTF-8")
Using fsOutputFile As New IO.FileStream(localFile, FileMode.Create,
FileAccess.Write)
Dim buffer(2047) As Byte
Dim read As Integer
Do
read =
responseStream.Read(buffer, 0, buffer.Length)
fsOutputFile.Write(buffer,
0, read)
Loop Until read = 0
responseStream.Close()
fsOutputFile.Flush()
fsOutputFile.Close()
End Using
responseStream.Close()
End Using
End Sub
''' <summary>
'''
Function to POST the HTTP rest request
''' </summary>
''' <param name="bytes"></param>
''' <param name="reqURL"></param>
''' <param name="sfSessionId"></param>
''' <param name="method"></param>
''' <param name="contentType"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Function Post(ByVal bytes() As Byte, ByVal reqURL As String, ByVal sfSessionId As String, ByVal method As String, ByVal contentType As String) As Stream
'Create
the request object
Dim requestHttp As WebRequest = WebRequest.Create(reqURL)
'Assign
the type of request POST,GET..
requestHttp.Method = method
'Assign
Content Type
requestHttp.ContentType = contentType '"text/csv; charset=UTF-8" or
"application/xml; charset=UTF-8"
'Assign
the session id to the header
requestHttp.Headers.Add(("X-SFDC-Session: " +
sfSessionId))
'Assign
byte length
If (bytes IsNot Nothing) Then
requestHttp.ContentLength =
bytes.Length
Dim strmHttpContent As System.IO.Stream = requestHttp.GetRequestStream
strmHttpContent.Write(bytes, 0,
bytes.Length)
strmHttpContent.Close()
End If
'Get the
response object
Dim responseHttpRequest As WebResponse = requestHttp.GetResponse
'Return
response Stream
Return responseHttpRequest.GetResponseStream
End Function