Thursday 26 February 2009

Deploying Reporting Services programmatically - shared data sources

Hello again! After a longish hiatus I'm back posting fairly useful stuff - so I don't lose/forget/have to do it again in another job.

The first useful snippet covers deploying Reporting Services reports using the rs utility on the command line. This will be necessary in situations where you can't deploy directly from BIDS - which to my mind isn't good practice when you are outside of the development environment. Although there are various examples of doing this there aren't many using VB.NET (which you have to use with rs) and none covering my requirements.

As you may know when using the rs utility to deploy (technically create) reports on a Report Server it does not maintain references to shared data sources even when they exist in the same relative position given in the Reporting Services project. This means you have the tricky job of adding those references programatically after using CreateReport to create the reports.

Tricky? Well it is if your reports have multiple different data sources. If you try adding a data source to a report that doesn't know about that data source (ie hasn't got a reference to it in the .rdl) you will get an error. Therefore you have to work out what data sources that report has a reference to and relate those references to the shared data sources with the same name elsewhere in Reporting Services. A complicating factor is that you can't (or at least I couldn't) simply map the data sources obtained from the report to the shared data source location. Which is way I ended up looping around the datasources retrieved from the report definition in the code below. Anyway it works and that's good enough for me...

In order to use the example create an empty .rss file in Notepad and copy and paste the following code and save it as UpdateDataSources.rss.

Public Sub Main()

rs.Credentials = System.Net.CredentialCache.DefaultCredentials

Dim item as CatalogItem
Dim items as CatalogItem()

Try
items=rs.ListChildren(ReportFolder, False)

For Each item in items

If item.Type = 2 'ie Report

Dim dataSources() as DataSource = rs.GetItemDataSources(item.Path)

For Each ds as DataSource in dataSources

Dim sharedDs(0) as DataSource
sharedDs(0)=GetDataSource(SharedDataSourceFolder, ds.Name)

rs.SetItemDataSources(item.Path, sharedDs)
Console.WriteLine("Set " & ds.Name & " datasource for " & item.Path & " report")

Next

end if


Next

Console.WriteLine("Shared data source reference set for {0} reports.", ReportFolder)

Catch e As SoapException
Console.WriteLine(e.Detail.InnerXml.ToString())
End Try

End Sub

Private Function GetDataSource(sharedDataSourcePath as string, dataSourceName as String) as DataSource


Dim reference As New DataSourceReference()
Dim ds As New DataSource

reference.Reference = sharedDataSourcePath & "/" & dataSourceName
ds.Item = CType(reference, DataSourceDefinitionOrReference)

ds.Name = dataSourceName

GetDataSource=ds


End Function


Use it with rs on the command-line as follows:

rs -i UpdateDataSources.rss -v ReportFolder=[Folder Location containing the reports to be updated] -v SharedDataSourceFolder=[Folder location containing the shared data sources].

4 comments:

steve said...

Phil,

Thanks for posting this, you gave me a shove in the right direction with your snippet on datasourcereference. really helped me out, thanks!

Gork68 said...

You state:

"Therefore you have to work out what data sources that report has a reference to and relate those references to the shared data sources with the same name elsewhere in Reporting Services."

Which is my problem... In SSRS 2005 you could use rs.exe to set a shared data source to the report named whatever you want. I have a lot of developers that name their data source anything and then I am tasked with deploying thousands of reports that will all use the same single shared data source that I create on the reporting server. Is there any way to assign the shared data source with rs.exe regardless of name? If not why on GOD's earth would microsoft change this from SSRS 2005... :(

Anonymous said...

Fairly Useful Phil:

Can I upvote you to "Unbelievably Indipensible Phil" ?

This post has saved my job and means my family can eat again.

Please send me read/write details to your bank account so I can write you into my will. No, really... :-)

Best Regards,

V. Grateful (Botswana)

Anonymous said...

Phil,

You saved my day. Very helpful post. Thank you!