What is the ItemType for SSRS Catalog Type 14?

Question

SQL Server 2019. I manually uploaded a xlsx file to the reporting server and it was placed in an "Excel Workbooks" group. When I look in the reportserver catalog table I see the Type value is 14. I'm trying to write a powershell script to upload a bunch of xlsx files instead of having to do it manually, one by one. I need to know the ItemType. I can use "Resource" but it doesn't upload it to the "Excel Workbooks" group, it uploads it to the "Resources" group. I did list everything in my report server and see the TypeName for the xlsx I manually uploaded is "ExcelWorkbook", but is not a valid ItemType. Any suggestions? Below is the powershell I'm using (I'm still new to powershell). Thanks!

$WebServiceUrl = "http://xxxx"  
$ReportFolder = "PDF_Reports2"  
$SourceDirectory = $PSScriptRoot  
$Overwrite = $true  
  
$SSRSProxy = New-WebServiceProxy -Uri $WebServiceUrl'/ReportServer/ReportService2010.asmx?WSDL' -UseDefaultCredential  
  
# LIST ITEMS IN SERVER  
#$SSRSProxy.ListChildren("/",$true)  
  
  
$type = $SSRSProxy.GetType().Namespace  
$datatype = ($type + '.Property')                
$Property =New-Object ($datatype);  
$Property.Name = "MimeType"  
$Property.Value = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"  
  
$SourceDirectory = "c:\tmp\SSRS\"  
$ItemType = "Resource"   # Resource works, but it gets put in the Resources group, I want it in the Excel Workbooks group.  
$ReportFolder = "/PDF_Reports2"  
  
ForEach ($rdlfile in Get-ChildItem $SourceDirectory -Filter "*.xlsx" | Where-Object { $_.Attributes -ne "Directory" } )  
{  
    $byteArray = [System.IO.File]::ReadAllBytes($rdlfile.FullName)  
    write-host $rdlfile.FullName  
  
    $Warnings = @();  
  
    $SSRSProxy.CreateCatalogItem($ItemType, $rdlfile, $ReportFolder, $Overwrite, $byteArray, $Property, [ref]$Warnings)  
  
    $warnings.Length  
  
}  

Answer 1

Hi @Richard Uchytil ,
ItemType is the type of item in the report server database, and I can find some common explanations about type in a catalog table: table-explaination-for-dbocatalog-table-in-reportserver-database.
Regarding the issue of your uploaded files, I think you can refer to this link: https: ssrs-programmatic-import-of-excel-workbook.
Best regards,
Aniya

Answer 2

Hello, I solve same task. thats my solution (as is, sorry):

my bat file:

for %%i in ("Z:\xlsx%1") do (Powershell.exe -executionpolicy remotesigned -File Z:\xlsx\LoadExcelFileToRS.ps1 %%~nxi %2)

@echo off

---

parameters:

%1 - filemask e.g. *.xlxs

%2 - folderPath on ms ssrs e.g. "/Files/Cat1/Cal2"

---

my PS file:

#load xlsx file to ms ssrs--------------------------------------------------
#Run from command line: Powershell.exe -executionpolicy remotesigned -File Z:\Nalog\LoadFileToRS.ps1 FileName RSPath

$SSRSServer='web.domain.ru'
$URI = "http://$SSRSServer/Reports/api/v2.0"
$FileName = $args[0]
$RSPath = $args[1]
$enc = [System.Text.Encoding]::UTF8.GetBytes($FileName)
$FileName = [System.Text.Encoding]::UTF8.GetString($enc)
# $FileName

# $FileName = 'FileName.xlsx'
$enc = [System.Text.Encoding]::UTF8.GetBytes($RSPath)
$RSPath = [System.Text.Encoding]::UTF8.GetString($enc)
#$RSPath


[System.Net.ServicePointManager]::SecurityProtocol = [System.Net.SecurityProtocolType]::Tls12;

 
# for xlsx files ----------------------------------------------------------------------------------------
$uploadItemPath = 'Z:\xlsx\'+$FileName
# $uploadItemPath = $FileName
$catalogItemsUri = $Uri + "/CatalogItems"
$bytes = [System.IO.File]::ReadAllBytes($uploadItemPath)
$payload = @{
    "@odata.type" = "#Model.ExcelWorkbook"
    "Content" = [System.Convert]::ToBase64String($bytes)
    "ContentType"=""
    "Name" = $FileName
#    "Path" = '/Налоги/Файлы/2022 год/2022-02 кв/Регистры'
    "Path" = $RSPath
    } | ConvertTo-Json
Invoke-WebRequest -Uri $catalogItemsUri -Method Post -Body $payload -ContentType "application/json;charset=utf-8" -UseDefaultCredentials | Out-Null

<# 
# any other files (as resource) ----------------------------------------------------------------------------------------
$uploadItemPath = 'Z:\anyOther\'+$FileName
#$uploadItemPath = $FileName
$catalogItemsUri = $Uri + "/CatalogItems"
$bytes = [System.IO.File]::ReadAllBytes($uploadItemPath)
$payload = @{
    "@odata.type" = "#Model.Resource"
    "Content" = [System.Convert]::ToBase64String($bytes)
    "ContentType"=""
    "Name" = $FileName
    "Path" = '/Files/Cat1/CatOther'
    } | ConvertTo-Json
Invoke-WebRequest -Uri $catalogItemsUri -Method Post -Body $payload -ContentType "application/json;charset=utf-8" -UseDefaultCredentials | Out-Null
#>