My QA folks have a problem; development has been working on migrating a bunch of reports from a legacy 3rd-party application to SQL Server Reporting Services. Development has finished their “sprint” (*cough* waterfall), and handed over 52 reports to QA, and told the to load them into their QA server. The problem? The web interface only loads 1 report at a time.
Me, being the DevOps guy that I am, thought “well, that’s just silly; there has to be a way to upload all of the reports at once, rather than clicking on some silly buttons over and over again”. I googled, and the best explanation I found was at this blogpost: https://basic-ssrs.blogspot.com/2015/12/upload-multiple-rdl-files-from-local.html (Kudos; this is a VERY well written walk-through).
Unfortunately, I did exactly what the blog suggested, and ran into two problems. The first? Running the .bat file opened and closed a command window in seconds. No feedback. Nothing. I went back and made a minor change to the .bat file; namely, I added the PAUSE command to the end:
“E:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\rs.exe” -i C:\report_upload\Upload_Multiple_RDL_files.rss -s “http://localhost/reportserver” -v FILE_NAME=”C:\report_upload\ConfigurationFile.txt”
pause
This allowed me to discover the second (more important) issue; I ran the .bat file, and now I get the following error:
Unhandled exception:
The definition of this report is not valid or supported by this version of Reporting Services. The report definition may have been created with a later version of Reporting Services, or contain content that is not well-formed or not valid based on Reporting Services schemas. Details: ‘.’, hexadecimal value 0x00, is an invalid character. Line 2563, position 10.
Ummmmm, what?
Back to the Bing. I found another article that describes basically the same process, but at the bottom of the comment section, there was this little gem:
Hello, I was getting the invalid content error:
https://www.mssqltips.com/sqlservertip/3255/sql-server-reporting-services-ssrs-rsexe-utility/
“….or contain content that is not well-formed or not valid based on Reporting Services schemas. Details: ‘.’, hexadecimal value 0x00, is an invalid character….”
Made some research and found that arrays in vb are created as N+1 long size, and filled with 0x00, so you need to change the script to:
definition = New [Byte](stream.Length – 1) {}
to get it working.
BINGO! Went back to the .rss file and changed the below code block to
‘ Deploying the Reports
Try
Dim stream As FileStream = File.OpenRead(fileFullPath)
Dim NameWithExt As String = fileFullPath.Replace(path, “”)
Dim NameOnly As String = NameWithExt.Replace(“.rdl”, “”)
definition = New [Byte](stream.Length-1) {}
stream.Read(definition, 0, CInt(stream.Length))
Ran the.bat, and in just a few minutes, I had a ton of reports loaded to my directory. Now I just gotta fix the datasources.
**Edit – based on comment below, I’ve embedded the entire revised script.
‘ Script Starting Point
' Script to deploy report to report server
' EXECUTE VIA COMMAND LINE
‘ Original script: https://basic-ssrs.blogspot.com/2015/12/upload-multiple-rdl-files-from-local.html
‘ Upload_Multiple_RDL_files.rss
DIM definition As [Byte]() = Nothing
DIM warnings As Warning() = Nothing
Public Sub Main()
' Variable Declaration
Dim TextLine As String = ""
Dim LocalDir As String = ""
Dim ServerDir As String = ""
Dim definition As [Byte]() = Nothing
'Dim warnings As Warning() = Nothing
' Reading Configuration Text file and assigning the values to variables
If System.IO.File.Exists(FILE_NAME) = True Then
Dim objReader As New System.IO.StreamReader(FILE_NAME)
Do While objReader.Peek() <> -1
TextLine = objReader.ReadLine()
Dim parts As String() = TextLine.Split(New Char() {","c})
'TextLine & objReader.ReadLine() '& vbNewLine
LocalDir = parts(0)
ServerDir = parts(1)
Dim path As String = LocalDir
Dim fileEntries As String() = Directory.GetFiles(path)
Dim fileFullPath As String = ""
For Each fileFullPath In fileEntries
' Deploying the Reports
Try
Dim stream As FileStream = File.OpenRead(fileFullPath)
Dim NameWithExt As String = fileFullPath.Replace(path, "")
Dim NameOnly As String = NameWithExt.Replace(".rdl", "")
definition = New [Byte](stream.Length-1) {}
stream.Read(definition, 0, CInt(stream.Length))
warnings = rs.CreateReport(NameOnly, ServerDir, True, definition, Nothing)
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!", NameOnly)
End If
Catch e As IOException
Console.WriteLine(e.Message)
End Try
Next fileFullPath
Loop
Else
Dim MsgBox as String = "File Does Not Exist"
End If
End Sub
'End of the Script
