Salesforce.com is a CRM application that is widely used by many corporations to manage their data and run their businesses. Connecting to Salesforce through Datastage can be done in different ways. The one im going to explain here is how to use the webservice stage and connect to the Salesforce API.
One major useful site that I found that gives a list of all the salesforce operations with the soap message structures was the developer force wiki site.
Lets take an example of the ‘upsert’ operation in the Salesforce API. The first major task you have to do is to log into the web service by using the username and password provided. Once you call the login operation though the web service call you will get a session id and server url as a result. This will be used to connect to the API for all future activities till a logout call is issued.
You can use the xml output stage to prepare your xml message. Now one thing you have to remember is that the Salesforce Api does have some record restrictions. For example it won’t allow you to upsert more than 200 records in a call. Another example is that you wont be able to retrieve more than 2000 records using the query call. You will have to use the queryMore call to retrieve records if the result is more than 2000.
Coming back to the upsert call, you will first have to prepare the xml message(s). Now if there are more than 200 records that you need to upsert you will have to introduce some sort of batch variable to group your data into batches.
E.g.
Column A Column B Column C Batch
AA BB CC 1
AB BC CD 1
AC BD CE 2
AD BE CF 2
Before you prepare your xml you will have to set the column values for each different tag you will be populating in your xml message..Then pass this data into your xml output stage. A sample design snapshot is shown below.
Datastage XML Job Design
In the xml output stage, you will have to enter the XPATH derivations for each column
E.g. For a column called sessionid you may be giving the xpath as /SOAP-ENV:Envelope/SOAP-ENV:Header/m:SessionHeader/m:sessionId/text()
You will have an idea of the xpaths from the developer force link provided above. This site will have SOAP message samples of all the calls from both the Enterprise and Partner web service APIs. You will also need to import the namespaces onto the xml output stage without which your xml cant be created. An easy way to do this is to get the xml sample from the site mentioned earlier and import this xml definition onto the Datastage server. You can then easily load the namespace using the ‘load’ option in Namespace declaration tab. Another important thing to note is that you will have to set the ‘Use trigger column’ option and select the batch column that you added in the transformer. This should also be present in the output column definition along with the xml output column. This will ensure that you are creating batches of XML messages each with the required number of records contained in it. You then simply have to pass the user defined soap message to the web service transformer.
In the web service transformer’s input tab make sure that you check the ‘userdefined message’ box in the Transformation Settings tab and select your XML column. We are doing this since we are creating the XML messages ourselves as batches. If that wasn’t the case then you could have simply sent the columns to the web service and the web service stage would create and send the xml.
You can capture and parse your web service result using the xml input stage.