SSIS を使って、SharePoint のリストに項目を追加する

こんにちは。
CData Software Japan Support Engineer の松本です。

SQL Server Integration Services (以下 SSIS )は、コンポーネントを組み合わせて、フローを作成しますが、具体的な作り方を解説している記事があまりなく、初めて触るとき、どういう風に作ればいいのかわからないことがあります。

今回は、そんなときのチュートリアルとして、SQL Server のデータを元に、SharePoint へリストアイテムと添付ファイルを追加するフローの作成方法を解説します。

開発環境
SSIS で行う処理
準備
プロジェクトの作成
接続設定 SQL Server
接続設定 SharePoint
変数の定義
SQLServer から、追加するリストアイテムを取得
リストアイテムをForeach ループで取り出す
SharePoint にリストアイテムを追加
SQLServer から、リストアイテムに追加する添付ファイルの情報を取得
添付ファイルの情報をForeach ループで取り出す
添付ファイルを追加する
全体のフロー
実行結果
まとめ

開発環境

今回使用する環境は、以下の通りです。
・Microsoft Visual Studio Community 2019
・SQL Server Integration Services Projects
・Microsoft Analyis Services Projects
・CData SSIS components for SharePoint

注意点として、最新版のVisual Studio 2022 では、SSIS の開発を行うことはできません。(2022/03 時点)

SSIS 全般の使い方については、Microsoft の自習書シリーズをご覧ください。
SQL Server 2012 自習書シリーズ No.7 Integration Services 入門
https://download.microsoft.com/download/3/2/d/32d0a666-103c-4c47-a0cb-3dfe5ab93fe1/SQL11_SelfLearning07_SSIS1.pdf

SQL Server 2012 自習書シリーズ No.8 Integration Services 応用
https://download.microsoft.com/download/3/2/d/32d0a666-103c-4c47-a0cb-3dfe5ab93fe1/SQL11_SelfLearning08_SSIS2.pdf

SSIS で行う処理

今回作成するフローは、このような流れになっています。
1. SQL Server のデータを、SharePoint のリストに追加します。
2. 追加したリストの項目に、ファイルを添付ファイルとして追加します。

準備

SQL Server のデータは以下の通りです。
ListData テーブルは、SharePoint に追加する、リストアイテムを格納したテーブルです。

AttachmentItems テーブルは、リストアイテムに追加する、添付ファイルのファイルパスとファイル名を格納したテーブルです。

SharePoint には、追加先となるリスト「TestList 」を作成します。

プロジェクトの作成

Visual Studio を起動して、新しいプロジェクトの作成(N)をクリックします。

テンプレートの検索に「Integration Services Project 」と入力し、Integration Services Project を選択します。

プロジェクト名を入力し、「作成」をクリックします。

SSIS プロジェクトが作成されます。

接続設定 SQL Server

接続マネージャーを右クリックし、「新しい接続(W)...」をクリックします。

SSIS 接続マネージャーの追加ダイアログが表示されるので、「ADO.NET 」を選択し、「追加(A)...」をクリックします。

ADO.NET の接続マネージャーの構成ダイアログが表示されるので、「新規作成(E)...」をクリックします。

接続マネージャーダイアログが表示されるので、接続情報を入力し、「接続テスト(T)」をクリックして、接続できることを確認します。

「OK」をクリックし、画面を閉じると、ADO.NET の接続マネージャーの構成ダイアログに接続先が追加されます。

接続マネージャーに接続先が追加されたら、Name プロパティにわかりやすい名前を設定します。

接続設定 SharePoint

SQL Server と同様に、SharePoint の接続先を追加します。
SSIS 接続マネージャーの追加ダイアログを表示し、「CDATA_SHAREPOINT」を選択し、「追加(A)...」をクリックします。

CData SharePoint Connection Manager ダイアログが表示されるので、接続情報を入力し、「Test Connection 」をクリックして、接続できることを確認します。
今回は、SharePoint Online に接続するので、下記のように設定します。

Authentication
	Auth Scheme	OAuth
	URL	SharePoint のサイトのURL
	User	ユーザー名
	Password	パスワード
OAuth	Initiate OAuth	GETANDREFRESH
Schema	Schema	REST

こちらもSQL Server と同じように、Name プロパティにわかりやすい名前を設定します。

変数の定義

使用する変数を定義します。今回使用した変数は下記の通りです。

Object型は、テーブルから完全な結果セットを受け取る変数です。

ConstListは、リストアイテムの追加先となるリスト名を指定しています。
今回は、直接定義していますが、実際に運用する場合は、外部に定義ファイルなどを作成し、そこから読み込むようにすると、プロジェクトを直接編集しなくても、設定を変更することができるようになります。

SQLServer から、追加するリストアイテムを取得

始めに、SQL Server のListData テーブルから、追加するリストアイテムを取得します。

リストアイテムは複数あるので、ResultSet で完全な結果セットを選択し、結果セットにObject型の変数を指定して、結果を受け取ります。

リストアイテムをForeach ループで取り出す

リストアイテムに追加するため、SQL Server から取得した結果セットを、Foreach ループで1レコードずつ取り出します。変数マッピングに、レコードのカラムに対応する変数を割り当てることで、ループごとに、各カラムの値を取り出すことができます。

SharePoint にリストアイテムを追加

SharePoint へリストアイテムを追加しますが、今回は追加したアイテムに対して、さらに添付ファイルを追加するので、追加したリストアイテムのItemId を取得しておく必要があります。

追加したリストアイテムのID は、INSERT後に「SELECT SCOPE_IDENTITY() 」で取得することができますが、CData SharePoint Task は、Task が終了すると、リソースが解放されてしまうため、そのままでは取得することができません。

今回は、スクリプトタスクを使用して、一つのタスク内で二つの処理を連続で行います。

追加するリスト名の変数を、ReadOnlyVariables に指定し、追加したリストアイテムのItemId を、ExecValue Variable プロパティに指定した変数に格納します。

ScriptMain.cs

        public void Main()
        {
            //ReadOnlyVariablesに割り当てた、挿入するリストアイテム名を取得
            var listName = Dts.Variables["User::ListName"].Value;
            //SharePointの接続
            var SharePoint = (IDbConnection)Dts.Connections["SharePoint"].AcquireConnection(Dts.Transaction);

            //リストアイテムを追加
            using (var command = SharePoint.CreateCommand())
            {
                command.CommandText = $"INSERT INTO 'TestList'(Title) VALUES ('{listName}')";
                command.ExecuteNonQuery();
            }
            //追加したリストアイテムのIDを取得
            using (var command = SharePoint.CreateCommand())
            {
                command.CommandText = "SELECT SCOPE_IDENTITY()";
                Dts.ExecutionValue = int.TryParse(command.ExecuteScalar().ToString(), out var newId) ? newId : throw new InvalidCastException();
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }