To generate the BASE64 encoded string of the local file content (binary or text), we can use FUN_FILE_BASE64ENC Placeholder Function. We have documented it in detail here.
Syntax to use FUN_FILE_BASE64ENC placeholder function
<< file_path,FUN_FILE_BASE64ENC>>
We need to set the local file physical path for which we like to encode the content as a Base64 string in the file_path parameter. We can use the SSIS variable as well instead of a hard-coded file path.
Sample Usage Example of the FUN_FILE_BASE64ENC:
<<c:\someimage.png,FUN_FILE_BASE64ENC>>
–OR–
{{User::MySSISVar1,FUN_FILE_BASE64ENC}}
Let’s try to achieve that in SSIS using the ZS Logging Task
ZS Logging Task can display the variable values in the SSIS output (ExecutionLog) in a MessageBox or a file or a variable.
- First, You need to Download and Install SSIS ZappySys PowerPack.
- Once you finished the first step, Open Visual Studio and Create a New SSIS Package Project.
- In Visual Studio just Drag and Drop the ZS Logging Task in the design panel.
- Double-click on ZS Logging Task to configure it.
- Set Log Mode to ExecutionLog and use the following text.
<<C:\Your_File_Path.File_Extension,FUN_FILE_BASE64ENC>>
- Click on the OK button to save the Logging Task configure setting UI.
- Finally, run the package and check the output (ExecutionLog). If you cannot see the output, go to View in the menu and Select Output.
POST file content as Base64 to API in ODBC
Here is example how you can do this in ODBC PowerPack with JSON / XML or CSV Driver
SELECT * FROM $
WITH
(
METHOD='POST'
,HEADER='Content-Type: application/xml || x-hdr1: SomeValue'
,SRC='https://your-api.com/create'
,BODY='
<data>
<attachment><<c:\temp\icecream_1.png,FUN_FILE_BASE64ENC>></attachment>
</data>'
--,Filter='$.root.row[*]'
)
And that’s it, In a few easy configurations we’re able to generate the Base64 encoded string of the file content. It can support large files as well, We have used a 30MB file and it gave the Base64 encoded string in 1 second only.