【C#】Microsoft.SqlServer.Dts.Runtimeを使ってパラメータマッピングと結果セットを持つSQL実行タスクを作成する

C# SSIS snippet

おさらい

タスクを作成する場合はPackage.Executables.Addを呼び出すことで可能となります。具体的に何のタスクを作成するかは「プログラムによるタスクの追加」という資料に記載されているSTOCKモニカーで指定することができます。

返ってくるExecutableクラスをTaskHostクラスにキャストし、TaskHost.PropertiesとSetValueメソッドを使用することで値を設定することができます。

//タスクを作成
//TaskHostにキャストすることでタスクに共通のプロパティを設定することができる
using (var th = package.Executables.Add("STOCK:SQLTask") as TaskHost)
{
    th.Name = "SQL Test";
    //TaskHost.Properties["foo"].SetValueで各タスク固有のプロパティにアクセスできる
    th.Properties["Connection"].SetValue(th, dbId);
    th.Properties["SqlStatementSource"].SetValue(th, "SELECT * FROM HOGE");
}

今回の目的

個人的によく使うし、なんとなく外部ファイルから自動生成しやすそうな「WHEREにSSISの変数によるパラメータが設定されているSQL実行タスク」と「単一行を返すSQL実行タスク」を作ってみます。面倒なので、一つにまとめますが。

とりあえず必要そうなものを適当に羅列してみましょう。

  • SQLを実行するための接続マネージャー
  • パラメータに使用する変数
  • 単一行を受け取る変数

この辺も併せて自動生成してしまいましょう。

dtsxを読む

実際にコードを書く前に、今回の要件のような場合、SSDTで生成されるdtsxではSQL実行タスクがどのように記述されているかを見てみましょう。

<DTS:Executable
  DTS:refId="Package\SQL Test"
  DTS:CreationName="Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
  DTS:DTSID="{68414096-F08C-459C-BE58-D189B2787C95}"
  DTS:ExecutableType="Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
  DTS:LocaleID="-1"
  DTS:ObjectName="SQL Test"
  DTS:TaskContact="Execute SQL Task; Microsoft Corporation; Microsoft SQL Server 2008; c 2007 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1"
  DTS:ThreadHint="0">
  <DTS:Variables />
  <DTS:ObjectData>
    <SQLTask:SqlTaskData
      SQLTask:Connection="{69FEE415-5479-4181-BBD2-7D377785551E}"
      SQLTask:SqlStatementSource="SELECT PIYO FROM FUGA WHERE HOGE = ?"
      SQLTask:ResultType="ResultSetType_SingleRow" xmlns:SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask">
      <SQLTask:ResultBinding
        SQLTask:ResultName="0"
        SQLTask:DtsVariableName="User::PiyoValue" />
      <SQLTask:ParameterBinding
        SQLTask:ParameterName="0"
        SQLTask:DtsVariableName="User::Hoge"
        SQLTask:ParameterDirection="Input"
        SQLTask:DataType="130"
        SQLTask:ParameterSize="-1" />
    </SQLTask:SqlTaskData>
  </DTS:ObjectData>
</DTS:Executable>

色々と書いてありますが、重要なのはDTS:ObjectDataの中にある要素になります。TaskHost.Propertiesで設定できるのも、この部分です。

<DTS:ObjectData>
<SQLTask:SqlTaskData
  SQLTask:Connection="{69FEE415-5479-4181-BBD2-7D377785551E}"
  SQLTask:SqlStatementSource="SELECT PIYO FROM FUGA WHERE HOGE = ?"
  SQLTask:ResultType="ResultSetType_SingleRow" xmlns:SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask">
  <SQLTask:ResultBinding
    SQLTask:ResultName="0"
    SQLTask:DtsVariableName="User::PiyoValue" />
  <SQLTask:ParameterBinding
    SQLTask:ParameterName="0"
    SQLTask:DtsVariableName="User::Hoge"
    SQLTask:ParameterDirection="Input"
    SQLTask:DataType="130"
    SQLTask:ParameterSize="-1" />
