批量上传报表的工具

很多朋友都用过reporting service，以及使用有关工具制作报表（文件格式为rdl)，通常情况下，这些报表需要部署到报表服务器，然后再统一访问。当报表比较多的时候，我们自然而然地会想，是否有什么方法批量上传报表么？答案是肯定的。

第一种方式，可以使用reporting service自带的一个rs的工具，通过提供一个脚本文件（扩展名为rss)，就可以实现该需求。微软提供了一个范例如下

C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Script Samples\PublishSampleReports.rss

大致的脚本和使用方式如下

可以看出，这些所谓的脚本是用VB.NET编写的。其实就是.NET编程，但因为它是所谓脚本的方式，反倒有些地方不是很自然了。例如代码中的rs在哪里定义的，是什么类型呢？我的理解是这个rs其实就是指向了reporting service所提供的那些web service。但是的确有点费解。

 

批量上传报表的脚本
'=============================================================================
'  File:      PublishSampleReports.rss
'
'  Summary:  Demonstrates a script that can be used with RS.exe to 
'         publish the sample reports that ship with Reporting Services.
'
'---------------------------------------------------------------------
' This file is part of Microsoft SQL Server Code Samples.
'
'  Copyright (C) Microsoft Corporation.  All rights reserved.
'
' This source code is intended only as a supplement to Microsoft
' Development Tools and/or on-line documentation.  See these other
' materials for detailed information regarding Microsoft code samples.
'
' THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
' KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
' IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
' PARTICULAR PURPOSE.
'=============================================================================
'
' 1.0 Documentation
'
' Read the following in order to familiarize yourself with the sample script.
' 
' 1.1 Overview
'
' This sample script uses a script file (.rss) and the script environment to run 
' Web service operations on a specified report server. The script creates a folder 
' that you specify as a command-prompt variable using the 杤 switch, and then 
' publishes the sample reports that ship with Reporting Services to a report server.
' Depending on the location of your sample reports, you may need to modify the 
' value of the filePath variable, which references the path to your sample reports.
' 
' 1.2 Script Variables
'
' Variables that are passed on the command line with the -v switch:
'
' (a) parentFolder - corresponds to the folder that the script creates and uses 
'     to contain your published reports
'
' 1.3 Sample Command Lines
' 
' 
' 1.3.1 Use the script to publish the sample reports to an AdventureWorks Sample Reports folder.
' 
'       rs -i PublishSampleReports.rss -s http://myserver/reportserver
' 

Dim definition As [Byte]() = Nothing
Dim warnings As Warning() = Nothing
Dim parentFolder As String = "AdventureWorks Sample Reports"
Dim parentPath As String = "/" + parentFolder
Dim filePath As String = "C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Report Samples\AdventureWorks Sample Reports\" 

Public Sub Main()Sub Main() 

    rs.Credentials = System.Net.CredentialCache.DefaultCredentials
    'Create the parent folder
    Try
        rs.CreateFolder(parentFolder, "/", Nothing)
        Console.WriteLine("Parent folder {0} created successfully", parentFolder)
    Catch e As Exception
        Console.WriteLine(e.Message)
    End Try 

    'Create the AdventureWorks shared data source
    CreateSampleDataSource("AdventureWorks", "SQL", "data source=(local);initial catalog=AdventureWorks")
    CreateSampleDataSource("AdventureWorksDW", "OLEDB-MD", _
        "data source=localhost;initial catalog=Adventure Works DW") 

    'Publish the sample reports
    PublishReport("Company Sales")
    PublishReport("Employee Sales Summary")
    PublishReport("Product Catalog")
    PublishReport("Product Line Sales")
    PublishReport("Sales Order Detail")
    PublishReport("Territory Sales Drilldown") 

End Sub 

Public Sub CreateSampleDataSource()Sub CreateSampleDataSource(name As String, extension As String, connectionString As String)
    'Define the data source definition.
    Dim definition As New DataSourceDefinition()
    definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
    definition.ConnectString = connectionString
    definition.Enabled = True
    definition.EnabledSpecified = True
    definition.Extension = extension
    definition.ImpersonateUser = False
    definition.ImpersonateUserSpecified = True
    'Use the default prompt string.
    definition.Prompt = Nothing
    definition.WindowsCredentials = False 

Try
    rs.CreateDataSource(name, parentPath, False, definition, Nothing)
    Console.WriteLine("Data source {0} created successfully", name) 

Catch e As Exception
    Console.WriteLine(e.Message)
End Try
End Sub 

Public Sub PublishReport()Sub PublishReport(ByVal reportName As String)
    Try
        Dim stream As FileStream = File.OpenRead(filePath + reportName + ".rdl")
        definition = New [Byte](stream.Length) {}
        stream.Read(definition, 0, CInt(stream.Length))
        stream.Close() 

    Catch e As IOException
        Console.WriteLine(e.Message)
    End Try 

    Try
        warnings = rs.CreateReport(reportName, parentPath, False, definition, Nothing) 

        If Not (warnings Is Nothing) Then
            Dim warning As Warning
            For Each warning In warnings
                Console.WriteLine(warning.Message)
            Next warning 

        Else
            Console.WriteLine("Report: {0} published successfully with no warnings", reportName)
        End If 

    Catch e As Exception
        Console.WriteLine(e.Message)
    End Try
