none
Uploading ssrs 2012 reports to report server

    Question

  • how to upload ssrs 2012 reports to report server through vb.net program?

    i want to deploy reports on customer report server. I have around 100 reports. so upload one by one will be quite time consuming. How can we write a simple program to upload all reports to report server? And report server is with instance.

    e.g. http://computername/reports_sqlsvr12 (sqlsvr12 - instance name)

    on report server i have created folder "FinRep". so i want to upload all the report to that folder.



    h2007

    Tuesday, March 11, 2014 7:07 AM

Answers

  • Hi h2007,

    In SQL Server Reporting Services (SSRS), we can use the Rs utility to deploy reports to the server. In this method, we must be a local administrator on the report server to use the Rs utility. Please refer to the following script:

    Dim definition As [Byte]() = Nothing
    Dim warnings As Warning() = Nothing
    Dim destinationFolder As String = "Deploy Reports"
    Dim reportsTargetPath As String = "/" + destinationFolder
    Dim reportsSourcePath As String = "C:\DemoScript"
    Dim datasourceName As String = "DataSource1"
    
    Public Sub Main()
    Try
        rs.Credentials = System.Net.CredentialCache.DefaultCredentials       
          
        If rs.GetItemType(reportsTargetPath) = ItemTypeEnum.Folder Then
            rs.DeleteItem(reportsTargetPath)
        End If
             
        rs.CreateFolder(destinationFolder, "/", Nothing)                
        Console.WriteLine("Parent folder {0} created successfully.", destinationFolder)
            
        CreateReportDataSource(datasourceName, "SQL", "Data Source=.;Initial Catalog=AdventureWorksDW2012")
     
      PublishReport("Report1")
      PublishReport("Report2")
      PublishReport("Report3")
        
        Console.WriteLine("Tasks completed successfully.")
     
    Catch  ex As Exception
       Throw ex
    End Try
    End Sub
    
    Public Sub CreateReportDataSource(name As String, extension As String, connectionString As String)
        Dim definition As New DataSourceDefinition()
        definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
        definition.ConnectString = connectionString
        definition.Enabled = True
        definition.Extension = extension
        
        Try
            rs.CreateDataSource(name, reportsTargetPath, False, definition, Nothing)    
        Catch e As Exception
            Console.WriteLine("ERROR creating data source: " + name)
            Throw e
        End Try
    End Sub
    
    Public Sub PublishReport(ByVal reportName As String) 
    Try
        Console.WriteLine("Deploying report '{0}'", reportName)
        Dim stream As FileStream = File.OpenRead(reportsSourcePath + "\" + reportName + ".rdl")
        definition = New [Byte](stream.Length-1) {}
        stream.Read(definition, 0, CInt(stream.Length))
        stream.Close()
     
        rs.CreateReport(reportName, reportsTargetPath, False, definition, Nothing)  
        Console.WriteLine("Report: {0} published successfully.", reportName)       
    
        SetDatasource(reportName)
        
    Catch e As Exception
        Console.WriteLine("ERROR while Publishing report: " + reportName) 
        Throw e
    End Try
    End Sub
    
    Public Sub SetDatasource(ByVal reportName As String)
    Try
    
        Console.WriteLine("Setting datasource to report '{0}'.", reportName)       
    
        Dim reference As New DataSourceReference()
        reference.Reference = reportsTargetPath + "/" + datasourceName
        Dim source As New DataSource()
        source.Item = reference 
        source.Name = datasourceName
        Dim sources As DataSource() = New DataSource(0) {}
        sources(0) = source
         
        rs.SetItemDataSources(reportsTargetPath + "/" + reportName, sources)
    
    Catch e As Exception
        Console.WriteLine("Error setting datasource to report '{0}'", reportName)
        Throw e
    End Try
    End Sub
    

    There is a document about SSRS Deploying Reports Using Rs Utility, you can refer to it.
    http://simplesqlserver.wordpress.com/2013/06/17/ssrs-deploying-reports-using-rs-utility-2/

    Regards,
    Alisa Tang


    Alisa Tang
    TechNet Community Support

    Thursday, March 13, 2014 2:10 AM
    Moderator

All replies

  • Hi h2007,

    In SQL Server Reporting Services (SSRS), we can use the Rs utility to deploy reports to the server. In this method, we must be a local administrator on the report server to use the Rs utility. Please refer to the following script:

    Dim definition As [Byte]() = Nothing
    Dim warnings As Warning() = Nothing
    Dim destinationFolder As String = "Deploy Reports"
    Dim reportsTargetPath As String = "/" + destinationFolder
    Dim reportsSourcePath As String = "C:\DemoScript"
    Dim datasourceName As String = "DataSource1"
    
    Public Sub Main()
    Try
        rs.Credentials = System.Net.CredentialCache.DefaultCredentials       
          
        If rs.GetItemType(reportsTargetPath) = ItemTypeEnum.Folder Then
            rs.DeleteItem(reportsTargetPath)
        End If
             
        rs.CreateFolder(destinationFolder, "/", Nothing)                
        Console.WriteLine("Parent folder {0} created successfully.", destinationFolder)
            
        CreateReportDataSource(datasourceName, "SQL", "Data Source=.;Initial Catalog=AdventureWorksDW2012")
     
      PublishReport("Report1")
      PublishReport("Report2")
      PublishReport("Report3")
        
        Console.WriteLine("Tasks completed successfully.")
     
    Catch  ex As Exception
       Throw ex
    End Try
    End Sub
    
    Public Sub CreateReportDataSource(name As String, extension As String, connectionString As String)
        Dim definition As New DataSourceDefinition()
        definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
        definition.ConnectString = connectionString
        definition.Enabled = True
        definition.Extension = extension
        
        Try
            rs.CreateDataSource(name, reportsTargetPath, False, definition, Nothing)    
        Catch e As Exception
            Console.WriteLine("ERROR creating data source: " + name)
            Throw e
        End Try
    End Sub
    
    Public Sub PublishReport(ByVal reportName As String) 
    Try
        Console.WriteLine("Deploying report '{0}'", reportName)
        Dim stream As FileStream = File.OpenRead(reportsSourcePath + "\" + reportName + ".rdl")
        definition = New [Byte](stream.Length-1) {}
        stream.Read(definition, 0, CInt(stream.Length))
        stream.Close()
     
        rs.CreateReport(reportName, reportsTargetPath, False, definition, Nothing)  
        Console.WriteLine("Report: {0} published successfully.", reportName)       
    
        SetDatasource(reportName)
        
    Catch e As Exception
        Console.WriteLine("ERROR while Publishing report: " + reportName) 
        Throw e
    End Try
    End Sub
    
    Public Sub SetDatasource(ByVal reportName As String)
    Try
    
        Console.WriteLine("Setting datasource to report '{0}'.", reportName)       
    
        Dim reference As New DataSourceReference()
        reference.Reference = reportsTargetPath + "/" + datasourceName
        Dim source As New DataSource()
        source.Item = reference 
        source.Name = datasourceName
        Dim sources As DataSource() = New DataSource(0) {}
        sources(0) = source
         
        rs.SetItemDataSources(reportsTargetPath + "/" + reportName, sources)
    
    Catch e As Exception
        Console.WriteLine("Error setting datasource to report '{0}'", reportName)
        Throw e
    End Try
    End Sub
    

    There is a document about SSRS Deploying Reports Using Rs Utility, you can refer to it.
    http://simplesqlserver.wordpress.com/2013/06/17/ssrs-deploying-reports-using-rs-utility-2/

    Regards,
    Alisa Tang


    Alisa Tang
    TechNet Community Support

    Thursday, March 13, 2014 2:10 AM
    Moderator
  • we have developed one program to upload all the SS reports to report server in vb.net,

    which works fine but for without any instanced sql server.

    For e.g. if i have only one instance of sql server on my pc,

    if my PC name is MYPC1 so url will be http://MTPC1/reports            - for this works perfect (works perfect for Sql Server 2012 also)

    --------------------------

    But if i have instanced sql server say SQL12 on my PC then url will be 

    http://MYPC1/reports_SQL12          - then its not working.

    We have used SQL server 2005 web service to do this - http://localhost/ReportServer/ReportService2005.asmx

    My program code is below,

    Imports System.IO
    Imports System
    Imports installStdRep.ReportService2005
    Imports System.Web.Services
    Imports System.Net
    
    
    
    Public Class Form1
    
        Dim myReportService As New ReportService2005.ReportingService2005
    
        Dim myCatalogItems As ReportService2005.CatalogItem()
    
    
    '---------------
    
        Private Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click
            Try
                Me.Cursor = Cursors.WaitCursor
                'Create Folder
                Dim mySearchConditions(0) As ReportService2005.SearchCondition
                mySearchConditions(0) = New ReportService2005.SearchCondition
                mySearchConditions(0).Name = "Name"
                mySearchConditions(0).Value = "/CREP"
    
                lblStatus.Text = "Creating Report folder.." 
    
                Pause(1)
                Try
                    myReportService.CreateFolder("CREP", "/", Nothing)
                Catch ex As Exception
                End Try
    
    
                Dim appPath As String = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location)
                'Add Reports
                Dim parentfolder As System.IO.Directory
                
                Dim folderPath As String = appPath & "\REP"
                Dim parentPath As String = "/CREP"
    
                Dim reports As String() = parentfolder.GetFiles(folderPath, "*.rdl")
                Dim report As String
    
                Dim cre As New System.Net.NetworkCredential(username, password)
                
                myReportService.Credentials = cre
    
                'Each report in the array will be published to the same folder.
                For Each report In reports
                    DeleteReport(report, parentPath)
                    PublishReport(report, parentPath)
                Next
                lblStatus.Text = ""
                Me.Cursor = Cursors.Default
                MessageBox.Show("Setup completed.", "Setup Standard Report", MessageBoxButtons.OK)
                Me.Close()
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.DefaultButton1, "Error")
                Me.Cursor = Cursors.Default
            End Try
        End Sub
    
    '-----------------------------------------
    
    
    
        Public Sub PublishReport(ByVal reportPath As String, ByVal parentPath As String)
    
            Dim definition As [Byte]() = Nothing
            Dim warnings As ReportService2005.Warning() = Nothing
            Dim warning As ReportService2005.Warning
    
            Try
                Dim stream As FileStream = File.OpenRead(reportPath)
                definition = New [Byte](stream.Length) {}
                stream.Read(definition, 0, CInt(stream.Length))
                stream.Close()
    
            Catch e As Exception
                Throw New Exception(e.Message)
            End Try
    
            Try
                'Parse the report path to separate the report name from its folder path
                Dim reportPieces As String() = Split(reportPath, "\")
                Dim reportName As String = reportPieces.GetValue(reportPieces.Length - 1)
                reportName = reportName.Substring(0, reportName.Length - 4)
                lblStatus.Text = "Copying " & reportName & "..."
                
                'Publish the report to Report Server using the specified parentPath
                'but do not overwrite an existing report.
                'For production purposes, you need to test whether report exists first.
                warnings = myReportService.CreateReport(reportName, parentPath, False, definition, Nothing)
                If Not (warnings Is Nothing) Then
                    For Each warning In warnings
                        Throw New Exception(warning.Message)
                    Next warning
                    
                End If
    
            Catch e As Exception
                Throw New Exception(e.Message)
            End Try
        End Sub 'PublishReport
    
    
    '------------------
    
    
        Public Sub DeleteReport(ByVal reportPath As String, ByVal parentPath As String)
            Try
                'Parse the report path to separate the report name from its folder path
                Dim reportPieces As String() = Split(reportPath, "\")
                Dim reportName As String = reportPieces.GetValue(reportPieces.Length - 1)
                reportName = reportName.Substring(0, reportName.Length - 4)
    
                Dim mySearchConditions(0) As ReportService2005.SearchCondition
                mySearchConditions(0) = New ReportService2005.SearchCondition
                mySearchConditions(0).Name = "Name"
                mySearchConditions(0).Value = reportName
    
                myCatalogItems = myReportService.FindItems("/", Nothing, mySearchConditions)
    
                If myCatalogItems.Length <> 0 Then
                    myReportService.DeleteItem(parentPath & "/" & reportName)
                End If
    
            Catch e As Exception
                Throw New Exception(e.Message)
            End Try
        End Sub 'DeleteReport
    
    '-----------------
    
    
        Public Function GetFolderPath(ByVal currentPath As String) As String
            'Parse the item path to get the folder hierarchy
            Dim pathPieces As String() = Split(currentPath, "/")
            Dim parentLength As Integer = pathPieces.Length - 1
            Dim parentPath(parentLength) As String
    
            Dim i As Integer
            For i = 0 To parentLength
                parentPath(i) = pathPieces(i)
            Next i
            If parentPath.Length = 1 Then
                Return "/"
            Else
                Return String.Join("/", parentPath)
            End If
        End Function 'GetFolderPath
    
        Public Sub New()
    
            ' This call is required by the Windows Form Designer.
            InitializeComponent()
            myReportService.Credentials = System.Net.CredentialCache.DefaultCredentials
            ' Add any initialization after the InitializeComponent() call.
    
        End Sub
    
        
    End Class


    h2007


    • Edited by h2007 Wednesday, March 26, 2014 2:17 AM
    Wednesday, March 26, 2014 2:16 AM