</SQLTask:SqlTaskData>
</DTS:ObjectData>

これぐらいの粒度になればなんとなくわかってくると思います。

Connectionが接続マネージャーに当たる部分。GUIDが指定されています。これは接続マネージャーを作る時にIDを貰っておけば大丈夫そうです。

SqlStatementSourceは使用するSQLがそのまま書かれています。

ResultTypeはSingleRowとのことなので、単一行なのでしょう。

ResultBindingとParameterBindingは一個ずつしか指定していないのですが、このXMLの書き方からして、恐らく増やせば増やすほどSQLTask:Result/ParameterBinding要素がSQLTask:SqlTaskData内に増えていくのでしょう。

と言うわけで、何となく中身が見えてきたので早速コードを書いていきましょう。とりあえず前回のコードと併せて書けるとこまで書いてしまいます。

using Microsoft.SqlServer.Dts.Runtime;

namespace CreatePackage
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var package = new Package { Name = "ExecuteSQLTest" })
            {
                //変数の追加
                package.Variables.Add("PiyoValue", false, "User", string.Empty);
                package.Variables.Add("Hoge", false, "User", string.Empty);

                //接続マネージャーの追加
                using (var manager = package.Connections.Add("OLEDB"))
                {
                    manager.Name = "TestDB";
                    manager.ConnectionString = "Provider=SQLOLEDB.1;Data Source=(local);Initial Catalog=master;Integrated Security=SSPI;";

                    //SQL実行タスクを作成
                    using (var th = package.Executables.Add("STOCK:SQLTask") as TaskHost)
                    {
                        th.Name = "SQL Test";
                        th.Properties["Connection"].SetValue(th, manager.ID);
                        th.Properties["SqlStatementSource"].SetValue(th, "SELECT PIYO FROM FUGA WHERE HOGE = ?");
                    }
                }

                //DTSXとして出力
                new Application().SaveToXml(string.Format(@"C:\temp\{0}.dtsx", package.Name), package, null);

            }
        }
    }
}

Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTaskを参照設定に加え、結果セット（ResultType）を設定する

今までの傾向から見るに、こんなコードを書けば設定できそうです。

th.Properties["ResultType"].SetValue(th, "ResultSetType_SingleRow");

と思って実行してみると、DtsRuntimeExceptionが発生します。

DtsRuntimeException はハンドルされませんでした。
要素 “ResultType” がコレクション “Propeties” に存在しません。

実はこのTaskHostのProptetiesが持つ要素の名前は、各タスクのクラス、今回で言えばExecuteSQLTaskクラスのプロパティ名に依存しています。なので、ResultSetTypeが正しいプロパティ名になります。

th.Properties["ResultSetType"].SetValue(th, "ResultSetType_SingleRow");

じゃあこれでいけるだろ、と思ったら今度はArgumentExceptionが飛んできます。

ArgumentException はハンドルされませんでした。
型 ‘System.String’ のオブジェクトを型 ‘Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ResultSetType’ に変換できません。

どうやらここは文字列ではなくenumを渡してやらなきゃいけないみたいです。って言うかよく読んだらそう書いてあるわ。

と言うわけで、渡す値も各タスクのプロパティのリファレンスをちゃんと読まないといけないみたいです。とりあえずResultSetType列挙体を呼び出すためにMicrosoft.SqlServer.Dts.Tasks.ExecuteSQLTaskをusingに追加してやりましょう。参照設定に追加するMicrosoft.SqlServer.SQLTask.dllはC:\Windows\assembly\GAC_32 or 64配下にいるはずです。

正しいコードはこうなります。

th.Properties["ResultSetType"].SetValue(th, ResultSetType.ResultSetType_SingleRow);

ParameterBindingsプロパティを設定する

先ほどの反省を生かしてExecuteSQLTaskクラスのプロパティを見ていくとParameterBindingsプロパティなるものがあります。多分こいつでしょう。

中身はIDTSParameterBindingsインターフェイスとのことで、Addメソッドを呼ぶとIDTSParameterBindingインターフェイスが返ってきます。で、そのプロパティを見ていくと、先ほどDTSXで見たプロパティ名が並んでいることに気づきます。

なので、一旦TaskHostからParameterBindingsプロパティを受け取り、IDTSParameterBindingsにキャストしてAddメソッドを呼び、返ってきたIDTSParameterBindingのプロパティを設定していく、と言う流れになります。

var param = (th.Properties["ParameterBindings"].GetValue(th) as IDTSParameterBindings).Add();
param.ParameterName = "0";
param.DtsVariableName = "User::Hoge";
param.ParameterDirection = ParameterDirections.Input;
param.DataType = (int)System.Data.OleDb.OleDbType.WChar;
param.ParameterSize = -1;

DataTypeが若干厄介で、これは使用する接続マネージャーに依存します。ADO.NETであればSystem.Data.DbType、OLE DBであればSystem.Data.OleDb.OleDbTypeと言った風に、System.Data名前空間で使用しているものをintにキャストする必要があります。

DTSXに書いてある数字を参考にする場合はこの対応表が便利そうです。ADO DataType Enum Valueと書かれている数値がそれになります。（しかしこれ、元は何の資料なんだろう…？）

ResultSetBindingsプロパティを設定する

ParameterBindingsと同じ要領です。IDTSResultBindingsインターフェイスにキャストしてAddメソッドを呼んでIDTSResultBindingインターフェイスのプロパティを設定してやる。

var result = (th.Properties["ResultSetBindings"].GetValue(th) as IDTSResultBindings).Add();
result.ResultName = "0";
result.DtsVariableName = "User::PiyoValue";

まとめ

最終的にはこんな形になります。ParameterBindingsやResultSetBindingsはAddメソッドが呼ばれているので改めてどこかにセットする必要はありません。

using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;

namespace CreatePackage
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var package = new Package { Name = "ExecuteSQLTest" })
            {
                //変数の追加
                package.Variables.Add("PiyoValue", false, "User", string.Empty);
                package.Variables.Add("Hoge", false, "User", string.Empty);

                //接続マネージャーの追加
                using (var manager = package.Connections.Add("OLEDB"))
                {
                    manager.Name = "TestDB";
                    manager.ConnectionString = "Provider=SQLOLEDB.1;Data Source=(local);Initial Catalog=master;Integrated Security=SSPI;";

                    //SQL実行タスクを作成
                    using (var th = package.Executables.Add("STOCK:SQLTask") as TaskHost)
                    {
                        th.Name = "SQL Test";
                        th.Properties["Connection"].SetValue(th, manager.ID);
                        th.Properties["SqlStatementSource"].SetValue(th, "SELECT PIYO FROM FUGA WHERE HOGE = ?");
                        th.Properties["ResultSetType"].SetValue(th, ResultSetType.ResultSetType_SingleRow);

                        //パラメータ設定
                        var param = (th.Properties["ParameterBindings"].GetValue(th) as IDTSParameterBindings).Add();
                        param.ParameterName = "0";
                        param.DtsVariableName = "User::Hoge";
                        param.ParameterDirection = ParameterDirections.Input;
                        param.DataType = (int)System.Data.OleDb.OleDbType.WChar;
                        param.ParameterSize = -1;

                        //結果セット設定
                        var result = (th.Properties["ResultSetBindings"].GetValue(th) as IDTSResultBindings).Add();
                        result.ResultName = "0";
                        result.DtsVariableName = "User::PiyoValue";
                    }
                }

                //DTSXとして出力
                new Application().SaveToXml(string.Format(@"C:\temp\{0}.dtsx", package.Name), package, null);

            }
        }
    }
}

参考