[SSIS][SSRS]透過 SSIS 來整批匯出所有 SSRS 內的報表檔

2015-04-13

4269
0
SSIS
2016-01-27

[SSRS]透過 SSIS 來整批匯出所有 SSRS 內的報表檔

既前一篇「SSRS 從 SQL Server 2008 移轉並升級到 SQL Server 2014」寫完之後，有朋友來信詢問說，因為舊的報表專案沒有保留下來，因此要整批將報表格式檔案給匯出，以利後續編輯或版本控制的話，那麼又該怎麼來處理呢 ?

網路上有介紹一些方式，像是利用 PowerShell 或者是用 T-SQL 讀取 ReportServer 資料庫內的 Catalog 表來處理，雖然都不錯，但都有些不滿意的地方，因此我用 SSIS 來展示如何實作這樣的功能，基本上我會採用 VS 2013 來配合 SQL Server 2014 進行，但如果您是採用 SQL Server 2008 或 2012，基本上都是相通的。

前置處理

首先我們先建立一個 SSIS 的專案，如果您也想使用 VS 2013 的話，可以參考另外一篇文章「使用 Visual Studio Community 2013 搭配 Business Intelligence for Visual Studio 2013 開發商業智慧」，搭配社群版本的 VS 2013 來使用。

在這個專案內，一開始我們透過設定「封裝參數」，設定一個參數 RootDirectory，型態是 String，主要是用來指定我們要匯出的目錄在哪裡，如果是 SSIS 2008 的話，因為使用封裝部屬模式，還沒有參數的功能，可以使用變數來做取代。

接著我們在「連接管理員」上新增一個新的「ADO.Net 連線」，指定到我們所要匯出的 ReportServer 的資料庫上，此部分可以按照您的需要改成不同的主機或連線方式，只要能連接到 ReportServer 的資料庫就可以了。

並且將該連線命名為 ReportServer

控制流程

完成前置作業之後，我們就可以先處理控制流程

1. 刪除目錄 : 此部分我們使用「檔案系統工作」的元件，主要是用來將要匯出的目錄下確定沒有任何舊的資料，設定參數如下：

Operation → 刪除目錄

IsSourcePathVariable → True

SourceVariable → $Package::RootDirectory ( 如果您是使用變數的話，則要注意一下命名空間 )

2. 取得目錄 : 此部分我們使用「執行 SQL 工作」的元件，主要是用來取得匯出的目錄下要有那些子目錄，設定參數如下：

ConnectionType → ADO.NET

Connection → ReportServer

SourceStatement → SELECT Name FROM dbo.Catalog WHERE Type = 1 AND ParentID IS NOT NULL

ResultSet → 完整結果集

因為要將結果先放到一個變數內，以利後續 Foreach 迴圈來使用，因此設定完「一般」的部分之後，選擇左邊的「結果集」，在變數的部分選擇「新增變數」，加入一個新的名稱是 Paths 類型為 Object 的變數

將結果放置到這個新增出來的變數內

3. Foreach 迴圈容器 : 此部分我們使用「Foreach 迴圈容器」的容器，主要是用來將上個步驟的 Paths 的變數一筆一筆取出，選擇左邊「集合」設定參數如下：

Enumerator → Foreach ADO 列舉值

ADO 物件來源變數 → User::Paths

為了取得迴圈的變數值，因此在「變數對應」的部分新增一個名稱為 Path 數值類型 String 的變數

4. 取得完整目錄名稱 : 此部分我們使用「運算式工作」的元件，主要是用來將透過迴圈取得的變數值和封裝參數做個合併，這樣我們就可以知道在我們所要匯出的目錄下要建立那些目錄。在執行這個步驟之前，我們需要先建立一個名稱為 Directory 數值類型 String 的變數，建立好之後就可以在下方「運算式」內進行設定，要稍微注意一下因為 \ 是跳脫字元，因此這裡要用兩個 \ 的符號。

運算式 → @[User::Directory] = @[$Package::RootDirectory] + "\\" + @[User::Path]

5. 建立目錄 : 此部分我們使用「檔案系統工作」的元件，主要是用來建立匯出的目錄和其下的子目路，設定參數如下：

Operation → 建立目錄

IsSourcePathVariable → True

SourceVariable → $User::Directory

6. 匯出 XML : 此部分我們使用「資料流程工作」的元件。

資料流程

透過前面控制流程的相關處理，我們可以透過取得 Catalog 內的資訊，建立好相關的目錄，接下來我們就進入資料流程，取得 Catalog 資料表內的資料並且匯出 XML 的資料放置到檔案內。

1. 取得 Catalog : 此部分我們使用「ADO.Net 來源」的元件，設定連接管理員和 SQL 命令文字， SQL 命令如下

WITH ItemContentBinaries AS
(
  SELECT
     ItemID,[Path],[Type]
   ,CASE Type
      WHEN 2 THEN 'Report'
      WHEN 5 THEN 'Data Source'
      WHEN 7 THEN 'Report Part'
      WHEN 8 THEN 'Shared Dataset'
      ELSE 'Other'
     END AS TypeDescription
   ,Content
   FROM ReportServer.dbo.Catalog
   WHERE Type IN (2,5,7,8)
),
ItemContent AS
(
  SELECT
     ItemID,[Path],[Type],TypeDescription
    ,CASE Type
       WHEN 2 THEN '.rdl'  --Report Definition Language
       WHEN 5 THEN '.rds'  --Report Data Source
       WHEN 7 THEN '.rsc'  --Report Server Component (? - Guessing)
       WHEN 8 THEN '.rsd'  --Report Server Data (? - Guessing)
     END AS ExportFileExtension
    ,Content
  FROM ItemContentBinaries
)
--The outer query gets the content in its varbinary, varchar and xml representations...
SELECT
   REPLACE( [Path], '/', '\' ) + ExportFileExtension AS ExportFileName
  ,Content AS ContentXML          
FROM ItemContent

這樣我們就可以取得匯出的名稱和資料內容