Get file content without temporary saving file

hi Mark,

So there is no database equivalent of the ‘read-file’ action process action? I tried an approach with a process procedure, and some t-sql I found for encoding the base64 into a string:

SELECT 

@zpl_file_code = dbo.decode_utf8_string(result) 

FROM (

SELECT 

CONVERT

(

VARCHAR(MAX), CAST('' AS XML).value('xs:base64Binary(sql:column("BASE64_COLUMN"))', 'VARBINARY(MAX)')

) AS result

FROM

(

SELECT 

file_content AS BASE64_COLUMN

FROM 

shipment_document

WHERE 

document_id=@document_id

) a

) x

This works in order to get the zpl code from the file, but when executing in the process action, it takes almost a minute to process:

Any idea’s for this?

Hi Tejo,

The Read file process action only reads the file data of the given File and places it in the file data output parameter. This usually is a variable with datatype varbinary(max). 

In case you have saved the Base64 data you have received from the 3rd party API, you can indeed decode the Base64 string as you explained. 

This process shouldn't take too long to execute, although I haven't tried using large files. I suspect the called function dbo.decode_utf8_string may be the issue. Could you execute the query and show the Execution plan? (Ctrl+M to turn on before execution)

The Execution plan can be used to see where SQL Server uses the most resources to execute the query. This may show the spot that causes the performance issue.

For example, the Execution plans for the above queries are as follows:

Could you try this and share this plan? Thanks!

hi Mark,

The function is indeed the issue, I got it from https://stackoverflow.com/questions/28168055/convert-text-value-in-sql-server-from-utf8-to-iso-8859-1 so I guess I have to get another decode function :-) 

But I get the idea of the process procedure, although it’s a shame it’s not showing in the process flow designer. Thanks for your help!