Programmaticall y deploy SQL server 2005 reports and Datasource

[balaki]

Hi,

I am working on a Installer Project, which requires to deploy SQL Server 2005 Reports (.rdl files) and Datasource (.rds file). The datasource is common for all reports. The report files are available in multiple folders (under D:\TestReport) and the .rds file is in a folder called 'Data Sources'. I wrote a VB.Net console application to do the task. I am able to deploy the report files under the specified folders. But I don't know how to deploy the rds file and assign that datasource to the report files.

I have created s datasource based on the input. The datasource is created, but when I browse the reports, the error "The report server cannot process the report. The data source connection information has been deleted. (rsInvalidDataS ourceReference) " appears. Herewith I have attached the code I used. Somebody please help me in this regard.

Thanks,

Bala

Module Module1
    Sub Main(ByVal Arg() As String)
        Try
            'If Arg.Length < 5 Then Exit Sub
            Dim DBServer As String = "Server" 'Arg(0)
            Dim DBName As String = "DBName" 'Arg(1)
            Dim ReportServerURL As String = "http://localhost/ReportServer1/ReportService2005.asmx" 'Arg(2)
            Dim ReportsParentFolder As String = "C:/TestReport" 'Arg(3)
            Dim ReportParentFolderName As String = "DB Reports"  'Arg(4)
 
            Dim RServices As New ReportingService2005.ReportingService2005()
            RServices.Credentials = System.Net.CredentialCache.DefaultCredentials
            RServices.Url = ReportServerURL
            RServices.CreateFolder(ReportParentFolderName, "/", Nothing)
 
            Dim ReportPath As String = ""
            Dim ReportDef() As Byte
            Dim DirInfo As New DirectoryInfo(ReportsParentFolder)
            Dim CurrentFolderName As String = ReportsParentFolder
            Dim CurReportFolderName As String = "/" & ReportParentFolderName
 
            ' Create datasource for the report.
            Dim dDefinition As New ReportingService2005.DataSourceDefinition()
            dDefinition.Extension = "SQL"
            dDefinition.ConnectString = "Data Source=" + DBServer + ";Initial Catalog=" + DBName
            dDefinition.ImpersonateUser = False
            dDefinition.ImpersonateUserSpecified = True
            dDefinition.Prompt = Nothing
            dDefinition.WindowsCredentials = True
            dDefinition.CredentialRetrieval = ReportingService2005.CredentialRetrievalEnum.Store
            dDefinition.Enabled = True
            dDefinition.EnabledSpecified = True
            dDefinition.UserName = ""
            dDefinition.Password = ""
            dDefinition.WindowsCredentials = True
 
            For Each RdlFile As FileSystemInfo In DirInfo.GetFileSystemInfos
                If RdlFile.GetType.Name = "DirectoryInfo" Then ' it is a folder
                    RServices.CreateFolder(RdlFile.Name, "/" & ReportParentFolderName, Nothing)
                    CurrentFolderName = ReportsParentFolder & "/" & RdlFile.Name
                    CurReportFolderName = CurReportFolderName & "/" & RdlFile.Name
 
                    Dim DirInfo1 As New DirectoryInfo(RdlFile.FullName)
                    For Each RdlFile1 As FileSystemInfo In DirInfo1.GetFileSystemInfos
                        If RdlFile1.GetType.Name <> "DirectoryInfo" Then
                            ReportPath = RdlFile1.Name
                            Dim File As New FileStream(CurrentFolderName & "/" & ReportPath, FileMode.Open)
                            ReportDef = New Byte(File.Length) {}
                            File.Read(ReportDef, 0, CInt(File.Length))
                            File.Close()
 
                            If LCase(Mid(RdlFile1.Name, RdlFile1.Name.Length() - 2)) = "rdl" Then
                                RServices.CreateReport(ReportPath.Remove(ReportPath.Length - 4, 4), CurReportFolderName, True, ReportDef, Nothing)
                            End If
                            If LCase(Mid(RdlFile1.Name, RdlFile1.Name.Length() - 2)) = "rds" Then
                                RServices.CreateDataSource("ReportData", "/" & ReportParentFolderName & "/Data Sources", False, dDefinition, Nothing)
                            End If
 
                        End If
                    Next
                Else ' it is a rdl file
                    ReportPath = RdlFile.Name
                    Dim File As New FileStream(CurrentFolderName, FileMode.Open)
                    ReportDef = New Byte(File.Length) {}
                    File.Read(ReportDef, 0, CInt(File.Length))
                    File.Close()
                    RServices.CreateReport(ReportPath.Remove(ReportPath.Length - 4, 4), CurrentFolderName, True, ReportDef, Nothing)
                End If
                CurReportFolderName = "/" & ReportParentFolderName
            Next
 
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
    End Sub
End Module

[kenobewan]

It sounds like the RDL is not set correctly to point to where the datasource is being deployed. You can either change the RDL or call SetItemDatasour ces (RS 2005) or SetReportDataso urces (RS 2000) to set the data source to point to the shared data source. HTH.

[balaki]

Thanks for your response.
I tried using SetItemDatasour ces, but could not succeed. It will be very helpful, if you can provide me a code sample. Thanks in advance.

[kenobewan]

Then show us what you tried. Thanks.