SSRS - Loading and Using A SQL Server Database Image

This blog entry discusses how to add an image to SQL Server for storage within the database, followed by how to use it in SSRS.

When including an image within a SQL Server Reporting Services report, you have 3 options:

1. Embedded.  The image is embedded within the report.  This is my least favorite choice, as it’s not easily reusable and maintenance of a change would be a big headache if very many reports existed with individual embedded images.
2. External.  The image is loaded to the Report Project and may be reused among multiple SSRS reports.  This approach was discussed in this ImagePaths in SSRS entry.  
3. Database.  The image is loaded to a SQL Server database and, like option 2, may be reused among multiple SSRS reports.  Because it’s stored in the database, it can be used for many other types of solutions as well.  This is the most flexible of the 3 options - and the topic of the rest of this entry.

Using OPENROWSET to Insert Image Into Table

1.  First, let’s create a table in SQL Server Management Studio to hold the image file.

CREATE TABLE dbo.Images

(

      [ImageID] [int] IDENTITY(1,1) NOT NULL,

      [ImageName] [varchar](40) NOT NULL,

      [OriginalFormat] [nvarchar](5) NOT NULL, 

      [ImageFile] [varbinary](max) NOT NULL

 )    

Note that the data type we’re using for the image is varbinary(max) instead of the image data type.  That’s because the image data type is being removed in a future version of SQL Server.

2.  Right-click this dog image and save it to your c:\ drive as “MyImage.png”.

  

3.  Now let’s insert the dog image into the database table we created in step 1.

INSERT INTO dbo.Images

(

       ImageName

      ,OriginalFormat

      ,ImageFile

)

SELECT

      'Sample Image'

      ,'png'

      ,ImageFile

FROM OPENROWSET(BULK N'C:\MyImage.png', SINGLE_BLOB) AS ImageSource(ImageFile);

That’s it!  Now you’re ready to use it.  I like to include an “Original Format” field so that I can easily remember what to set for the MIME type in SSRS.

SSRS Image Properties

In this section we’ll discuss how to use the database image in SSRS.

1.  First, create a dataset within your report which contains the following query.  I’m calling my dataset “HeaderImage.”

SELECT ImageFile

FROM dbo.Images

2.  From the Toolbox, drag an Image Report Item onto the body of the report.  Configure it as follows:

Select the image source:  Database

Use this field:  =First(Fields!ImageFile.Value, “HeaderImage”)

Use this MIME type:  image/png    (this is the MIME type & subtype, which classifies file types on the web for consistent handling between browsers & O/S)

And voila, you have a drooling puppy on your report - just what you've always wanted!