SQL Server Reporting Service - 命令行部署脚本介绍

首先应公司老总要求写了英文版: http://www.cnblogs.com/WilsonWu/archive/2009/02/19/1394200.html

英文不好别笑话.

进入正题!

使用 SQL Server Reporting Service 的朋友应该都会遇到与此类似的问题, 尤其是在产品中, 比如我们有若干个报表形成的一个产品, 这些报表是需要用户部署在报表服务器上的才能使用的, 我们可以只给用户 RDL 报表文件和一个文档教程, 让他们自己上传, 自己配置数据源等等, 但是这样也许令人感到这个产品太不人性化, 而 SQL Server Reporting Service 也想到了这点, 它给我们提供了一个名为 RS.exe 的小工具, 这个工具就可以帮助我们以命令行的形式做一些有关 Reporting Service 的工作, 现在先介绍一下 RS.exe 工具中的主要功能:

首先使用-?命令获取一些帮助信息:

D:\RS>rs -?
Microsoft (R) Reporting Services RS
Version 10.0.1600.22 ((SQL_PreRelease).080709-1414 ) x86
Executes script file contents against the specified Report Server.
RS -i inputfile -s serverURL [-u username] [-p password]
   [-l timeout] [-b] [-e endpoint] [-v var=value] [-t]

        -i  inputfile   Script file to execute
        -s  serverURL   URL (including server and vroot) to execute
                        script against.
        -u  username    User name used to log in to the server.
        -p  password    Password used to log in to the server.
        -e  endpoint    Web service endpoint to use with the script.
                        Options are:
                        Exec2005 - The ReportExecution2005 endpoint
                        Mgmt2005 - The ReportService2005 endpoint
        -l  timeout     Number of seconds before the connection to the
                        server times out. Default is 60 seconds and 0 is
                        infinite time out.
        -b              Run as a batch and rollback if commands fail
        -v  var=value   Variables and values to pass to the script
        -t  trace       Include trace information in error message

其实看上去也不难, 其中重点介绍一个参数, 大家可以看到-i这个参数中需要传输一个脚本文件给该工具, 其实这个脚本文件也是一般的VB.NET语法写的, 在网上查了一些资料, SQL Server 安装包中没有默认包含脚本实例, 所以我们需要自己下载一下, 大家可以通过: http://www.codeplex.com/MSFTRSProdSamples 这个网站下载并安装, 其中会下载到一个安装包, 它包含了所有 SQL Server 中技术的实例, 安装完成后, 我们进入C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services 目录, 然后找到一个名为 "Script Samples" 的目录, 这个目录中有若干个RSS为后缀的文件, 这些文件就是脚本文件里, 打开一个名为PublishSampleReports.rss看一下, 内容如下:

'=============================================================================
'  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