Open mst42a opened 4 years ago
In addition, subscriptions after import had the wrong Status (Disabled / Enabled).
I was having issues with this and I ended up writing the following to migrate subscriptions from one server to another. It runs a query against the ReportServer db and uses the data from that to create the subs on the target server using New-RsSubscription. I've only tested it for email and fileshare subscriptions as I don't have any DDS but it works (please excuse my shady powershell skills):
<#
Uses ReportingServicesTools utility to transfer subscriptions from one report server to another.
Runs a query on the source server ReportServer database to get the information and then uses this data and New-RsSubscription to create the subcriptions on the target report server.
Have to install ReportingServicesTools
Install-Module -Name ReportingServicesTools
EXAMPLE:
SSRS_MigrateSubs.ps1 -SourceServer Server01 -TargetServer Server02
Tested with email and fileshare but not with data-driven
#>
#Parameters
Param
(
[Parameter(Position=0, Mandatory=$true)]
[String] $SourceServer,
[Parameter(Mandatory=$true)]
[String] $TargetServer
)
#source server ReportServer database connection string
$SQLSourceconnectionString = "server=$SourceServer;database='ReportServer';trusted_connection=true;";
#Query to run to get the subscription information needed
$sqlgetsubs = ";WITH xmlsetup
AS
(
SELECT
S.[SubscriptionID],
S.[Locale],
CAT.Path AS ReportName,
S.[ReportZone],
Owner.Sid AS OSid,
Owner.[UserName] AS OUsername,
Owner.AuthType AS OAuthType,
S.[DeliveryExtension],
S.[InactiveFlags],
CAST(S.[ExtensionSettings] AS XML) AS ExtensionSettings,
Modified.Sid AS MSID,
Modified.[UserName] AS MUserName,
Modified.AuthType AS MAuthType,
S.[ModifiedDate],
S.[Description],
S.[LastStatus],
S.[EventType],
CAST(S.[MatchData] AS XML) AS Schedule,
CAST(S.[Parameters] AS XML) AS [Parameters],
S.[DataSettings],
S.[Version],
sched.ReportAction,
sched.ScheduleID
FROM [Subscriptions] S
INNER JOIN [Catalog] CAT ON S.[Report_OID] = CAT.[ItemID]
INNER JOIN [Users] Owner ON S.OwnerID = Owner.UserID
INNER JOIN [Users] Modified ON S.ModifiedByID = Modified.UserID
INNER JOIN [ReportSchedule] sched ON sched.SubscriptionID = s.SubscriptionID
INNER JOIN [Schedule] sc ON sc.ScheduleID = sched.ScheduleID)
SELECT
s.ReportName AS [Path],
s.[Description],
s.EventType,
CASE S.DeliveryExtension
WHEN 'Report Server Email' THEN 'Email'
WHEN 'Report Server FileShare' THEN 'Fileshare'
END AS DeliveryMethod,
s.Schedule,
CASE
WHEN s.DeliveryExtension = 'Report Server Email'
THEN ExtensionSettings.value('(//ParameterValue/Value[../Name=`"RenderFormat`"])[1]','nvarchar(50)')
ELSE ExtensionSettings.value('(//ParameterValue/Value[../Name=`"RENDER_FORMAT`"])[1]','nvarchar(50)')
END
AS RenderFormat,
ExtensionSettings.value('(//ParameterValue/Value[../Name=`"TO`"])[1]','nvarchar(1000)') AS [To],
ExtensionSettings.value('(//ParameterValue/Value[../Name=`"CC`"])[1]','nvarchar(1000)') AS [CC],
ExtensionSettings.value('(//ParameterValue/Value[../Name=`"BCC`"])[1]','nvarchar(1000)') AS [BCC],
ExtensionSettings.value('(//ParameterValue/Value[../Name=`"ReplyTo`"])[1]','nvarchar(1000)') AS [ReplyTo],
ExtensionSettings.value('(//ParameterValue/Value[../Name=`"Subject`"])[1]','nvarchar(1000)') AS [Subject],
CASE ExtensionSettings.value('(//ParameterValue/Value[../Name=`"IncludeReport`"])[1]','bit') ^ 1
WHEN 1 THEN 'True'
WHEN 0 THEN 'False'
END AS [ExcludeReport],
CASE ExtensionSettings.value('(//ParameterValue/Value[../Name=`"IncludeLink`"])[1]','bit') ^ 1
WHEN 1 THEN 'True'
WHEN 0 THEN 'False'
END AS [ExcludeLink],
ExtensionSettings.value('(//ParameterValue/Value[../Name=`"Priority`"])[1]','nvarchar(1000)') AS [Priority],
ExtensionSettings.value('(//ParameterValue/Value[../Name=`"Comment`"])[1]','nvarchar(1000)') AS [Comment],
ExtensionSettings.value('(//ParameterValue/Value[../Name=`"PATH`"])[1]','nvarchar(1000)') AS [FileSharePath],
ExtensionSettings.value('(//ParameterValue/Value[../Name=`"FILENAME`"])[1]','nvarchar(1000)') AS [FileName],
ExtensionSettings.value('(//ParameterValue/Value[../Name=`"WRITEMODE`"])[1]','nvarchar(1000)') AS [FileWriteMode],
s.Parameters AS ReportParameters
FROM
xmlsetup s"
#connect to source SQL server
$sqlConnection = new-object System.Data.SqlClient.SqlConnection;
$sqlConnection.ConnectionString = $SQLSourceconnectionString;
#SQL Command - set up the SQL call
$sqlCommand = New-Object System.Data.SqlClient.SqlCommand;
$sqlCommand.Connection = $sqlConnection;
$sqlCommand.CommandText = $sqlgetsubs;
#SQL Adapter - get the results using the SQL Command
$sqlAdapter = new-object System.Data.SqlClient.SqlDataAdapter
$sqlAdapter.SelectCommand = $sqlCommand
$dataSet = new-object System.Data.Dataset
$recordCount = $sqlAdapter.Fill($dataSet)
#Close SQL Connection
$sqlConnection.Close();
#Get single table from dataset
$data = $dataSet.Tables[0]
#iterate through each row of the result and create a subscription on the target report server
foreach ($row in $data)
{
#convert the parameters to a hash table
[xml]$rp = $row.ReportParameters
$params = @{}
if ($rp.ParameterValues.ChildNodes)
{$rp.ParameterValues.ChildNodes | Foreach {$params[$_.Name] = $_.Value} }
if ($row.DeliveryMethod -eq "Email")
#do the email subscriptions
{
#convert these values to proper booleans
$ExcludeReport = [System.Convert]::ToBoolean($row.ExcludeReport)
$ExcludeLink = [System.Convert]::ToBoolean($row.ExcludeLink)
New-RsSubscription -ReportServerUri "http://$TargetServer/ReportServer" -RsItem $row.Path -Description $row.Description -Schedule $row.Schedule -Parameters $params -EventType $row.EventType -DeliveryMethod $row.DeliveryMethod `
-RenderFormat $row.RenderFormat -To $row.TO -CC $row.CC -BCC $row.BCC -ReplyTo $row.ReplyTo -ExcludeReport:$ExcludeReport -Subject $row.Subject -Comment $row.Comment -ExcludeLink:$ExcludeLink `
-Priority $row.Priority
}
else
#do the fileshare subscriptions
{
New-RsSubscription -ReportServerUri "http://$TargetServer/ReportServer" -RsItem $row.Path -Description $row.Description -Schedule $row.Schedule -Parameters $params -EventType $row.EventType -DeliveryMethod $row.DeliveryMethod `
-FileSharePath $row.FileSharePath -RenderFormat $row.RenderFormat -Filename $row.FileName -FileWriteMode $row.FileWriteMode
}
}
Do you want to request a feature or report a bug? BUG What is the current behavior?
imports subscriptions from a file to the wrong location. The first subscription in the list goes to the right place, the rest are recreated in the original report.
If the current behavior is a bug, please provide the steps to reproduce and if possible a minimal demo of the problem. First I have use Export-RsSubscriptionXml and create subscriptions.xml file. Get-RsSubscription -Proxy $proxy -path '/Prototypes/MS/Customer Requests Summary' | Export-RsSubscriptionXml "C:\Users\mstaszak\Downloads\RS_test\MySubscriptions.xml" Next, I have trying to import this file. Import-RsSubscriptionXml -Proxy $proxy -path "C:\Users\mstaszak\Downloads\RS_test\MySubscriptions.xml" | Copy-RsSubscription -Proxy $proxy -RsItem '/Prototypes/MS/Customer Requests' -Verbose
It looks like the first occurrence is placed correctly, but the next one gets wrong DestinationPath and creating an additional subscription in my original report (now I have two identically). What is the expected behavior?
Copy all of the subscriptions to the new Report
Which versions of Powershell and which OS are affected by this issue? Did this work in previous versions of our scripts?
PSVersion 5.1.18362.752
PSEdition Desktop Windows 10