Tuesday, December 06, 2005

SQL Reporting Services: Deploying Reports To Production

I can not beleive how hard it is to deploy reports to production! Its easy for development. Just tell visual studio where the server is and bang! its done. I spent most of today trying to figure out how to deploy to production without having to manually upload each report through the web interface and then manually linking them to my shared data source.

I tried writing a vb script to do it which I got 90% their but could not link to a shared datasource. Microsofts documentation of the scripting commands you can do ARE GARBAGE! when trying to set a property you need to send a name value pair (which is fine) but there is no docs I could find that say what the strings are that I can pass to it. But I digress.

In the end I found this tool:
http://www.sqldbatips.com/showarticle.asp?ID=62

This person wrote a tool that connects to my development RS and generates a folder of scripts along with a batch file to run them all. I opened the batch file, changed the server to production and a couple of minutes later I was .....happy? yes. that is the word.

For those of you that want to deploy reports with a shared datasource via a manual script (using the rs.exe program to execute it) here is a snip of one of the scripts:


Public Sub Main()
Dim name As String = "Allocation Report"
Dim parent As String = "/MyProgram.ReportStaging"
Dim location As String = "c:\tempreports\Allocation Report.rdl"
Dim overwrite As Boolean = True
Dim reportContents As Byte() = Nothing
Dim warnings As Warning() = Nothing
Dim fullpath As String = parent + "/" + name

'Common CatalogItem properties
Dim descprop As New [Property]
descprop.Name = "Description"
descprop.Value = ""
Dim hiddenprop As New [Property]
hiddenprop.Name = "Hidden"
hiddenprop.Value = "False"

Dim props(1) As [Property]
props(0) = descprop
props(1) = hiddenprop

'Read RDL definition from disk
Try
Dim stream As FileStream = File.OpenRead(location)
reportContents = New [Byte](stream.Length) {}
stream.Read(reportContents, 0, CInt(stream.Length))
stream.Close()

warnings = RS.CreateReport(name, parent, overwrite, reportContents, props)

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", name)
End If

'Set report DataSource references
Dim dataSources(0) As DataSource

Dim dsr0 As New DataSourceReference
dsr0.Reference = "/MyProgram.ReportStaging/dsMyProgram"
Dim ds0 As New DataSource
ds0.Item = CType(dsr0, DataSourceDefinitionOrReference)
ds0.Name="dsMyProgram"
dataSources(0) = ds0


RS.SetReportDataSources(fullpath, dataSources)

Console.Writeline("Report DataSources set successfully")



Catch e As IOException
Console.WriteLine(e.Message)
Catch e As SoapException
Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText + " (" + e.Detail.Item("Message").InnerText + ")")
End Try
End Sub

0 Comments:

Post a Comment

<< Home