Bulk uploading images to SSRS

问题:

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?

回答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

回答2:

To load multiple images in a SSRS solution, I used the following method. It's pretty easy First, create a new Images folder in the Report Manager where you want to link the external images in the report

1. Open Visual Studio
2. Navigate to File
3. Select Open
4. Select File
5. Create a New Folder called "Images" or whatever
6. Paste all images desired in this folder
7. Now, navigate to Solution Explorer
8. Select "Reports" and right-click
9. Select Add then "Existing Item"
10. Navigate to the new folder just created
11. Change the File Type at the bottom to "All Files"
12. Select all the images desired. Short cut to select all is the Ctrl key plus the letter A
13. Click "add"
14. All images will be added beneath any reports in the Solution Explorer

15. Navigate to the "Debug" menu and select the Solution Properties as seen here Solution Properties

16. Temporarily change the TargetReportFolder to match the name of a new images folder you created in Report Manager

17. In Solution Explorer, right-click on all the images (not the reports)
18. Select "Deploy"

Verify that the deployment was successful in Visual Studio then, navigate to the Report Manager folder and verify the images are there!

DON'T FORGET: Reset the TargetReportFolder to the previous value!

Report Manager Image Folder