SQL 実行タスクにおける結果セット

[アーティクル]
09/14/2014

Integration Services パッケージでは、タスクが使用する SQL コマンドの種類によって、SQL 実行タスクに結果セットが返されるかどうかが決まります。たとえば、通常、SELECT ステートメントは結果セットを返しますが、INSERT ステートメントは返しません。

結果セットに含まれる内容も、SQL コマンドによって異なります。たとえば、SELECT ステートメントからの結果セットに含まれる行数は、0 行、1 行、または多数行である場合があります。ただし、カウントや合計を返す SELECT ステートメントの結果セットに含まれるのは 1 行だけです。

SQL 実行タスクで結果セットを使用すると、SQL コマンドが結果セットを返すかどうかを確認したり、その結果セットの内容を把握するだけでなく、その他の情報も取得できます。 SQL 実行タスクで結果セットを正しく使用する際に必要となる、追加の使用要件やガイドラインがあります。以降では、こうした使用要件およびガイドラインについて説明します。

結果セットの種類の指定
結果セットによる変数の設定
SQL 実行タスクにおける結果セットの構成

結果セットの種類の指定

SQL 実行タスクでサポートされている結果セットの種類は、次のとおりです。

"なし" は、クエリが結果を返さない場合に使用される結果セットです。たとえば、テーブルのレコードを追加、変更、および削除するクエリで使用されます。
"単一行" は、クエリが返す行が 1 行のみの場合に使用される結果セットです。たとえば、カウントや合計を返す SELECT ステートメントでこの結果セットが使用されます。
"完全な結果セット" は、クエリが複数行を返す場合に使用される結果セットです。たとえば、テーブル内のすべての行を取得する SELECT ステートメントでこの結果セットが使用されます。
"XML" は、クエリが結果セットを XML 形式で返す場合に使用される結果セットです。たとえば、FOR XML 句を含む SELECT ステートメントでこの結果セットが使用されます。

SQL 実行タスクが "完全な結果セット" の結果セットを使用し、クエリが複数の行セットを返す場合、タスクは最初の行セットのみを返します。この行セットでエラーが発生すると、タスクはそのエラーをレポートします。他の行セットでエラーが発生しても、タスクはエラーをレポートしません。

結果セットによる変数の設定

結果セットの種類が、単一行、行セット、または XML の場合、クエリが返す結果セットをユーザー定義の変数にバインドできます。

結果セットの種類が "単一行" の場合、列名を結果セットの名前として使用し、返される結果の列を変数にバインドしたり、列一覧の列の序数を結果セットの名前として使用できます。たとえば、クエリ SELECT Color FROM Production.Product WHERE ProductID = ? の結果セットの名前は Color または 0 となります。クエリが複数の列を返す場合に、すべての列の値にアクセスするには、各列を異なる変数にバインドする必要があります。数字を結果セットの名前として使用し、列を変数にマップする場合、その数字はクエリの列一覧に列が表示される順序を示します。たとえば、クエリ SELECT Color, ListPrice, FROM Production.Product WHERE ProductID = ? では、Color 列に 0 を、ListPrice 列に 1 を使用します。列名を結果セットの名前として使用できるかどうかは、タスクの構成で指定されているプロバイダーによって異なります。すべてのプロバイダーで列名が使用できるわけではありません。

単一の値を返す一部のクエリには、列名が含まれないことがあります。たとえば、ステートメント SELECT COUNT (*) FROM Production.Product からは列名が返されません。返される結果にアクセスするには、結果名として序数位置 0 を使用します。列名で返される結果にアクセスするには、列名を指定する AS <alias name> 句をクエリに含める必要があります。ステートメント SELECT COUNT (*)AS CountOfProduct FROM Production.Product では、CountOfProduct 列を指定します。その後、CountOfProduct 列名または序数位置 0 を使用して、返された結果列にアクセスできます。

結果セットの種類が "完全な結果セット" または "XML" の場合、結果セット名には 0 を使用する必要があります。

結果セットの種類が "単一行" の結果セットに変数をマップする場合、変数のデータ型と結果セットに含まれる列のデータ型は互換性がある必要があります。たとえば、String データ型の列を含む結果セットを、数値データ型の変数にマップすることはできません。 TypeConversionMode プロパティを Allowed に設定すると、SQL 実行タスクは出力パラメーターとクエリ結果を結果が割り当てられている変数のデータ型に変換します。

XML 結果セットをマップできるのは、String または Object データ型の変数のみです。変数が String データ型の場合、SQL 実行タスクは文字列を返し、XML ソースは XML データを使用できます。変数が Object データ型の場合、SQL 実行タスクはドキュメントオブジェクトモデル (DOM) オブジェクトを返します。

完全な結果セットは、Object データ型の変数にマップする必要があります。結果は、行セットオブジェクトとして返されます。 Foreach ループコンテナーを使用することによって Object 変数に格納されているテーブル行の値をパッケージ変数に抽出した後、スクリプトタスクを使用することによって、パッケージ変数に格納されたデータをファイルに書き出すことができます。そのために Foreach ループコンテナーとスクリプトタスクを使用する方法については、msftisprodsamples.codeplex.com の CodePlex サンプル「Execute SQL Parameters and Result Sets」を参照してください。

次の表は、結果セットにマップできる変数のデータ型をまとめたものです。

結果セットの種類	変数のデータ型	オブジェクトの種類
単一行	結果セット内の型列と互換性のあるすべての型	適用なし
完全な結果セット	Object	タスクで ADO、OLE DB、Excel、および ODBC 接続マネージャーを含むネイティブ接続マネージャー使用する場合、返されるオブジェクトは ADO Recordset です。タスクで ADO.NET 接続マネージャーなどのマネージ接続マネージャーを使用する場合、返されるオブジェクトは System.Data.DataSet です。次の例に示すように、スクリプトタスクを使用して、System.Data.DataSet オブジェクトにアクセスできます。 `Dim dt As Data.DataTable Dim ds As Data.DataSet = CType(Dts.Variables("Recordset").Value, DataSet) dt = ds.Tables(0)`
XML	String	String
XML	Object	タスクで ADO、OLE DB、Excel、および ODBC 接続マネージャーを含むネイティブ接続マネージャー使用する場合、返されるオブジェクトは MSXML6.IXMLDOMDocument です。タスクで ADO.NET 接続マネージャーなどのマネージ接続マネージャーを使用する場合、返されるオブジェクトは System.Xml.XmlDocument です。

変数は、SQL 実行タスクまたはパッケージのスコープ内で定義できます。変数にパッケージスコープがある場合、結果セットはパッケージ内の他のタスクやコンテナーで利用できます。また、パッケージ実行タスクや DTS 2000 パッケージ実行タスクが実行する任意のパッケージでも利用できます。

変数を "単一行" の結果セットにマップすると、次の条件が満たされている場合、SQL ステートメントによって返される、文字列以外の値が、文字列に変換されることがあります。

TypeConversionMode プロパティが true に設定されている。プロパティ値は、プロパティウィンドウで設定するか、SQL 実行タスクエディターを使用して設定します。
変換によってデータが切り捨てられない。

結果セットを変数に読み込む方法の詳細については、「結果セットを SQL 実行タスクの変数にマップする」を参照してください。