Salesforce connector and Attachment API (Blobs and whatnot)

[mrthorne]

Has anyone had any luck/experience with the Salesforce connector and successfully inserting new rows to the Attachment API?  I have a simple workflow where I take a sample text file into the Blob Input input tool, convert the blob to base64 (per Salesforce API), then append an exisitng parentId and Name (along with renaming the Blob input to 'Body' and shove that into a Salesforce output tool pointing at the Attachment table.

The result is a target record generated, but the response from the Salesforce output tool is "0 records processed and 0 failed across 1 batch".

I've played around with some other combinations of adding other non-required fields (CreatedDate, Description etc) with no changes.  If I add something that is not part of the table the Salesforce output tool properly errors out.

Any ideas?

Thanks!

[NeilR · Accepted solution]

Blob fields are currently not supported with the Salesforce Output tool (see bottom of this page). The Salesforce Output tool uses the Bulk API, and it looks like this API does not support base64 fields. What API documentation are you referencing? You may be able to create your own custom workflow/macro using the Download tool to get at this functionality.

[mrthorne]

Thanks for the response!

So if the Salesforce Output tool uses the Bulk API (and that Blob isn't supported), that at least tells me why things aren't working as designed.  I was referencing the Attachment SOAP API which talks about the "Body" field (binary data), needing to be first converted to base64 before the insert.

Looking a little into the Spring '16 Salesforce documentation, it's interesting that in the same paragraph mentions this:

"The REST Bulk API lets you query, insert, update, upsert, or delete a large number of records asynchronously. The records can include binary attachments, such as Attachment objects or Salesforce CRM Content."

..and then at the end of that same paragrapoh states what you saw:

"Currently base64 fields are not supported in queries with the Bulk API."

It appears Attachments can be inserted using a batch request instead which will require the creation of a slightly different workflow/macro to make it work properly.  A manifest file has to be created with the attachments included.  I'll play around with this to see if I can get something working.  Unfortunate that it's not quite as slick as using the built in tool, but at least I know where not to spend any more time!

Thanks for the pointing in the right direction.

[NeilR]

The built in Salesforce Output tool has a macro back-end - you could try using it as a starting point. You can find it here:

C:\Program Files\Alteryx\bin\RuntimeData\Macros\Supporting_Macros\SalesforceOutput.yxmc

[aMac]

Thanks Neil and to the OP-- this is an excellent thread for a similar problem we're trying to solve. Would you happen to have a workflow handy that does not use Salesforce's Bulk-api?

I cracked Salesforce's Output tool open, and this is a beast of a macro to replicate for the sake of not using Bulk-API

Thanks!

[mrthorne]

aMac I spent a little time working through building a workflow for the non-bulk API version of this and ultimately abandoned my efforts just because I needed to move on.  I ultimately ended up taking my attachments into Salesforce via a custom c# app instead.  I know our data team would still love to have an out of the alteryx box version of a non-bulk API connection tool to Salesforce though.

[aMac]

Sorry to hear you had to move on, but those are certainly familiar circumstances. I actually was able to POST a Base64 encoded Attachment to a Custom Object, so this solution is tantalizingly in range to build.

However, my Base64 tool was actually encoding the literal filepath string, so now I am trying to wrestle with the Blob tool and we'll see if the REST API will actually enable us to write 2GB files as attachments.

I will certainly keep this thread updated with any breakthroughs and would love any help/insight that the community could guide on when it comes Download Tool and SF's REST API (not Bulk API)

[aMac]

Quick update on my efforts: I have been able to POST consistently larger files as Attachments to Salesforce via the Workbench. However, when I tried to write a Base64 encoded Blob using a Download tool, I consistently got a "Invalid Session Key" 401 Error from Salesforce. I even copied the portion of the 'generate access/session' key from the native Output macro in the \bin\ folder.

@NeilR - I am generating a session key via hardcoded credentials in an initial Download tool using the same structure as the Download tool in the 'Retrieve Session Key' Output section and then appending that Session Id as a field that is added as a Header in a second POST Download tool where I pass in a JSON constructed using the JSON Build tool.

Mock JSON:

{
"ParentId" :"[parent id string]"
"Name":"Test Attach Upload"
"Body" : "[Maaasssive Base64 encoded blob string]"
}

Also changed the Content-Type header to:

Content-Type | application/json

However, I am still returning 401/Invalid Session Id errors-- any idea what could be going on?

cc: @TashaA and any other Alteryx Content folks that may have any ideas

edited to update post with the change to the Download tool's Content-Type Header.

[NeilR]

It would help if you posted your workflow and the relevant API documentation you're looking at.

[aMac]

Thanks @NeilR, I uploaded a mocked up version of the workflow.

Re-reading the Docs, I suspect I need to actually create a session_id against the end point I am trying to perform a REST method against, right now I am authenticating via what looks like the standard SOAP API that was configured in the standard Salesforce Output macro.

I am not entirely sure how I could reconfigure for the REST API, this is where I could really use some help!

As far as Salesforce Docs, I have been struggling to find exactly what I need to explain the process. I have been referencing a few 'Invalid Session_Id' posts:

Invalid Session Id from SF Rest API

REST API Spring '17 - skip to page 12 on authentication, and the diagram on 13 is helpful in diagramming out the flow

edit: I have obfuscated my sf login in the mock-- I am assuming you will test with your own org. Please DM me if you would like to test with my org