Salesforce.com Web Service Call

[Dan]

This Jitterpak provides Salesforce.com users the basic setup for accessing Salesforce.com through the site’s Web Service API. Users can use the pre-defined WSDL call to retrieve and make changes to their account information.

Included this post are two files:

:arrow: The Jitterpak (Jitterpak for Salesforce.jpk)
:arrow: The Tutorial for this Jitterpak (Jitterpak for Salesforce.pdf)

NOTE: A change in Salesforce.com's security requires you to change the login/password included in this Jitterpak to your own. You will need to include your security token on the password.

You need to be logged in to view and download the files.

[Ilan]

I’ve updated the Jitterpak for Salesforce to include a group of operations that deletes New Accounts created by after the Create Account operations are run. My salesforce.com test account filled up with too many ‘New Account’ entries.

The new group (folders) of integration Operations is called Delete Accounts. They delete any Salesforce.com account that starts with the name ‘New Account’. It comprises the following:
1. Login to Delete Accounts for retrieving and storing your SessionID and ServerURL in global variables to be used by subsequent web service calls.
2. Build Query for New Accounts that describes the query to retrieve accounts that start with ‘New Account’
3. Query New Accounts that passes the query built in the previous operation to salesforce.com and returns account information that is stored in a file.
4. Delete New Accounts that uses the Account Ids from the file to delete the appropriate accounts from Salesforce.com.

Prior to running these operations, you will need to setup a file share on your network or your PC in order for information to be saved from salesforce.com

Once you have created this file share, you will need to modify the following sources and targets provided in this Jitterpak to reflect the correct fileshare as well as login credentials:

1. Sources:
a. New Accounts

2. Targets:
a. Login Info
b. Query Info
c. Account Info
d. Create Status
e. New Account Info
f. New Account Delete Status

Once you have done that, you will also need to make sure that the Jitterpak calls your specific salesforce.com by providing your login credentials, or use my test account provided in the Jitterpak login.

To point to your salesforce.com open the transformation called ‘Login Request’, double-click on the username data element and provide your username then double-click on the password and provide your password.

To delete accounts you created open the operation called ‘Login to Delete Accounts’. That will deploy all relevant operations to your server. Once that is done, the operation will instantiate all required operations to delete the unwanted test accounts.

If you add any functionality to your Jitterpak for Salesforce that would be useful to others please post them on this thread.

[The_fox]

Hello,

I tried to query Account on my salesforce but it does not work. I think there is something to change in the transformation.

I would like to jnow also how to make transformation for other salesforce object. I think you need the entreprise wsdl.

I am uite a newbie on integration so sorry if the questiosn appear a bit stupid.

Regards

[Ilan]

To query Account information from your salesforce.com you will need to change the transformation called Login Request. When you open that transformation and expand the target tree (the tree on the right) you should see two elements: username and password.

Double-click on username and change it to your username when you login to salesforce. Once you have done that, double-click on password and change it to your password.

Once you make the changes, click that save button in the transformation toolbar.

To run the operation that copies your accounts from salesforce to a file, open the operation called Login to Query Account. Click the button that places the operation to the queue for execution (it looks like a bolt of lightning). The system should now tell you that its going to deploy the changes you just made to the transformation. It will then run the operation.

If you have any issues running it please provide us the details.

The Jitterpak already has the standard enterprise WSDL. If you have a customized WSDL please tell us so that we can guide you on loading that.

To call other salesforce functions right-click on Web Service Calls and choose New Web Service Call from the popup menu. choose the sfdc_enterprise.wsdl to pick your method from and then click next. A screen with a drop down should then let you select the sfdc method you wish to call. Once you select the method e.g. query, click next. The feature that is different in salesforce is that you must set the Web Service URL to [ServerURL] which is a global variable set after you login.

If you tell us what you'd like to do then we would be glad to create a Jitterpak for your scenario. Also, if you wish to use your enterprise.wsdl vs the standard one, please post that to this forum and we'll load it into the Jitterpak we create for you.

Thanks.

[The_fox]

Thanks Ian

The first part was ok. when I Query Account I have the following error message Fatal Error:
Transformation failed

Error:
Failed to perform transformation.
The webservice response does not match the structure defined by the schema file. Reported error: The structure of the returned XML does not match the structure defined by the schema. No matching node was found in the schema structure tree for the XML node 'CurrencyIsoCode' in the namespace 'urn:sobject.enterprise.soap.sforce.com'. There are 41 nodes on the corresponding level in the schema structure: fieldsToNull, Id, AccountNumber, Active__c, AnnualRevenue, BillingCity, BillingCountry, BillingPostalCode, BillingState, BillingStreet, CreatedById, CreatedDate, CustomerPriority__c, Description, Fax, Industry, LastModifiedById, LastModifiedDate, Name, NumberOfEmployees, NumberofLocations__c, OwnerId, Ownership, ParentId, Phone, Rating, SLAExpirationDate__c, SLASerialNumber__c, SLA__c, ShippingCity, ShippingCountry, ShippingPostalCode, ShippingState, ShippingStreet, Sic, Site, SystemModstamp, TickerSymbol, Type, UpsellOpportunity__c, Website. [CODE:10712]
And by the way looking at your entrepise wdsl , it is not 100% standard has you have some custom fields too

[The_fox]

Ok

I am making some progress here. I have been able to query my on account.

I ran into another issue here.

I have create a simple table on Postgresql named Accound with id, Name, Created Date, Last Updated date but I have this error message. The database is setted in Unicode. Issue with some Unicode into Postgres SQL?

I have a request: to be able to monitor changes in salesforce with the GetUpdated and Getdeleted Method. If you can give some hints with an Account table for a start

