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

by 松本勝成 | 2022年03月31日

こんにちは。
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 に接続するので、下記のように設定します。

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

変数の定義

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

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

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

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

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

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

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

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

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

        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;
        }

SQLServer から、リストアイテムに追加する添付ファイルの情報を取得

リストアイテムの追加と、ItemId の取得ができたので、リストアイテムに添付ファイルの追加するため、AttachmentItems テーブルから、添付ファイルの情報を取得します。

添付ファイルの情報をForeach ループで取り出す

ListData と同じく、Foreach ループを使って、一つずつ取り出していきます。

添付ファイルを追加する

最後に、リストアイテムに添付ファイルを追加します。

全体のフロー

これで、一連のフローが作成できました。

実行結果

実行すると、SharePoint にリストアイテムが追加され、さらに、リストアイテム内に添付ファイルが追加されます。

まとめ

SSIS は、このようにコンポーネントを組み合わせていくことで、処理を組み立てていくことができます。
さらにパラメーターを外部に持たせたりするなどの工夫をすることで、より柔軟な処理を作ることができます。

また、スクリプトタスクは、非常に強力なコンポーネントなので、既存のコンポーネントで難しいと感じた時は、スクリプトタスクの活用も検討してみてください。

関連コンテンツ