SQL Server Reporting Service

1352222

　　上次介绍了 SQL Server Reporting Service 命令行部署报表的基本内容, 利用这些知识我们可以轻松的部署报表, 然而在 TFS 中, 每个项目都有它对应的报表, 这些报表如果要一个个的更新也是件痛苦的事情, 现在我也遇到了这个问题, 针对 TFS 开发了两张报表, 但是如何将这些报表应用到所有项目上呢? 结合之前的部署脚本知识, 我们可以使用下面方法实现:
　　首先建立一个批处理文件ImportWIT.bat, 用来更新某个项目的Work Item定义文件:
　　

@ECHO OFF

CALL "C:\Program Files\Microsoft Visual Studio 9.0\VC\vcvarsall.bat" x86

witimport /t %1 /p %2 /f WorkItemDefinitions\Task.xml
witimport /t %1 /p %2 /f WorkItemDefinitions\Bug.xml
witimport /t %1 /p %2 /f WorkItemDefinitions\Scenario.xml

rem /t %1 /p %2
rem echo Hit any key to continue
rem PAUSE

　　在WorkItemDefinitions目录下将所有对应Work Item定义文件放入:

　　然后建立一个Reports目录, 将所有需要的报表文件放入, 如下图:

　　然后创建一个脚本文件PublishReports.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 = ""
Dim parentPath As String = "/" + parentFolder
Dim filePath As String = "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
    Dim sreader As New System.IO.StreamReader("ProjectInfo.ini")
    Dim pName As String = ""
    pName = sreader.ReadLine()

    PublishReport("SSW Progress Report", pName)
    PublishReport("SSW Release Plan", pName)

End Sub

Public Sub CreateSampleDataSource()Sub CreateSampleDataSource(ByVal name As String, ByVal extension As String, ByVal 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, ByVal projectName 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

        parentPath = "/" + projectName

        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

　　根据上面的代码可以看到需要一个保存项目名的文件, 最后我们就创建一个ProjectInfo.ini文件, 该文件内容留空即可.
　　上述文件都船舰好了之后接着创建一个总体的脚本, 命名为Update.bat, 内容为:

@ECHO OFF

copy nul ProjectInfo.ini /y
echo %2>>ProjectInfo.ini

RS -i "PublishReports.rss" -s "http://%1/ReportServer/"
ImportWIT.bat %1 %2
　　
　　以上就是所有需要创建的内容, 如下:

　　其中RS.EXE文件可以在SQL Server安装目录中找到.
　　接下来我们来执行升级某个Team Project的具体操作:
　　打开CMD, 输入如下命令:

update.bat [TFSServerName] [TeamProjectName]　　
　　这样就可以将一个Team Project中的工作项和报表更新成脚本中提到的内容.
　　完整升级包下载: http://files.iyunv.com/WilsonWu/TFS_2008_SSWTemplateUpdateScript_ver1-1.zip