Convert SQL Server Base64 Encoded Text to Images

By: Simon Liew   |   Updated: 2018-09-05   |   Comments   |   Related: > TSQL

Problem

A table in our SQL Server database stores images in Base64-encoded text format. It does not seem that SQL Server CAST() and CONVERT() can convert Base64-encoded text into images. Is there an easy way to decode and convert these BASE64-encoded data back into an image and save them on the file system?

Solution

Base64 encoding schemes are commonly used when there is a need to encode binary data that needs be stored and transferred over media that are designed to deal with textual data. From this definition, we would expect the Base64 content is stored in a VARCHAR(max) data type in SQL Server. SQL Server CONVERT() and CAST() function do not recognize Base64 textual content specifically, it treats Base64 content as like any normal text.

But fortunately, hidden in SQL Server XQuery, the support to encode and decode BASE64 format is readily available since SQL Server 2005. You can then leverage SQL Server Data Tools (SSDT) to loop and download all the decoded Base64 content to the file system in image format.

In this tip, we will run through the actual mechanics to the solution using XQuery and SSDT. The solution is built using SSDT for Visual Studio 2013 and the table is in a database on SQL Server 2016 Developer Edition.

At the end of this tip, there is a link to download the SSDT solution and the database used in this tip.

Demonstration

We have table [dbo].[Base64Images] with the table definition below. This table is populated with 2 sample rows. The type of the image can be determined by the first 4 letters in the Base64 content. Image type PNG would begin with “iVBO”, and image type JPG will begin with “/9j/” to name a couple of examples.

Here we can see our sample images use image type PNG since the Base64 content starts with "iVBO".

SELECT *
FROM [ImageDB].[dbo].[Base64Images]

Step 1 - Decode and Convert the Base64-encoded Text into Binary Type

The T-SQL below uses XQuery to decode and convert the Base64-encoded text into binary type. This query will be subsequently used as a source input in SSDT.

WITH ConvertBase64 AS (
SELECT 
   Base64Content 
   , 'C:\mssqltips\images\' + FileName as [PathAndFileName]
FROM [ImageDB].[dbo].[Base64Images]
)
SELECT 
   [PathAndFileName]
   , CAST('' AS XML).value('xs:base64Binary(sql:column("Base64Content"))'
     , 'VARBINARY(MAX)') AS img
FROM ConvertBase64

Step 2 - Create New SSIS Package

Using SSDT, create a blank SSIS package and configure a new Connection Manager using [Native OLE DB\SQL Server Native Client] to the source database containing the table [dbo].[Base64Images]. The connection manager name in this solution is ImageDB.

Step 3 - Add a Data Flow Task in Integration Services

Drag and drop a [Data Flow Task] from the SSIS Toolbox into the [Control Flow].

Step 4 - Setup the Data Source in SSIS

Double-click the [Data Flow Task] to go into the [Data Flow]. Inside the [Data Flow Task], drag and drop an [OLE DB Source] from SSIS Toolbox.

When you double-click the [OLE DB Source] here, it will launch the [OLE DB Source Editor] form. Set the OLE DB connection manager here to the connection manager created in Step 1 which is ImageDB. Paste the T-SQL query from Step 1 to the SQL command text. Essentially, this query returns 2 columns as described in the table below.

Step 5 - Map the Columns in SQL Server Integration Services

Click on the Columns page. This will reflect the 2 columns output from the query, PathAndFileName and img column. This step is actually very important to map the columns from the query to the [OLE DB Source]. Click the [OK] button to go back to the SSIS package.

Step 6 - Export Column Transformation in Integration Services

In the [Other Transforms] section in the SSIS Toolbox, drag and drop the [Export Column].

Connect the precedence to specify [OLE DB Source] as the source, and [Export Column] as the destination.

Double-click the [Export Column] task, it will launch the [Export Column Transformation Editor]. Specify column [img] in the Extract Column since this contains the binary image, and the column [PathAndFileName] in the File Path Column.

Step 7 - Execute the SSIS Package

This is the final step to execute the package or the [Data Flow Task]. We will execute the [Data Flow Task] as below.

The execution of the [Data Flow Task] is successful.

Step 8 - Review the Exported Files

Go to the path specified in the query. There are 2 image files in folder C:\mssqltips\images folder.

Conclusion

XQuery contains many hidden gems to complement T-SQL query in SQL Server to greatly simplify some challenging scenarios. In addition, SSDT is a very powerful free ETL tool; especially the [Export Column] task is very useful for downloading images stored in database table. It removes a lot of complexity for developers and requires only a few parameter configurations to get it to work.

Next Steps

• Download SSDT solution from this tip
• Download the database from this tip
• Here are some additional articles:
  • Export Column Transformation
  • Integration Services (SSIS) Connections
  • Base64
  • Use the BINARY BASE64 Option

About the author

Simon Liew is an independent SQL Server Consultant in Sydney, Australia. He is a Microsoft Certified Master for SQL Server 2008 and holds a Master’s Degree in Distributed Computing.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips