CData Software Blog

クラウド連携のCData Software の技術ブログです。

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

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

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

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

開発環境

今回使用する環境は、以下の通りです。
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 に追加する、リストアイテムを格納したテーブルです。

ListDataテーブル

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

AttachmentItems

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

SharePoint のリスト

プロジェクトの作成

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

Visual Studio 2019 新しいプロジェクトの作成

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

Integration Services Project を選択

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

プロジェクト名「Sample01」を入力

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

SSIS プロジェクトの初期画面

接続設定 SQL Server

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

新しい接続

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

ADO.NET を追加

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

ADO.NET の接続マネージャーの構成

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

SQLServer の接続情報を入力

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

ADO.NET の接続マネージャーの構成

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

Name プロパティにわかりやすい名前を設定

接続設定 SharePoint

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

CDATA_SHAREPOINT を選択

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

CData SharePoint Connection Manager
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 ループで取り出す

リストアイテム Foreach ループ
リストアイテム 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;
        }

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

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

SQL Server から、添付ファイルの情報を取得
添付ファイルの情報を取得(パラメーターマッピング)
添付ファイルの情報を取得(結果セット)

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

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

添付ファイル Foreach ループ
添付ファイル Foreach ループ(変数のマッピング)

添付ファイルを追加する

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

リストアイテムに添付ファイルを追加
リストアイテムに添付ファイルを追加(Parameter Mapping)

全体のフロー

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

全体のフロー

実行結果

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

実行結果

まとめ

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

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