In this post we will show you how to convert varbinary to Base64 in SSIS. We will use FREE Tasks provided by ZappySys.
PrerequisitesBefore 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(
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)
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.
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.