Regards and sorry for bothering you ith such newbie question

Another Question. It does not seem to take into account the batch size to query the account

Fatal Error:
Transformation failed

Error:
Failed to perform transformation.
Insert SQL statement failed to execute (Insert mode). SqlState = S1000 NativeError = 7
ERROR: Unicode characters greater than or equal to 0x10000 are not supported
SQL - Param Data INSERT INTO "public"."Acount" ("Id","Name") VALUES (?,?)
Param #0 = '00120000002pCjrAAE', Len = 18
Param #1 = 'Handelsbanken Kapitalf?altning', Len = 32 [CODE:10095]

[Ilan]

I've attached two files.

1. SFDC to AccountDB.sql will create postgres tables for you. These tables will be used to store your sfdc data. I'm posting this as the Jitterpak I've attached uses these tables, but once you have it working then create transformations that point to your database.

I have changed the login routine to use information in the Login table vs entering them manualy into the transformation. Please edit the .sql file and change the insert statement to include your username and password.

Once you have made that change, run the SQL script in the PGAdmin Query tool

2. With the database in place, import the SFDC to AccountDB.jpk. You'll need to make modifications to the Source and Target so that it has the appropriate settings and credentials for your database.

Once that is done, I have created 3 operations that will do the following:
a. Login to SFDC - get your sessionID and ServerURL. It will start
b. getUpdated - gets all Accounts updated since May 10th and inserts their ID into the Account Table. When it succeeds it will start
c. getDeleted - gets all Accounts deleted between these dates and inserts them into the same table

Once you are comfortable with this, please point to your database and create the operations as you require. If you have any questions or issues then please post them here.

Also, because your salesforce username and password are in the database, you can also export and post your Jitterpak safely if you run into errors and we can test it using our salesforce account. You can also email it to me if you'd prefer ilan.sehayek@jitterbit.com .

Thanks.

[The_fox]

Hi Ilan,

Thanks I gonna try this.

Do you have an idea about my SQL Error on the Unicode?

Regards

[Ilan]

I could not reproduce this. The database I used to create the Jitterpak is also Unicode so I do not believe that is the issue.

If the error persists then please remove your username and password from the transformation and export the Jitterpak. Also, please provide the sql statements to create the database here.

Please email that to me at ilan.sehayek@jitterbit.com

[The_fox]

Hi Ilan,

Here the SQL to create the table Account
CREATE TABLE "Acount"
(
"Id" varchar(18) NOT NULL,
"Name" varchar,
"Created DateTime" timestamptz NOT NULL,
"Last Update Date Time" timestamptz NOT NULL
)
WITHOUT OIDS;
ALTER TABLE "Acount" OWNER TO postgres

Just for your information, when I am making the query directly in Postgres it works

INSERT INTO "public"."Acount" ("Created DateTime","Id","Last Update Date Time","Name") VALUES ('2006-04-19 10:20:09','00120000002pCjrAAE','2006-05-24 09:58:21','Handelsbanken Kapitalföaltning') so maybe a issue in the driver?

Find the Jitterpark. Just Launch the Login as in your example.
Wich username/password I have to remove? Progresql or salesforce?
Edit by Henrik: removed Jitterpak because it contained user credentials.

[Henrik Nordberg]

Hi Ilan,

Thanks I gonna try this.

Do you have an idea about my SQL Error on the Unicode?

Regards

This error is due to PostgreSQL being configured to use UTF-8 and the client (Jitterbit) uses ISO 8859-1. You can enable automatic conversion in PostgreSQL by following the instructions here: http://www.postgresql.org/docs/8.1/stat ... ibyte.html
Look under the 21.2.3. Automatic Character Set Conversion Between Server and Client section.

Thanks
- Henrik

[Guest]

Thanks Henrik, I will try that

[The_fox]

Ok,

I have try it but still no luck in changing the client encoding. Where I put my sql command (database, table, server level?) and are you sure about ISO 8859-1 this should not be something like -5 or-6 or -8

Regards

[Guest]

Ok,

I have try it but still no luck in changing the client encoding. Where I put my sql command (database, table, server level?) and are you sure about ISO 8859-1 this should not be something like -5 or-6 or -8

Regards

I have succeeded in changing my database client encoding. I had to pass by the command line but still the same issue in my transformation

[tumusudheer]

Hai all. I am a beginner of this JitterBit, so please solve my problems & please do not hesitate to read this big message...

I imported the salesforce Jitter Pack.It automatically created a project. So I tried to deploy that project on to server. When i tried first, It gave me error , So i reindexed some of the tabels which are giving me probelms, At last I succeeded in deploying the project.

In the Login request , I changed the UserName & password . Then I choose the "Login to Query Account" operation, then I clicked on button to post this Operation on to the Queue.

Here is the actual problem I am getting, When i clicked to "Show Queue For selected Operation" Button,Some times I am able to see the operation there(on queue) & some times I am not able to see(seeing NO MATCHES FOUND ) . when I am seeing that , i am clicking the REFRESH button , then there is NO MATCHES FOUND entry is i am getting in queue.

1. How can i know whether my Operations are running or not?Where i can see the Error messages of the Operations?

2. When i saw the System Queue also, I am getting NO MATCHES FOUND entry only, Can u please tell me what to do?

3.How can I know that I am successfully connecting to salesforce?even CREATE ACCOUNT also not working?

4.Iam not able to see the .txt files on shared folder. why?

5. Is this problem arised due to reindexing of some of my tables at the begining?

6. Whenever I changed some parameters of project, is it necessary to depoly that project again on server?

Can any one please give me suggessions....

thank you.