End Sub 

 

第二种方式，自己编写一个工具来完成更加丰富的控制。这里可以用任何的语言，只要你熟悉即可。

这里的关键在于，添加对reporting service的web service的引用。下面是它所有的方法

"ReportingService2005" 说明

文档

The Reporting Services Web Service enables you to manage a report server and its contents including server settings, security, reports, subscriptions, and data sources.

方法

• CancelBatch ( )

• CancelJob ( JobID As string ) As boolean

• CreateBatch ( ) As string

• CreateDataDrivenSubscription ( Report As string ,  ExtensionSettings As ExtensionSettings ,  DataRetrievalPlan As DataRetrievalPlan ,  Description As string ,  EventType As string ,  MatchData As string ,  Parameters As ArrayOfParameterValueOrFieldReference ) As string

• CreateDataSource ( DataSource As string ,  Parent As string ,  Overwrite As boolean ,  Definition As DataSourceDefinition ,  Properties As ArrayOfProperty )

• CreateFolder ( Folder As string ,  Parent As string ,  Properties As ArrayOfProperty )

• CreateLinkedReport ( Report As string ,  Parent As string ,  Link As string ,  Properties As ArrayOfProperty )

• CreateModel ( Model As string ,  Parent As string ,  Definition As base64Binary ,  Properties As ArrayOfProperty ) As ArrayOfWarning

• CreateReport ( Report As string ,  Parent As string ,  Overwrite As boolean ,  Definition As base64Binary ,  Properties As ArrayOfProperty ) As ArrayOfWarning

• CreateReportHistorySnapshot ( Report As string ) As string

• CreateResource ( Resource As string ,  Parent As string ,  Overwrite As boolean ,  Contents As base64Binary ,  MimeType As string ,  Properties As ArrayOfProperty )

• CreateRole ( Name As string ,  Description As string ,  Tasks As ArrayOfTask )

• CreateSchedule ( Name As string ,  ScheduleDefinition As ScheduleDefinition ) As string

• CreateSubscription ( Report As string ,  ExtensionSettings As ExtensionSettings ,  Description As string ,  EventType As string ,  MatchData As string ,  Parameters As ArrayOfParameterValue ) As string

• DeleteItem ( Item As string )

• DeleteReportHistorySnapshot ( Report As string ,  HistoryID As string )

• DeleteRole ( Name As string )

• DeleteSchedule ( ScheduleID As string )

• DeleteSubscription ( SubscriptionID As string )

• DisableDataSource ( DataSource As string )

• EnableDataSource ( DataSource As string )

• ExecuteBatch ( )

• FindItems ( Folder As string ,  BooleanOperator As BooleanOperatorEnum ,  Conditions As ArrayOfSearchCondition ) As ArrayOfCatalogItem

• FireEvent ( EventType As string ,  EventData As string )

• FlushCache ( Report As string )

• GenerateModel ( DataSource As string ,  Model As string ,  Parent As string ,  Properties As ArrayOfProperty ) As ArrayOfWarning

• GetCacheOptions ( Report As string ) As boolean

• GetDataDrivenSubscriptionProperties ( DataDrivenSubscriptionID As string ) As string

• GetDataSourceContents ( DataSource As string ) As DataSourceDefinition

• GetExecutionOptions ( Report As string ) As ExecutionSettingEnum

• GetExtensionSettings ( Extension As string ) As ArrayOfExtensionParameter

• GetItemDataSourcePrompts ( Item As string ) As ArrayOfDataSourcePrompt

• GetItemDataSources ( Item As string ) As ArrayOfDataSource

• GetItemType ( Item As string ) As ItemTypeEnum

• GetModelDefinition ( Model As string ) As base64Binary

• GetModelItemPermissions ( Model As string ,  ModelItemID As string ) As ArrayOfString

• GetModelItemPolicies ( Model As string ,  ModelItemID As string ) As ArrayOfPolicy

• GetPermissions ( Item As string ) As ArrayOfString3

• GetPolicies ( Item As string ) As ArrayOfPolicy

• GetProperties ( Item As string ,  Properties As ArrayOfProperty ) As ArrayOfProperty

• GetRenderResource ( Format As string ,  DeviceInfo As string ) As base64Binary

• GetReportDefinition ( Report As string ) As base64Binary

• GetReportHistoryLimit ( Report As string ) As int

• GetReportHistoryOptions ( Report As string ) As boolean

• GetReportLink ( Report As string ) As string

• GetReportParameters ( Report As string ,  HistoryID As string ,  ForRendering As boolean ,  Values As ArrayOfParameterValue ,  Credentials As ArrayOfDataSourceCredentials ) As ArrayOfReportParameter

• GetResourceContents ( Resource As string ) As base64Binary

• GetRoleProperties ( Name As string ) As ArrayOfTask

• GetScheduleProperties ( ScheduleID As string ) As Schedule

• GetSubscriptionProperties ( SubscriptionID As string ) As string

