Deploy one Reporting Services Project into multiple Folders on Report Server

Question

Hi all,

I use SQL Server 2008 Reporting Services. I have some reports for our 40 Sales agents - each of them has his own role in the Analysis Services cube so each one can see only his own data. Deploying the reports to one folder on the report server using Windows Authentication works fine as long as no one wants to subsribe to a report. To use subscription each user has to store his data source credentials on the report server. To do so, each one needs to have his own data source in his Users Folder - thats how far I came.

Is the only chance I have, to create 40 different Projects and define in each one another TargetFolder, if I want to deploy the reports at once? Or is there a way to define multiple TargetFolders in one Project? Or at least a way to synchronize the Projects, because I don't want to modify 40 reports for a simple change.

Thanks in Advance

Answer 1

Hi Ernesto

  Pls make use if this thread , it should give you an idea how to do it :
http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/722297e8-1c60-4958-878d-fef8d121dacb

 Cheers
Anand

Answer 2

Anand, in that thread there was a request to you to post details. Can you do so?
====================================================

Hi Anand,

I am glad to hear that you have solved the issue.

By the way, could you please post the solution you used? That will benefit other community members who met the same issue.

Thanks,
Jin Chen

---

Jin Chen - MSFT

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

Answer 3

Hi Anand,

thank you for your reply, but I don't understand your solution.

Maybe some additional information helps you or the others what I want to do:

In the .rptproj file of my Reporting Services Project are different configutations:

<Configurations>

    <Configuration>
      <Name>Sales1</Name>
      <Platform>Win32</Platform>
      <Options>
        <TargetServerURL>http://<server>/ReportServer</TargetServerURL>
        <TargetFolder>Users Folders/Sales User1/My Reports/Sales</TargetFolder>
        <TargetDataSourceFolder>Users Folders/Sales User1/My Reports/Data Sources</TargetDataSourceFolder>
      </Options>
    </Configuration>

    <Configuration>
      <Name>Sales2</Name>
      <Platform>Win32</Platform>
      <Options>
        <TargetServerURL>http://<server>/ReportServer</TargetServerURL>
        <TargetFolder>Users Folders/Sales User2/My Reports/Sales</TargetFolder>
        <TargetDataSourceFolder>Users Folders/Sales User2/My Reports/Data Sources</TargetDataSourceFolder>
      </Options>
    </Configuration>

    <Configuration>
      <Name>Sales3</Name>
      <Platform>Win32</Platform>
      <Options>
        <TargetServerURL>http://<server>/ReportServer</TargetServerURL>
        <TargetFolder>Users Folders/Sales User3/My Reports/Sales</TargetFolder>
        <TargetDataSourceFolder>Users Folders/Sales User3/My Reports/Data Sources</TargetDataSourceFolder>
      </Options>
    </Configuration>

  </Configurations>

In the .sln  file of the Project there are references to these configurations (in this case, Sales2 is active).

Global
 GlobalSection(SolutionConfigurationPlatforms) = preSolution
  Debug|Default = Debug|Default
  DebugLocal|Default = DebugLocal|Default
  Release|Default = Release|Default
 EndGlobalSection
 GlobalSection(ProjectConfigurationPlatforms) = postSolution
  {B8A951E7-6CAD-4149-B3D4-59308F1A2A80}.Debug|Default.ActiveCfg = Sales2
  {B8A951E7-6CAD-4149-B3D4-59308F1A2A80}.DebugLocal|Default.ActiveCfg = Sales2
  {B8A951E7-6CAD-4149-B3D4-59308F1A2A80}.DebugLocal|Default.Build.0 = Sales1
  {B8A951E7-6CAD-4149-B3D4-59308F1A2A80}.Release|Default.ActiveCfg = Sales1
  {B8A951E7-6CAD-4149-B3D4-59308F1A2A80}.Release|Default.Build.0 = Sales1
  {B8A951E7-6CAD-4149-B3D4-59308F1A2A80}.Release|Default.Deploy.0 = Sales2
 EndGlobalSection
 GlobalSection(SolutionProperties) = preSolution
  HideSolutionNode = FALSE
 EndGlobalSection
EndGlobal

