Thursday, March 3, 2016

Access Salesforce REST API from Talend Open Studio

Today I have a task to pull report data from Salesforce REST API into MySQL database. I've used Talend Open Studio to sync daily Salesforce's modules into Postgresql or replicate MS SQL database into Postgresql before so looks like this task is not difficult. It turned out the Salesforce REST API now requires OAuth 2.0, so I googled to find out how to do that and got this http://www.deepinopensource.com/access-apex-rest-api-salesforce-from-talend/ . The problem is this guide's using cURL to get access_token to enter manually into tRESTClient while I need to do it automatically for daily auto-sync.. So I learned more about Talend's components.. (found it's very powerful!). Here is my solution: use one tRESTClient to request an access token first to assign into context variable, then execute another tRESTClient to access Salesforce REST API with that token.

Below are screenshots.


1. Request a token

2. Extract the token from output

3. Assign token into context variable

4. Use that token for REST

5. Because the Salesforce REST output contains special node name that causes invalid parsing so replace it with a common character

6. Extract the JSON data
 7. Save into MySQL database



15 comments:

  1. Hello Luan. This is great stuff! I like the fact that you took screenshots and explained every step. Did you ever get an error "javax.ws.rs.BadRequestException: HTTP 400 Bad Request" on step #1? I don't know if this error is due to the Salesforce Connected App setup or something else. Thanks for your feedback. Daniel.

    ReplyDelete
    Replies
    1. hello, please can u tolde me how did u get ride of the error "HTTP bad request, invalid JSON data" ??

      Delete
  2. Found the problem; the URL I was using was not the one from the Salesforce sandbox I needed to connect to. Now, I have another problem: "unable to find valid certification path to requested target". Any ideas?

    ReplyDelete
  3. seems SF dev sandbox endpoint doesn't have valid ssl cert. you can google : salesforce unable to find valid certification path to requested target
    I think you might try https://github.com/escline/InstallCert to import SF cert into your computer:
    ex: java InstallCert SF_URL:443
    then copy generated jssecacerts into your %JAVA_HOME%\lib\security

    ReplyDelete
  4. Hey thanks for the feedback! Have you tried executing this job recently? From a year ago up to today, Salesforce has updated their TLS specifications, and the org may require to use TLS 2.0 now. Is that your situation too?

    I resolved the certification problem but now I'm back to the "HTTP 400 Bad Request" message. I see in Salesforce that the login attempt is recorded, but the message simply sais "Invalid password", even though I've checked and I'm using the password with token correctly.

    ReplyDelete
  5. nope. I've switched to use Python to download CSV file as the API limits to get 2K records.
    http://luan-itworld.blogspot.com/2016/03/download-salesforce-report-in-csv-to.html

    make sure to enter correct client id, client secret, user, password, grant type..

    ReplyDelete
  6. OMG!!! I found my problem! It was soooooo hard and yet soooooo easy. The issue I was having is that my password contained an ampersand character ("%") and after doing some digging, I figured this was interfering with the "POST" call. These kinds of things nobody tells you so it's best to document it for future reference.

    Luan: great job in posting this and thank you for responding my questions so promptly!

    Good luck to all...

    ReplyDelete
  7. Hey Luan... I've got a question about a Talend scenario I'm trying to solve for.. maybe you can provide some ideas as to where to find the solution?

    In basic terms, I need to generate JSON output with 200 records at a time from a csv file of 1,000 records. The structure is:

    tFileInputDelimited --> tMap --> tWriteJSONField --> tFileOutputRaw

    Since the file reads "all" the 1,000 records, it creates one JSON output with all the 1,000 records, but I need to split the records up in batches of 200 so that I get 5 different JSON outputs with 200 records each.

    Any ideas what components I need?
    Thanks.

    ReplyDelete
  8. This is an idea of what I'm trying to accomplish:
    https://www.talendforge.org/forum/viewtopic.php?id=29953

    ReplyDelete
  9. sorry I don't have time to test it. For your case I think just use a simple python script
    FYI https://stackoverflow.com/questions/19697846/python-csv-to-json

    ReplyDelete
  10. This comment has been removed by a blog administrator.

    ReplyDelete
  11. This comment has been removed by a blog administrator.

    ReplyDelete
  12. This comment has been removed by a blog administrator.

    ReplyDelete
  13. Hi Luan,

    your implementation is very similar to what I am trying to do. So, I was trying to build a job and got stuck at the component "tExtractJSONFields", I am unable to get access-token in the mapping area.

    Any help is appreciated!

    ReplyDelete
  14. can we also post to salesforce rest api

    ReplyDelete