Bulk uploading images to SSRS

Question

We have used the excellent RSBuild for bulk uploading reports to SQL Server Reporting Services (SSRS) but I find no way to bulk upload images. Using the Report Manager to upload one image at a time is silly. The difficulty is that the SQL Server Reporting web service that RSBuild uses doesn't support image uploads. Is there a way to bulk upload images to SSRS in some other fashion?

Answer 1

You will need to upload one image manually first so you read the contents of the Catalog table for the image row it inserted in the ReportServer database and use the ParentId, PolicyId and CreatedById and ModifiedById in the following script.

Of course, this can be further abstracted into an application and be more awesome.

BEGIN TRAN
    DECLARE @ReportFolderPath NVARCHAR(MAX)
    DECLARE @ImageFolderPath NVARCHAR(MAX)
    DECLARE @ImageFileName NVARCHAR(MAX)
    DECLARE @ImageFullFileName NVARCHAR(MAX)
    DECLARE @ImageFileExtension NVARCHAR(MAX)
    DECLARE @ImageMime NVARCHAR(MAX)
    DECLARE @ImageFullPath NVARCHAR(MAX)
    DECLARE @ParentId NVARCHAR(MAX)
    DECLARE @PolicyId NVARCHAR(MAX)
    DECLARE @CreatedModifiedId NVARCHAR(MAX)
    DECLARE @SqlToGetImageContent NVARCHAR(MAX)
    DECLARE @ImageContent VARBINARY(MAX)

    SET @ReportFolderPath = '/MyReports/'
    SET @ImageFolderPath = 'C:\Users\jdoe\Desktop\Reports\images\'
    SET @ImageFileName = 'logo'
    SET @ImageFileExtension = 'bmp' 
    SET @ImageFullFileName = @ImageFileName + '.' + @ImageFileExtension
    SET @ImageFullPath = @ImageFolderPath + @ImageFileName  
    SET @ParentId = '0AAFF0D8-0616-4E63-9B1D-EBF99153B443'      
    SET @PolicyId = '8632B07A-EE75-4097-970C-18BE9958F7A2'                                                          
    SET @CreatedModifiedId = 'C6121121-D0E4-4B25-BD4E-177EDA709ECB'
    SET @SqlToGetImageContent = 'SELECT @ImageContent = BulkColumn FROM Openrowset(Bulk '''+ @ImageFolderPath + @ImageFullFileName +''', Single_Blob) AS ImageData'

    IF(@ImageFileExtension = 'jpg')
        BEGIN
            SET @ImageMime = 'image/jpeg'
        END
    ELSE
        BEGIN
            SET @ImageMime = 'image/' + @ImageFileExtension
        END


    EXEC SP_EXECUTESQL 
        @Query  = @SqlToGetImageContent
      , @Params = N'@ImageContent VARBINARY(MAX) OUTPUT'
      , @ImageContent = @ImageContent OUTPUT

    INSERT INTO [ReportServer$DEV2012].[dbo].[Catalog] (ItemID, [Path], Name, ParentID, [Type], Content, [Intermediate], SnapshotDataID,    LinkSourceID,   Property,   [Description],  Hidden, CreatedByID,    CreationDate,   ModifiedByID,   ModifiedDate,   MimeType,   SnapshotLimit,  Parameter,  PolicyID, PolicyRoot, ExecutionFlag, ExecutionTime, SubType, ComponentID)
    VALUES(
            NEWID(),
            @ReportFolderPath + @ImageFullFileName, 
            @ImageFullFileName, 
            @ParentId,  
            3,  
            (SELECT @ImageContent),
            NULL,   
            NULL,   
            NULL,   
            '<Properties />',   
            NULL,   
            0,  
            @CreatedModifiedId, 
            GETDATE(),  
            @CreatedModifiedId,
            GETDATE(),  
            @ImageMime, 
            NULL,   
            NULL,   
            @PolicyId,  
            0,  
            1,  
            NULL,   
            NULL,   
            NULL
        )
COMMIT TRAN