Export all of Report on Power Bi Server - Power Bi

Posted by: bart30508 | in PowerBI, Powershell | 2 months ago |

<# .SYNOPSIS Export of all SSRS reports datasources and images .DESCRIPTION This PowerShell script exports all (or filtered) reports, data sources and images directly from the ReportServer database to a specified folder. For the file name the complete report path is used; for file name invalid characters are replaced with a -. Reports are exported with .rdl as extension, data sources with .rds and resources without any additional extension. Please change the "Configuration data" below to your enviroment. Works with SQL Server 2005 and higher versions in all editions. Requires SELECT permission on the ReportServer database. .NOTES Author : Olaf Helper Requires: PowerShell Version 1.0, Ado.Net assembly .LINK GetSqlBinary: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getsqlbinary.aspx #>

# Configuration data
[string] $server   = "PowerBiDBServer";                      # SQL Server Instance.
[string] $database = "PowerBiDBName";                        # ReportServer Database.
[string] $folder   = "C:\Users\Export_PBI_SSRS\";            # Path to export the reports to.

<# # Select-Statement for file name & blob data with filter.    
$sql = "SELECT  CT.[Path]
        ,CT.[Type]
        ,ISNULL(cc.ContentType,'SSRS') as ContentType
        ,CONVERT(varbinary(max), cc.[Content]) AS PBI_BinaryContent
        ,CONVERT(varbinary(max), ct.[Content]) AS RDL_BinaryContent
FROM dbo.[Catalog] AS CT
        LEFT OUTER JOIN dbo.CatalogItemExtendedContent cc
            ON ct.ItemID = cc.ItemId
WHERE CT.[Type] IN (2, 8, 5,13)
    AND ISNULL(cc.ContentType,'CatalogItem') = 'CatalogItem'";      
 #       WHERE CT.[Type] IN (8)"; #>

 # Select-Statement for file name & blob data with filter. Just All File 20220311 max
$sql = "SELECT DISTINCT
         CT.[Name]
        ,CT.[Type]
        ,ISNULL(cc.ContentType,'SSRS') as ContentType
        ,CONVERT(varbinary(max), cc.[Content]) AS PBI_BinaryContent
        ,CONVERT(varbinary(max), ct.[Content]) AS RDL_BinaryContent
FROM dbo.[Catalog] AS CT
        LEFT OUTER JOIN dbo.CatalogItemExtendedContent cc
            ON ct.ItemID = cc.ItemId
WHERE CT.[Type] IN (2, 8, 5,13)
    AND ISNULL(cc.ContentType,'CatalogItem') = 'CatalogItem'";      
 #       WHERE CT.[Type] IN (8)";

# Open ADO.NET Connection with Windows authentification.
$con = New-Object Data.SqlClient.SqlConnection;
$con.ConnectionString = "Data Source=$server;Initial Catalog=$database;Integrated Security=True;";
$con.Open();

Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) + ": Started ...");

# New command and reader.
$cmd = New-Object Data.SqlClient.SqlCommand $sql, $con;
$rd = $cmd.ExecuteReader();

$invalids = [System.IO.Path]::GetInvalidFileNameChars();
# Looping through all selected datasets.
While ($rd.Read())
{
    Try
    {
        # Get the name and make it valid.
        $name = $rd.GetString(0);
        Write-Output "fetching $name"
        foreach ($invalid in $invalids)
           {    $name = $name.Replace($invalid, "-");    }

        If ($rd.GetInt32(1) -eq 2)
            {    $name = $name + ".rdl";    }
        ElseIf ($rd.GetInt32(1) -eq 5)
            {    $name = $name + ".rds";    }
        ElseIf ($rd.GetInt32(1) -eq 8)
            {    $name = $name + ".rsd";    }
        ElseIf ($rd.GetInt32(1) -eq 11)
            {    $name = $name + ".kpi";    }
        ElseIf ($rd.GetInt32(1) -eq 13)
        {   $name = $name + ".pbix";    }



        Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) + ": Exporting {0}" -f $name);

        $name = [System.IO.Path]::Combine($folder, $name);

        # New BinaryWriter; existing file will be overwritten.
        $fs = New-Object System.IO.FileStream ($name), Create, Write;
        $bw = New-Object System.IO.BinaryWriter($fs);

        # Read of complete Blob with GetSqlBinary
        if ($rd.GetString(2) -eq "SSRS") {
            $bt = $rd.GetSqlBinary(4).Value;
        } else{
            $bt = $rd.GetSqlBinary(3).Value;
        }


        $bw.Write($bt, 0, $bt.Length);
        $bw.Flush();
        $bw.Close();
        $fs.Close();
    }
    Catch
    {
        Write-Output ($_.Exception.Message)
    }
    Finally
    {
        $fs.Dispose();
    }
}

# Closing & Disposing all objects
$rd.Close();
$cmd.Dispose();
$con.Close();
$con.Dispose();

Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) + ": Finished");
Currently unrated
 or 

Subscribe

* indicates required

Recent Posts

Archive

2022
2021

Categories

Apache 1

Data Science 2

Dbfit 1

Design Pattern 1

Devops 3

DigitalOcean 1

Django 1

English 3

Excel 5

Flask 3

Git 1

HackMD 1

Heroku 1

Html/Css 1

Linux 4

Machine Learning 2

Manufacture 1

Mezzanine 18

Oracle 1

Postgresql 7

PowerBI 4

Powershell 4

Python 21

SEO 2

SQL Server 51

SQLite 1

Windows 1

database 8

work-experience 1

其他 1

自我成長 1

資料工程 1

Tags

SEO(1) Github(2) Title Tag(2) ML(1) 李宏毅(1) SQL Server(18) Tempdb(1) SSMS(1) Windows(1) 自我成長(2) Excel(1) python Flask(1) python(5) Flask(2)

Authors

bart30508 (146)

Feeds

RSS / Atom

Export all of Report on Power Bi Server - Power Bi

© COPYRIGHT 2011-2022. Max的文藝復興. ALL RIGHT RESERVED.