Functions/CatalogItems/Import-RsSubscriptionXml.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
# Copyright (c) 2016 Microsoft Corporation. All Rights Reserved.
# Licensed under the MIT License (MIT)

function Import-RsSubscriptionXml {
    <#
        .SYNOPSIS
            This script imports a subscription that has been exported e.g via Get-RsSubscription | Export-RsSubscriptionXml .\somepath.xml

        .DESCRIPTION
            This script imports a subscription and rebuilds the SRSS specific properties so that the resultant object will be accepted by Set-RsSubscription.
            This is useful if you need to store your subscription configs in a file (e.g to add to source control) and then later want to use that file to
            recreate your subscriptions.

        .PARAMETER Path
            The path to the XML file that contains the exported subscription.
            This is typically a file created by executing Get-RsSubscription | Export-RsSubscriptionXml .\somepath.xml

        .PARAMETER ReportServerUri
            Specify the Report Server URL to your SQL Server Reporting Services Instance.
            Use the "Connect-RsReportServer" function to set/update a default value.

        .PARAMETER Credential
            Specify the credentials to use when connecting to the Report Server.
            Use the "Connect-RsReportServer" function to set/update a default value.

        .PARAMETER Proxy
            Report server proxy to use.
            Use "New-RsWebServiceProxy" to generate a proxy object for reuse.
            Useful when repeatedly having to connect to multiple different Report Server.

        .EXAMPLE
            Import-RsSubscriptionXml .\MySubscriptions.xml

            Description
            -----------
            This command will import the subscriptions contained in .\MySubscriptions.xml, recreate any SRSS specific properties
            and output a PowerShell object with the result.

        .EXAMPLE
            Import-RsSubscriptionXml .\MySubscriptions.xml | Set-RsSubscription -Path /Example/Report

            Description
            -----------
            This command will import the subscriptions contained in .\MySubscriptions.xml, recreate any SRSS specific properties
            and pipe the results to Set-RsSubscription which will add them to the /Example/Report report.
    #>


    [cmdletbinding()]
    param(
        [Parameter(Mandatory=$True,Position=0)]
        [string]
        $Path,

        [string]
        $ReportServerUri,

        [System.Management.Automation.PSCredential]
        $Credential,

        $Proxy
    )

    Begin
    {
        $Proxy = New-RsWebServiceProxyHelper -BoundParameters $PSBoundParameters
        $Namespace = $Proxy.GetType().NameSpace
    }
    Process
    {
        Write-Verbose "Importing Subscription from $Path..."
        $Subscription = Import-Clixml $Path

        foreach ($Sub in $Subscription) 
        {
            #Recreate .DeliverySettings properties as valid SRSS object type
            $ParameterValues = @()

            $Sub.DeliverySettings.ParameterValues | ForEach-Object {
                if ($_.Name)
                {
                    $ParameterValues = $ParameterValues + (New-Object "$Namespace.ParameterValue" -Property @{ Name = $_.Name; Value = $_.Value })
                }
                elseif ($_.ParameterName)
                {
                    $ParameterValues = $ParameterValues + (New-Object "$Namespace.ParameterFieldReference" -Property @{ ParameterName = $_.ParameterName; FieldAlias = $_.FieldAlias })
                }
            }

            $DeliverySettings = @{
                Extension = $Sub.DeliverySettings.Extension
                ParameterValues = $ParameterValues 
            }

            $Sub.DeliverySettings = (New-Object "$Namespace.ExtensionSettings" -Property $DeliverySettings)

            #Recreate .Values property as valid SRSS object type
            $Values = @()

            $Sub.Values | ForEach-Object {

                if ($_.Name)
                {
                    $Values = $Values + (New-Object "$Namespace.ParameterValue" -Property @{ Name = $_.Name; Value = $_.Value })
                }
                elseif ($_.ParameterName)
                {
                    $Values = $Values + (New-Object "$Namespace.ParameterFieldReference" -Property @{ ParameterName = $_.ParameterName; FieldAlias = $_.FieldAlias })
                }
            }
            $Sub.Values = $Values


            #Recreate .DataRetrievalPlan property as valid SRSS object type for Data Driven subscriptions
            if ($Sub.IsDataDriven)
            {
                $DataSetDefinitionFields = @()
                    
                $Sub.DataRetrievalPlan.DataSet.Fields | ForEach-Object {
                    $DataSetDefinitionFields     = $DataSetDefinitionFields + (New-Object "$Namespace.Field" -Property @{ Alias = $_.Alias; Name  = $_.Name })
                }

                $DataSetDefinition = New-Object "$Namespace.DataSetDefinition"
                $DataSetDefinition.Fields = $DataSetDefinitionFields

                $DataSetDefinition.Query = New-Object "$Namespace.QueryDefinition"

                $DataSetDefinition.Query.CommandType            = $sub.DataRetrievalPlan.DataSet.Query.CommandType
                $DataSetDefinition.Query.CommandText            = $sub.DataRetrievalPlan.DataSet.Query.CommandText
                $DataSetDefinition.Query.Timeout                = $sub.DataRetrievalPlan.DataSet.Query.Timeout
                $DataSetDefinition.Query.TimeoutSpecified       = $sub.DataRetrievalPlan.DataSet.Query.TimeoutSpecified

                $DataSetDefinition.CaseSensitivity              = $sub.DataRetrievalPlan.DataSet.CaseSensitivity
                $DataSetDefinition.CaseSensitivitySpecified     = $sub.DataRetrievalPlan.DataSet.CaseSensitivitySpecified
                $DataSetDefinition.Collation                    = $sub.DataRetrievalPlan.DataSet.Collation
                $DataSetDefinition.AccentSensitivity            = $sub.DataRetrievalPlan.DataSet.AccentSensitivity
                $DataSetDefinition.AccentSensitivitySpecified   = $sub.DataRetrievalPlan.DataSet.AccentSensitivitySpecified
                $DataSetDefinition.KanatypeSensitivity          = $sub.DataRetrievalPlan.DataSet.KanatypeSensitivity
                $DataSetDefinition.KanatypeSensitivitySpecified = $sub.DataRetrievalPlan.DataSet.KanatypeSensitivitySpecified
                $DataSetDefinition.WidthSensitivity             = $sub.DataRetrievalPlan.DataSet.WidthSensitivity
                $DataSetDefinition.WidthSensitivitySpecified    = $sub.DataRetrievalPlan.DataSet.WidthSensitivitySpecified
                $DataSetDefinition.Name                         = $sub.DataRetrievalPlan.DataSet.Name

                $DataRetrievalPlanItem = New-Object "$Namespace.DataSourceReference"
                $DataRetrievalPlanItem.Reference = $sub.DataRetrievalPlan.Item.Reference

                $DataRetrievalSettings = @{ 
                    Item = $DataRetrievalPlanItem
                    DataSet = $DataSetDefinition
                }

                $DataRetrievalPlan = New-Object "$Namespace.DataRetrievalPlan" -Property $DataRetrievalSettings

                $Sub.DataRetrievalPlan = $DataRetrievalPlan  
            }

            #Output subscription
            $Sub
        }
    }
}