我需要创建一个可重复的过程来部署SQL Server Reporting Services报表。我不赞成使用Visual Studio和/或者Business Development Studio来执行此操作。脚本部署的rs.exe方法似乎也很笨拙。是否有人能以一种非常优雅的方式来部署报告。这里的关键是我希望流程完全自动化。

解决方案

我知道我们说我们不赞成Business Development Studio这样做，但是我发现内置工具非常可靠且易于使用。

好吧，不是很优雅。我们创建了自己的工具，该工具使用了reportservices2005 Web服务。我们发现这是获得我们想要的东西的最可靠的方法。

其实并没有那么困难，可以让我们扩展它来执行其他操作，例如根据需要创建数据源和文件夹。

我们是否研究过任何持续集成解决方案，例如CruiseControl.NET？如果我们能够使用rs.exe部署报表，则可以在CruiseControl中设置一个自动过程，以在计时器上或者在修改报表时生成和部署报表。

我们使用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

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

Reporting Services Scripter is a .NET
  Windows Forms application that enables
  scripting and transfer of all
  Microsoft SQL Server Reporting
  Services catalog items to aid in
  transferring them from one server to
  another. It can also be used to easily
  move items on mass from one Reporting
  Services folder to another on the same
  server. Depending on the scripting
  options chosen, Reporting Services
  Scripter can also transfer all catalog
  item properties such as Descriptions,
  History options, Execution options
  (including report specific and shared
  schedules), Subscriptions (normal and
  data driven) and server side report
  parameters.

我使用了@David提供的脚本，但是我不得不添加一些代码(我将其输入为答案，因为这对于注释来说太长了。

问题是：如果报表定义中已经有添加到报表的"共享数据源"，则此数据源永远不会与脚本中创建的数据源相同。

从" CreateReport"方法发出的警告中也可以明显看出这一点：

The data set '' refers to the shared data source '', which is not published on the report server.

因此，之后必须显式设置数据源。我进行了以下代码更改：

我添加了一个全局变量：

Dim dataSourceRefs(0) As DataSource

在CreateDataSource方法的末尾，将填充该变量：

Dim dsr As New DataSourceReference
dsr.Reference = "/" + parentFolder + "/" + db
Dim ds As New DataSource
ds.Item = CType(dsr, DataSourceDefinitionOrReference)
ds.Name = db
dataSourceRefs(0) = ds

并在PublishReport方法中，显式设置该数据源(在调用CreateReport之后)：

rs.SetItemDataSources(targetFolder + "/" + reportName.Substring(0, reportName.Length - 4), dataSourceRefs)

请注意，此最后一次调用仅是RS 2005或者更高版本。如果要将报告加载到RS 2000服务器上，则必须改为使用SetReportDataSources：

rs.SetReportDataSources(targetFolder + "/" + reportName.Substring(0, reportName.Length - 4), dataSourceRefs)