• GetSystemPermissions ( ) As ArrayOfString3

• GetSystemPolicies ( ) As ArrayOfPolicy

• GetSystemProperties ( Properties As ArrayOfProperty ) As ArrayOfProperty

• GetUserModel ( Model As string ,  Perspective As string ) As base64Binary

• InheritModelItemParentSecurity ( Model As string ,  ModelItemID As string )

• InheritParentSecurity ( Item As string )

• ListChildren ( Item As string ,  Recursive As boolean ) As ArrayOfCatalogItem

• ListDependentItems ( Item As string ) As ArrayOfCatalogItem

• ListEvents ( ) As ArrayOfEvent

• ListExtensions ( ExtensionType As ExtensionTypeEnum ) As ArrayOfExtension

• ListJobs ( ) As ArrayOfJob

• ListModelDrillthroughReports ( Model As string ,  ModelItemID As string ) As ArrayOfModelDrillthroughReport

• ListModelItemChildren ( Model As string ,  ModelItemID As string ,  Recursive As boolean ) As ArrayOfModelItem

• ListModelPerspectives ( Model As string ) As ArrayOfModelCatalogItem

• ListReportHistory ( Report As string ) As ArrayOfReportHistorySnapshot

• ListRoles ( SecurityScope As SecurityScopeEnum ) As ArrayOfRole

• ListScheduledReports ( ScheduleID As string ) As ArrayOfCatalogItem

• ListSchedules ( ) As ArrayOfSchedule

• ListSecureMethods ( ) As ArrayOfString

• ListSubscriptions ( Report As string ,  Owner As string ) As ArrayOfSubscription

• ListSubscriptionsUsingDataSource ( DataSource As string ) As ArrayOfSubscription

• ListTasks ( SecurityScope As SecurityScopeEnum ) As ArrayOfTask

• Logoff ( )

• LogonUser ( userName As string ,  password As string ,  authority As string )

• MoveItem ( Item As string ,  Target As string )

• PauseSchedule ( ScheduleID As string )

• PrepareQuery ( DataSource As DataSource ,  DataSet As DataSetDefinition ) As DataSetDefinition

• RegenerateModel ( Model As string ) As ArrayOfWarning

• RemoveAllModelItemPolicies ( Model As string )

• ResumeSchedule ( ScheduleID As string )

• SetCacheOptions ( Report As string ,  CacheReport As boolean )

• SetDataDrivenSubscriptionProperties ( DataDrivenSubscriptionID As string ,  ExtensionSettings As ExtensionSettings ,  DataRetrievalPlan As DataRetrievalPlan ,  Description As string ,  EventType As string ,  MatchData As string ,  Parameters As ArrayOfParameterValueOrFieldReference )

• SetDataSourceContents ( DataSource As string ,  Definition As DataSourceDefinition )

• SetExecutionOptions ( Report As string ,  ExecutionSetting As ExecutionSettingEnum )

• SetItemDataSources ( Item As string ,  DataSources As ArrayOfDataSource )

• SetModelDefinition ( Model As string ,  Definition As base64Binary ) As ArrayOfWarning

• SetModelDrillthroughReports ( Model As string ,  ModelItemID As string ,  Reports As ArrayOfModelDrillthroughReport )

• SetModelItemPolicies ( Model As string ,  ModelItemID As string ,  Policies As ArrayOfPolicy )

• SetPolicies ( Item As string ,  Policies As ArrayOfPolicy )

• SetProperties ( Item As string ,  Properties As ArrayOfProperty )

• SetReportDefinition ( Report As string ,  Definition As base64Binary ) As ArrayOfWarning

• SetReportHistoryLimit ( Report As string ,  UseSystem As boolean ,  HistoryLimit As int )

• SetReportHistoryOptions ( Report As string ,  EnableManualSnapshotCreation As boolean ,  KeepExecutionSnapshots As boolean )

• SetReportLink ( Report As string ,  Link As string )

• SetReportParameters ( Report As string ,  Parameters As ArrayOfReportParameter )

• SetResourceContents ( Resource As string ,  Contents As base64Binary ,  MimeType As string )

• SetRoleProperties ( Name As string ,  Description As string ,  Tasks As ArrayOfTask )

• SetScheduleProperties ( Name As string ,  ScheduleID As string ,  ScheduleDefinition As ScheduleDefinition )

• SetSubscriptionProperties ( SubscriptionID As string ,  ExtensionSettings As ExtensionSettings ,  Description As string ,  EventType As string ,  MatchData As string ,  Parameters As ArrayOfParameterValue )

• SetSystemPolicies ( Policies As ArrayOfPolicy )

• SetSystemProperties ( Properties As ArrayOfProperty )

• UpdateReportExecutionSnapshot ( Report As string )

• ValidateExtensionSettings ( Extension As string ,  ParameterValues As ArrayOfParameterValueOrFieldReference ) As ArrayOfExtensionParameter

 

最后我开发的一个自己使用的小工具，如图所示。

该工具可以对一个目录下，所有报表以及数据源进行上传（可以递归子文件夹），同时整个程序是多线程的。