How to convert Varbinary to Base64 in SSIS

Contents hide

1 Introduction

2 Prerequisites

3 Preparing Sample Data

4 Method 1 – Using Template Transform

4.1 Save Base64 string to Variable

5 Method 2 – Using Trash Destination

5.1 More from ZappySys Blog

Introduction

In this post we will show you how to convert varbinary to Base64 in SSIS. We will use FREE Tasks provided by ZappySys.

Prerequisites

Before we perform the steps listed in this article, you will need to make sure the following prerequisites are met:

SSIS designer installed. Sometimes it is referred to as BIDS or SSDT (download it from the Microsoft site).
Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
Make sure ZappySys SSIS PowerPack is installed (download it, if you haven't already).
(Optional step). Read this article, if you are planning to deploy packages to a server and schedule their execution later.

Preparing Sample Data

First let’s create a sample table with some Varbinary datatype. Run following command in SSMS to create a sample table with one sample row.

create table binarydata(
 data varbinary(max)
)
go
insert into binarydata(data )
values(0x5468697320697320612074657374) /*binary of "This is a test"*/

create table binarydata(

data varbinary(max)

)

insert into binarydata(data )

values(0x5468697320697320612074657374) /*binary of "This is a test"*/

Method 1 – Using Template Transform

Let’s use Template Transform to convert Varbinary data to Base64 format. When you use columns with binary data in Template transform it automatically converts to Base64 by default.

Go to Data flow
Drag your OLEDB Source and configure it to outputs SQL Data with Varbinary column (e.g. select data from dbo.binarydata).
Now drag ZS Template Transform from SSIS Toolbox
Connect OLEDB Source to Template Transform
Double click Template Transform to configure. Now you can click Insert Placeholders > Columns > Select your Upstream column name (which has Varbinary data)
You will see Template text as <%MyVarBinaryColumn%>
Click OK to Save Template Transform
Now connect Template transform to any Destination e.g. OLEDB Destination. You can Map TemplateOutput column to target column. This will contain Base64 representation of Varbinary data. For example purpose we used ZS Trash destination (Check save to file)

Convert Varbinary to BASE64 using SSIS Template Transform

Save Base64 string to Variable

In previous step we saw how Template Transform can convert binary data (i.e. Varbinary datatype) to BASE64 string. Now let’s look at how to save this value to Variable using Set Variable Transform.

Using SSIS Set Variable Transform in Data flow (Capture binary data as base64 example)

Method 2 – Using Trash Destination

Another way to dump Binary data to CSV file is use ZS Trans Destination. See previous screenshot. If you map OLEDB Source directly to Trash destination without Template Transform then you will see something like below.

data
VGhpcyBpcyBhIHRlc3Q=

1 2	data VGhpcyBpcyBhIHRlc3Q=