Reporting Services部署

Question

我需要创建一个可重复的过程来部署SQL Server Reporting Services报表。我不赞成使用Visual Studio和/或Business Development Studio来做这件事。编写部署脚本的rs.exe方法似乎也相当笨拙。有没有人有一种非常优雅的方式来部署报表。这里的关键是我希望这个过程完全自动化。

Answer 1

我们使用rs.exe，一旦我们开发了脚本，我们就不需要再接触它了，它就可以工作了。

这是源代码(我手动略微修改了它，在没有机会测试的情况下删除了敏感数据，希望我没有破坏任何东西)，它部署了各种语言的子目录中的报告和相关图像。此外，还创建了数据源。

'=====================================================================
'  File:      PublishReports.rss
'
'  Summary: Script that can be used with RS.exe to 
'           publish the reports.
'
'  Rss file spans from beginnig of this comment to end of module
' (except of "End Module").
'=====================================================================

Dim langPaths As String() = {"en", "cs", "pl", "de"}
Dim filePath As String = Environment.CurrentDirectory

Public Sub Main()

    rs.Credentials = System.Net.CredentialCache.DefaultCredentials

    'Create parent folder
    Try
        rs.CreateFolder(parentFolder, "/", Nothing)
        Console.WriteLine("Parent folder created: {0}", parentFolder)
    Catch e As Exception
        Console.WriteLine(e.Message)
    End Try

    PublishLanguagesFromFolder(filePath)

End Sub

Public Sub PublishLanguagesFromFolder(ByVal folder As String)
    Dim Lang As Integer
    Dim langPath As String

    For Lang = langPaths.GetLowerBound(0) To langPaths.GetUpperBound(0)
        langPath = langPaths(Lang)

        'Create the lang folder
        Try
            rs.CreateFolder(langPath, "/" + parentFolder, Nothing)
            Console.WriteLine("Parent lang folder created: {0}", parentFolder + "/" + langPath)
        Catch e As Exception
            Console.WriteLine(e.Message)
        End Try

        'Create the shared data source
        CreateDataSource("/" + parentFolder + "/" + langPath)

        'Publish reports and images
        PublishFolderContents(folder + "\" + langPath, "/" + parentFolder + "/" + langPath)
    Next 'Lang
End Sub

Public Sub CreateDataSource(ByVal targetFolder As String)
    Dim name As String = "data source"

    'Data source definition.
    Dim definition As New DataSourceDefinition
    definition.CredentialRetrieval = CredentialRetrievalEnum.Store
    definition.ConnectString = "data source=" + dbServer + ";initial catalog=" + db
    definition.Enabled = True
    definition.EnabledSpecified = True
    definition.Extension = "SQL"
    definition.ImpersonateUser = False
    definition.ImpersonateUserSpecified = True
    'Use the default prompt string.
    definition.Prompt = Nothing
    definition.WindowsCredentials = False
    'Login information
    definition.UserName = "user"
    definition.Password = "password"

    Try
    'name, folder, overwrite, definition, properties 
        rs.CreateDataSource(name, targetFolder, True, definition, Nothing)
    Catch e As Exception
        Console.WriteLine(e.Message)
    End Try

End Sub

Public Sub PublishFolderContents(ByVal sourceFolder As String, ByVal targetFolder As String)
    Dim di As New DirectoryInfo(sourceFolder)
    Dim fis As FileInfo() = di.GetFiles()
    Dim fi As FileInfo

    Dim fileName As String

    For Each fi In fis
        fileName = fi.Name
        Select Case fileName.Substring(fileName.Length - 4).ToUpper
            Case ".RDL"
                PublishReport(sourceFolder, fileName, targetFolder)
            Case ".JPG", ".JPEG"
                PublishResource(sourceFolder, fileName, "image/jpeg", targetFolder)
            Case ".GIF", ".PNG", ".BMP"
                PublishResource(sourceFolder, fileName, "image/" + fileName.Substring(fileName.Length - 3).ToLower, targetFolder)
        End Select
    Next fi
End Sub

Public Sub PublishReport(ByVal sourceFolder As String, ByVal reportName As String, ByVal targetFolder As String)
    Dim definition As [Byte]() = Nothing
    Dim warnings As Warning() = Nothing

    Try
        Dim stream As FileStream = File.OpenRead(sourceFolder + "\" + reportName)
        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
   'name, folder, overwrite, definition, properties 
        warnings = rs.CreateReport(reportName.Substring(0, reportName.Length - 4), targetFolder, True, 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", targetFolder + "/" + reportName)
        End If
    Catch e As Exception
        Console.WriteLine(e.Message)
    End Try
End Sub

Public Sub PublishResource(ByVal sourceFolder As String, ByVal resourceName As String, ByVal resourceMIME As String, ByVal targetFolder As String)
    Dim definition As [Byte]() = Nothing
    Dim warnings As Warning() = Nothing

    Try
        Dim stream As FileStream = File.OpenRead(sourceFolder + "\" + resourceName)
        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
    'name, folder, overwrite, definition, MIME, properties 
        rs.CreateResource(resourceName, targetFolder, True, definition, resourceMIME, Nothing)
        Console.WriteLine("Resource: {0} with MIME {1} created successfully", targetFolder + "/" + resourceName, resourceMIME)
    Catch e As Exception
        Console.WriteLine(e.Message)
    End Try
End Sub

下面是调用rs.exe的批处理：

SET ReportServer=%1
SET DBServer=%2
SET DBName=%3
SET ReportFolder=%4

rs -i PublishReports.rss -s %ReportServer% -v dbServer="%DBServer%" -v db="%DBName%" -v parentFolder="%ReportFolder%" >PublishReports.log 2>&1

pause

Answer 2

嗯，不是很优雅。我们创建了自己的工具，该工具使用reportingservices2005 web服务。我们发现这是获得我们想要的最可靠的方式。

这并不是很难，并且可以让您扩展它来做其他事情，比如根据需要创建数据源和文件夹。

Answer 3

我强烈推荐RSScripter。如概述中所述：

Microsoft Services编写器是一个.NET Windows窗体应用程序，它启用所有Microsoft SQL Server Reporting Services目录项的脚本编写和传输，以帮助将它们从一台服务器传输到另一台服务器。还可以使用它轻松地将大量项从同一服务器上的一个Reporting Services文件夹移动到另一个文件夹。根据所选的脚本选项，Reporting Services脚本编写器还可以传输所有目录项属性，如说明、历史记录选项、执行选项(包括特定于报表的计划和共享计划)、订阅(普通和数据驱动)以及服务器端报表参数。