Problem:
In an organization, there was a requirement to create a
subscription on SSRS report to export the report’s pdf copy to SharePoint library
while the SSRS is installed in native mode. And in subscription of report there
is no option of “Document library” delivery.
Solution:
1.
First, create a folder and “share”
it from the property of that folder I.e we can access that shared location like
\\ServerName\Reports
2.
In the SSRS report subscription,
deliver the report using “File folder” destination, in “file name” add “_@timestamp”
at the end as it will add the date and time with file name.
3.
Log on to the SharePoint server where we want to
upload that report and open PowerShell Integrated Scripting Environment (ISE) and
write the below code and save it with any name i.e. ExportToSharepoint.ps1
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web
Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web
Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
Add-PSSnapin Microsoft.SharePoint.PowerShell
$webUrl = "http://my/XXXX/"
$docLibraryName = "DocLib"
$docLibraryUrlName = "DocLib8\sub\sub" # specify
your subfolder url here
$web = Get-SPWeb $webUrl
$docLibrary = $web.Lists[$docLibraryName]
$files = ([System.IO.DirectoryInfo] (Get-Item $localFolderPath)).GetFiles()
ForEach($file
in $files)
{
if($file.Name.Contains(".pdf"))
{
$fileStream = ([System.IO.FileInfo]
(Get-Item $file.FullName)).OpenRead()
$folder = $web.getfolder($docLibraryUrlName)
$spFile = $folder.Files.Add($folder.Url + "/" +
$file.Name,[System.IO.Stream]$fileStream, $true)
$fileStream.Close();
}
}
$web.Dispose()
4.
Put the ExportToSharepoint.ps1
in windows task scheduler by following the steps https://www.metalogix.com/help/Content%20Matrix%20Console/SharePoint%20Edition/002_HowTo/004_SharePointActions/012_SchedulingPowerShell.htm
5.
It will be better to create
another windows task schedule to delete that report after few minutes when it
is uploaded to the SharePoint as ExportToSharepoint.ps1 will upload all
the file that are available on the location.
6.
To delete the reports from
the location use below PowerShell script:
$Path
= "D:\SharePointReport
"
$Daysback
= "-0"
$CurrentDate
= Get-Date
$DatetoDelete
= $CurrentDate.AddDays($Daysback)
Get-ChildItem
$Path |
Where-Object { $_.LastWriteTime -lt
$DatetoDelete } |
Remove-Item
7.
Save step 6 code as “DeleteFile.ps1”
and put in the windows task scheduler and its done.