I'd like to activate more than one configuration.

Thanks

Answer 4

Ok Ernesto ,

   I have given a sincere attempt to put together a sample .rss file [because I had to seperate .rss file to serve as a sample for everyone in SSRS forum] and its rptProj file  which are as follows , pls follow the comments in the code and the logic behind this code is as follows : 

    INPUTS TO .RSS FILE :

    1.  Accepts the "rptProjFilePath" using -v switch of the rs.exe [So that the code knows from where your rsproj file is coming from] 
  
    2.  Accepts the "dbServerName" using -v switch [This is an assumption that all my Datasources are targeting the same DB server ,you might have to write additional code to handle your situation here].

    3. Accepts the "sourceReportFolder" using -v switch [So you tell the code from where your .rpt files for deployment is coming from] 

    4. Optionally , if you wish to control the target folders for your reports and datasources from outside the .rss file [send values to reportsPath,dataSourcesPath from -v switch, but handles the population of these variables] 

   EXPLANATION OF MAIN() SUBPROCEDURE : 

   1. RetrieveAllInformationFromRptProjFile() Method first accesses the rptProj files reads the Datasources,Reports,Configuration of your choice in to  their respective local variables as strings. 

  2. First assigns reportsPath and dataSourcesPath using the Configurations Hashtable retrieved in step1 [Script assigns the values if they weren't sent using -v]

 3.  Loops through the DataSources Arraylist retrieved in step1 then deploys all datasources 

 4. Loops through all the Reports Arraylist retrieved in step1 then deploys all report items to the target server and then re-assigns the Datasource reference back to the report [Assumption made is all my datasources that are to deployed are shared datasources] .

With all this pls read the notes I have mentioned in the NOTES section of the .rss file .

My apologies if this script throws any syntax/compile errors do let me know , as the intention behind the script is to show how to read .rptproj file and then deploy report items using that file in an automated manner. I hope this helps many of them like me/you who searched everywhere on internet for finding an automating script for SSRS Reports deployment .

BELOW IS THE .RSS FILE :

'=====================================================================
'  File:      PublishSalesReports.rss
'
'  Summary:  Demonstrates a script that can be used with RS.exe to 
'	     publish the sample reports that ship with Reporting Services.
' 
'  NOTES : In rs.exe , please use -v switch to pass inputs to your public variables of your script [dbServerName ,sourceReportFolder,rptProjFilePath] rest of the
'  public variables are handled in code but can also be optionally passed using -v switch
'  
'  Pls note that this source code below is only a sample , so for simplicity sake there are HARDCODED values 
'  and you will find comments that warn you of these HARDCODINGS. So in those situations a person who intends 
'  to use this .rss file might have to modify those HARDCODINGS to suit there respective case.
'  <br/>'  Note that the connection string while creating datasources might have to be modified to fit your case   <br/>' 
'  This script is also not the optimized when it comes to handling .NET Exceptions[ NullReference, TypeCastExceptions], boxing/unboxing 
'  pls feel free include checks for these exceptions whereever appropriate 
'
'  This .rss file comes with an sample rptProj file called [Sample.rptProj] you can use it in conjunction when you read this code for better understanding 
'
'  There is no support for this code and is only a suggested way of automating Reportserver deployment triggered by a rptProj 
' File  -Anand.R 
'=====================================================================*/

Dim definition As [Byte]() = Nothing
Dim warnings As Warning() = Nothing

'Target Folder on Report Server where reports will be deployed, the code handles the population of this variable or can also be passed in -v  
Public  reportsPath As String 

'Source folder Path that contains all the .rdl files to be deployed, to passed be using -v switch
Public sourceReportFolder As String  

'Database Server Name for a Datasource , to be passed using -v switch 
Public dbServerName as string  

'Target Folder on Report Server where Datasources will be deployed , the code handles the population of this variable or can also be passed in -v  
Public dataSourcesPath as String

'Physical Path of rptProjFile, to be passed using -v switch
Public rptProjFilePath as string  

Public Sub Main()

    rs.Credentials = System.Net.CredentialCache.DefaultCredentials
    Dim name As String
    Dim ds as System.Collections.ArrayList 
    Dim rpts as System.Collections.ArrayList
    Dim configs as System.Collections.Hashtable    

    'Create the parent folder - NOTE THE FOLLOWING LINES ARE COMMENTED , BUT CODE RETAINED TO SHOW HOW FOLDERS ARE TO BE CREATED AT RUNTIME ON REPORTSERVER
    'PLS REPLACE THESE COMMENTED LINES WITH CHECKS AND ERROR HANDLING THAT SUITS YOUR CASE.
    ' Try
        'rs.CreateFolder(reportPath, "/", Nothing)
        'Console.WriteLine("Parent folder created: {0}", reportPath)
   ' Catch e As Exception
        'Console.WriteLine(e.Message)
    'End Try

    'Retrieves all of stuff from rptProj File - This where the reading the rptProjfile happens
     RetrieveAllInformationFromRptProjFile(ds,rpts,configs)

      'Assign TargetFolders for Reports and Datasources 
     If string.IsNullOrEmpty(reportsPath) Then       
      reportsPath = configs["TargetFolder"]
     End If 
     
     If string.IsNullOrEmpty(dataSourcesPath) Then 
      dataSourcesPath=configs["TargetDataSourceFolder"]
     End If 

    'Create the shared data source
    For Each item As Object in ds
      CreateSampleDataSource(Cstr(item))
    Next
    
   
    'Loop through each report object
    For Each item As Object in rpts
      PublishReport(Cstr(item))
      'WARNING : HARDCODE for the sake of simplicity , I am assuming to take only the first DataSource from the Arraylist ds .
      ' PLS MODIFY IT TO SUIT YOUR CASE
      SetDataSourceReference(Cstr(item),Cstr(ds[0]))
    Next
    

  
End Sub

Public Sub RetrieveAllInformationFromRptProjFile(ByRef dataSources as System.Collections.ArrayList ,ByRef reports as System.Collections.ArrayList, ByRef configs as System.Collections.Hashtable)

'Reads DataSources from rptProjFile
dataSources= GetAllDataSources(rptProjFilePath) 

'Reads Reports from rptProjFile
reports = GetAllReports(rptProjFilePath)

'Reads Configs from rptProjFile
'WARNING : I have HARDCODED a CONFIGURATION NAME , PLS REPLACE "DebugLocal" WITH A VALUE APPROPRIATE TO YOUR CASE THIS CONFIGURATION IS PRESENT IN Sample.rptProj
configs = GetSpecificConfiguration(rptProjFilePath, "DebugLocal");


End Sub

Public Sub CreateSampleDataSource(ByVal dsName as string)
    Dim name As String = dsName.Substring(0,dsName.IndexOf("."))
    Dim actualFolder As String = dataSourcesPath

    

    'Define the data source definition.
    Dim definition As New DataSourceDefinition()
    definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
    definition.ConnectString = "data source="+dbServerName+";initial catalog=TestDB;Integrated Security=SSPI"
    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 = True

Try
    rs.CreateDataSource(name, actualFolder, True, definition, Nothing)
    Console.WriteLine("Deployed Datasource "+name+" to "+actualFolder)
    Console.WriteLine("----------------")
    Console.WriteLine()
    
Catch e As Exception
    Console.WriteLine(e.Message+" - Deploy of Datasource  failed " )
    throw new Exception(e.Message);
End Try
    
End Sub 

Public Sub SetDataSourceReference(ByVal reportName as string,ByVal dsName as string)

  Dim reference As New DataSourceReference()
      reference.Reference = dataSourcesPath+"/"+dsName.Substring(0,dsName.IndexOf("."))
      Dim dataSources(0) As DataSource
      Dim ds As New DataSource()
      ds.Item = CType(reference, DataSourceReference)
      ds.Name = dsName
      dataSources(0) = ds
      
      Try 
        rs.SetItemDataSources(reportPath+"/"+reportName , dataSources)
        Console.WriteLine("Correcting WARNING - Assigning DataSource to "+reportName)
        Console.WriteLine(" ------------------")
        Console.Writeline()
      Catch e as Exception 
       Console.WriteLine("EXCEPTION : "+e.Message)
       throw new Exception(e.Message);
      End Try


End Sub

' Publishes a report with file extension [.rdl] to a target folder of Report Server
Public Sub PublishReport(ByVal reportName As String)
    Try
        Dim stream As FileStream = File.OpenRead(sourceReportFolder + reportName )
        definition = New [Byte](stream.Length-1) {}
        stream.Read(definition, 0, CInt(stream.Length))
        stream.Close()

    Catch e As IOException
        Console.WriteLine(e.Message)
        throw new Exception(e.Message);
    End Try

    Try
        warnings = rs.CreateReport(reportName, reportPath,True, definition, Nothing)

        If Not (warnings Is Nothing) Then
            Dim warning As Warning
            For Each warning In warnings
                Console.WriteLine("WARNING :"+warning.Message)
            Next warning

        Else
            Console.WriteLine("Report: {0} published successfully with no warnings", reportName)
        End If

    Catch e As Exception
        Console.WriteLine("EXCEPTION :"+e.Message)
        throw new Exception(e.Message);
    End Try
End Sub 

'This Method reads the rptProj File and returns all the Reports in a Arraylist 
Public Function GetAllReports(ByVal rptProjPath As String) As System.Collections.ArrayList 
        Dim rpts As System.Collections.ArrayList = Nothing 
        Dim projFile As System.Xml.XmlDocument = RetrieveDocument(rptProjPath) 
        If projFile IsNot Nothing Then 
            rpts = New System.Collections.ArrayList() 
            Dim dsNodes As System.Xml.XmlNodeList = projFile.SelectNodes("Project/Reports/ProjectItem") 
            For Each item As System.Xml.XmlNode In dsNodes 
                    
                rpts.Add((item.SelectSingleNode("Name")).InnerText) 
            Next 
        Else 
            New System.IO.FileNotFoundException("Invalid Path to rptProjFile, file does not exists") 
        End If 
        Return rpts 
    End Function 
    
    'This Method reads the rptProj File and returns all the DataSources in a ArrayList
    Public Function GetAllDataSources(ByVal rptProjPath As String) As System.Collections.ArrayList 
        Dim ds As System.Collections.ArrayList = Nothing 
        Dim projFile As System.Xml.XmlDocument = RetrieveDocument(rptProjPath) 
        If projFile IsNot Nothing Then 
            ds = New System.Collections.ArrayList() 
            Dim dsNodes As System.Xml.XmlNodeList = projFile.SelectNodes("Project/DataSources/ProjectItem") 
            For Each item As System.Xml.XmlNode In dsNodes 
                ds.Add((item.SelectSingleNode("Name")).InnerText) 
            Next 
        Else 
            New System.IO.FileNotFoundException("Invalid Path to rptProjFile, file does not exists") 
        End If 
        Return ds 
    End Function 
    
    'This Method reads the rptProj file and returns the specific Configuration mentioned in the <NAME> tag
    Public Function GetSpecificConfiguration(ByVal rptProjPath As String, ByVal name As String) As System.Collections.Hashtable 
        Dim configs As System.Collections.Hashtable = Nothing 
        Dim projFile As System.Xml.XmlDocument = RetrieveDocument(rptProjPath) 
        If projFile IsNot Nothing Then 
            configs = New System.Collections.Hashtable() 
            If Not String.IsNullOrEmpty(name) Then 
                Dim dsNodes As System.Xml.XmlNodeList = projFile.SelectNodes("Project/Configurations/Configuration[Name='" & name & "']") 
                For Each item As System.Xml.XmlNode In dsNodes 
                    configs.Add("TargetFolder", (item.SelectSingleNode("Options/TargetFolder")).InnerText) 
                    configs.Add("TargetDataSourceFolder", (item.SelectSingleNode("Options/TargetDataSourceFolder")).InnerText) 
                    Exit For 
                Next 
            Else 
                Throw New System.Exception("Empty Parameter GetSpecificConfiguration(), cannot be empty") 
            End If 
        Else 
            Throw New System.IO.FileNotFoundException("Invalid Path to rptProjFile, file does not exists") 
        End If 
        Return configs 
    End Function 
    
    'This Method returns the XmlDocument of the rptProjfile 
    Public Function RetrieveDocument(ByVal rptProjPath As String) As System.Xml.XmlDocument 
        If IsValidFile(rptProjPath) Then 
            Dim fs As New System.IO.FileStream(rptProjPath, System.IO.FileMode.Open, System.IO.FileAccess.Read) 
            Dim rules As New System.Xml.XmlReaderSettings() 
            rules.CloseInput = True 
            Dim doc As System.Xml.XmlReader = System.Xml.XmlReader.Create(fs, rules) 
            Dim xDoc As New System.Xml.XmlDocument() 
            xDoc.Load(doc) 
                
            Return xDoc 
        End If 
        Return Nothing 
    End Function 
    
    'Just a File existence check
    Public Function IsValidFile(ByVal rptProjPath As String) As Boolean 
        If System.IO.File.Exists(rptProjPath) Then 
            Return True 
        End If 
        Return False 
    End Function 

SAMPLE .RPTPROJ FILE :

<?xml version="1.0" encoding="utf-8"?>
<Project xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<State>8L1NvdXJjZUNvbnRyb2xJbmZvPgBLAH...BLAH...BLAH...</State>
  <DataSources>
    <ProjectItem>
      <Name>SampleDS.rds</Name>
      <FullPath>SampleDS.rds</FullPath>
    </ProjectItem>
  </DataSources>
  <Reports>
    <ProjectItem>
     <Name>SalesReport1.rdl</Name>
      <FullPath>SalesReport1.rdl</FullPath>
    </ProjectItem>
    <ProjectItem>
      <Name>SalesReport2.rdl</Name>
      <FullPath>SalesReport2.rdl</FullPath>
    </ProjectItem>
    <ProjectItem>
      <Name>SalesReport3.rdl</Name>
      <FullPath>SalesReport3.rdl</FullPath>
    </ProjectItem>
   </Reports>
  <Configurations>
   <Configuration>
      <Name>Debug</Name>
      <Platform>Win32</Platform>
      <Options>
        <TargetServerURL>http://TestServer:8080/ReportServer_SQL2008</TargetServerURL>
        <TargetFolder>SomeFolder</TargetFolder>
        <TargetDataSourceFolder>SomeFolder/DataSources</TargetDataSourceFolder>
        <StartItem>SalesReport1.rdl</StartItem>
      </Options>
    </Configuration>
    <Configuration>
      <Name>DebugLocal</Name>
      <Platform>Win32</Platform>
      <Options>
        <TargetServerURL>http://MyServer/ReportServer</TargetServerURL>
        <TargetFolder>Reports</TargetFolder>
        <TargetDataSourceFolder>Reports/DataSources</TargetDataSourceFolder>
        <OverwriteDataSources>true</OverwriteDataSources>
      </Options>
    </Configuration>
   </Configurations>
</Project>

Pls note I have duplicated this sample .rpt proj to come close to your situation , by including multiple configurations in the .rss code i have hardcoded "DebugLocal" you might have to replace it with a value like "Sales1" , "Sales2" ,"Sales3" whichever fits your case . So might have to tweak this code here and there to fit your case as this code is FOR REFERENCE PURPOSE ONLY.

Finally the RS Command for this sample should look something similar to this :

rs –i RssFileName.rss -s http://servername/reportserver -v Param1="Value1" -v Param2="Value2" -v Param3="Value3"

I hope this helps !!!

Cheers
Anand R

Answer 5

Hi Anand,

thank you very much for your answer. I'm not sure if I can use it, as I have not so much experience in programming.

Ernesto

Answer 6

Hello Anand,

finally I brought it to work. Thank you very much!

There is only one Problem left, I couldn't solve yet.

It works when I set reportPath = "/Reports" and dataSourcePath="/Data Sources".

Your code tries to retrieve the Report Path from rptProjFile, but the error message says:
"Value of type "System.Collections.Hashtable" cannot be converted to string."
And
"Identifier expected."

The other problem is of the same kind with SetDataSourceReference(Cstr(item),Cstr(ds[0])),

"ds[0]" "Value of type "System.Collections.ArrayList" cannot be converted to string."  

Maybe you or someone else has an idea what I'm doing wrong.

Thanks in advance
Ernesto