SSIS Base64 Encode Decode without Coding

Introduction

In this post you will learn how to use FREE SSIS Logging Task to perform ssis base64 encode decode and save to Variable or File  (i.e. convert Base64 Encoded value to plain text or encode plain text to base64) . You can also use many other format specifiers found here

How to invoke Placeholder Functions

There are 3 ways you can use placeholder functions in many tasks with Variable Icon next to the property.

1. User Variable Placeholder
2. Static String Placeholder
3. Mixed Placeholder (Static String with Variable)

Examples: 

{{User::var1,FUN_BASE64ENC}}  –OR–
<<some direct string,FUN_BASE64ENC>>

–OR–

<<{{User::var1}} and {{User::var2}} ,FUN_BASE64ENC>>

Encode Plain text to Base64 string (SSIS base64 encode decode)

Many times you have requirement to convert plain text into Base64 encoded string (This is very common when you have to transfer data over the web – set query string / http headers / body ). Typically to perform this you may have to write C# Script if you are SSIS developer but below is very simple way to do without writing any code. Here is the most common use case of Base64 encoded string

1. Download FREE Tasks/Components of SSIS PowerPack
2. Create new SSIS Package
3. Drag ZS Logging Task from Toolbox
4. Create SSIS variable which holds plain text value (Lets call it varPlainText. Set some initial value which you want to encode)
5. Configure SSIS Logging Task as below
   

   SSIS Base64 Encode, Decode using SSIS Logging Task (Without coding convert plain text to base 64 string)

6. Run it and you will see new file created with encoded value. You can also save encoded value to Variable if you change Logging Mode = Variable rather than File

Convert Base64 Encoded value to plain text (Decode)

To convert Base64 encoded string into plaintext you can perform similar steps described in the above section except you need to use BASE64DEC format specifier rather than BASE64ENC

Example:

 

How to decode Base64 file in SSIS and save to new file

If you have a file coming as a base64 encoded string and you want to decode it and save it to a new file then it’s simple too. Basically, you will need SSIS Advanced File System Task  and SSIS Logging Task to achieve this (see package below).

Here are your steps.

1. Use SSIS Advanced File System Task and use read file content option to save file content into SSIS Variable
2. Configure Advanced File System as below.
   

   Read file content into SSIS variable using SSIS Advanced File System task

3. Next step connect SSIS Logging Task
4. Configure SSIS Logging Task as per below screenshot.
   

   Decode base64 file content in SSIS – Use Logging Task

Convert Base64 Encoded string of local file content (Binary or Text)

Using FUN_FILE_BASE64ENC we can encode the image or any other local file(Binary or Text) to the Base64 string.

FUN_FILE_BASE64ENC

Syntax:

Returns BASE64 encoded string of local file content (binary or text).

Parameters:

Name	Description
file_path	[String] File path for which you like encode content as Base64.

Examples:

Sample Output:

This returns base64 encoded string of binary file (png)