次の方法で共有

SQL 実行タスク

  • [アーティクル]

更新 : 2007 年 9 月 15 日

SQL 実行タスクは、パッケージ内の SQL ステートメントやストアド プロシージャを実行します。このタスクには、1 つの SQL ステートメントまたは順に実行される複数の SQL ステートメントを含めることができます。SQL 実行タスクは、次の目的で使用できます。

  • データを挿入する準備として、テーブルまたはビューを切り捨てます。
  • テーブル、ビューなどのデータベース オブジェクトを、作成、変更、および削除します。
  • データの読み込みを行う前にファクト テーブルとディメンション テーブルを再作成します。
  • ストアド プロシージャを実行します。
  • クエリから返された行セットを変数に保存します。

SQL 実行タスクは、次の方法で構成できます。

  • データベースへの接続に使用する接続マネージャの種類を指定します。
  • SQL ステートメントが返す結果セットの種類を指定します。
  • SQL ステートメントのタイムアウトを指定します。
  • SQL ステートメントの実行元を指定します。
  • SQL ステートメントの準備フェーズをタスクがスキップするかどうかを指定します。
  • ADO 接続の種類を使用している場合、SQL ステートメントがストアド プロシージャかどうかを指定する必要があります。その他の接続の種類では、このプロパティは読み取り専用であり、値は常に false です。

SQL 実行タスクを Foreach ループや For ループ コンテナと組み合わせて使用すると、複数の SQL ステートメントを実行できます。これらのコンテナには、パッケージ内での繰り返し制御フローが実装されているため、SQL 実行タスクを繰り返して実行できます。たとえば、Foreach ループ コンテナを使用すると、パッケージはフォルダ内のファイルを列挙して SQL 実行タスクを繰り返して実行し、各ファイルに格納された SQL ステートメントを実行します。

データ ソースへの接続

SQL 実行タスクでは、さまざまな種類の接続マネージャを使用して、SQL ステートメントまたはストアド プロシージャを実行するデータ ソースに接続できます。このタスクが使用できる接続の種類の一覧を、次の表に示します。

接続の種類 接続マネージャ

Excel

Excel 接続マネージャ

OLE DB

OLE DB 接続マネージャ

ODBC

ODBC 接続マネージャ

ADO

ADO 接続マネージャ

ADO.NET

ADO.NET 接続マネージャ

SQLMOBILE

SQL Server Compact Edition 接続マネージャ

SQL ステートメントの作成

このタスクでは、タスクのプロパティを SQL ステートメントの実行元として使用できます。タスクのプロパティには、ステートメント、単数または複数のステートメントが含まれるファイルへの接続、またはステートメントが含まれる変数名を含めることができます。SQL ステートメントは、実行元のデータベース管理システム (DBMS) の言語仕様に従って作成する必要があります。

SQL ステートメントがファイルに格納されている場合、タスクはファイル接続マネージャを使用してそのファイルに接続します。詳細については、「ファイル接続マネージャ」を参照してください。

SSIS デザイナでは、[SQL 実行タスク エディタ] ダイアログを使用して SQL ステートメントを入力できます。または、SQL クエリを作成するためのグラフィカル ユーザー インターフェイスである クエリ ビルダを使用することもできます。

ms141003.note(ja-jp,SQL.90).gifメモ :
有効な SQL ステートメントが SQL 実行タスクの外部に記述されている場合、SQL 実行タスクは解析に失敗することがあります。

複数のステートメントの一括送信

SQL 実行タスクに複数のステートメントが含まれる場合、それらをグループ化してバッチとして実行できます。バッチの開始と終了を知らせるには、GO コマンドを使用します。2 つの GO コマンド間にあるすべての SQL ステートメントは、OLE DB プロバイダにバッチで送信されて実行されます。SQL コマンドには、GO コマンドで分割された複数のバッチを含めることができます。

グループ化してバッチに含めることができる SQL ステートメントの種類には制限があります。詳細については、「ステートメントのバッチ」を参照してください。

SQL 実行タスクで SQL ステートメントのバッチを実行する場合、バッチには次の規則が適用されます。

  • 結果セットを返すことができるのは、バッチの最初のステートメント 1 つだけです。
  • 結果セットで結果のバインドを使用する場合、クエリは同じ数の列を返す必要があります。クエリが異なる数の列を返した場合、タスクは失敗します。ただし、タスクが失敗しても、そのタスクによって実行された DELETE や INSERT などのクエリは成功する場合があります。
  • 結果のバインドで列名を使用する場合、クエリは、タスクで使用される結果セットの名前と同じ名前の列を返す必要があります。列が存在しない場合、タスクは失敗します。
  • タスクでパラメータのバインドを使用する場合、バッチ内のすべてのクエリは、同じ数と種類のパラメータを持つ必要があります。

パラメータ化 SQL コマンドの実行

SQL ステートメントとストアド プロシージャでは多くの場合、入力パラメータ、出力パラメータ、およびリターン コードを使用します。SQL 実行タスクでは、Input、Output、および ReturnValue パラメータ型がサポートされます。入力パラメータには Input 型、出力パラメータには Output 型、およびリターン コードには ReturnValue 型を使用します。

ms141003.note(ja-jp,SQL.90).gifメモ :
SQL 実行タスクでは、データ プロバイダがサポートしている場合のみ、パラメータを使用できます。

クエリやストアド プロシージャなど、SQL コマンドのパラメータは、SQL 実行タスクのスコープ内、親コンテナまたはパッケージのスコープ内に作成されたユーザー定義変数にマップされます。変数の値はデザイン時に設定することも、実行時に動的に設定することもできます。パラメータは、システム変数にマップすることもできます。詳細については、「Integration Services の変数」および「システム変数」を参照してください。

SQL コマンドの構文では、SQL 実行タスクが使用する接続の種類によって、異なるパラメータ マーカーが使用されます。たとえば、ADO.NET 接続マネージャの場合は、SQL コマンドが使用するパラメータ マーカーの形式を @varParameter にする必要がありますが、OLE DB 接続の場合は疑問符 (?) パラメータ マーカーが必要です。

変数とパラメータの間でのマッピングでパラメータ名として使用できる名前も、接続マネージャの種類によって異なります。たとえば、ADO.NET 接続マネージャでは @ プレフィックス付きのユーザー定義名を使用し、OLE DB 接続マネージャでは、パラメータ名として 0 から始まる序数の数値を使用する必要があります。

次の表に、SQL 実行タスクで使用できる接続マネージャの種類の SQL コマンドの要件をまとめます。

接続の種類 パラメータ マーカー パラメータ名 SQL コマンドの例

ADO

?

Param1、Param2、…

SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?

ADO.NET

@<parameter name>

@<parameter name>

SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = @parmContactID

ODBC

?

1, 2, 3, …

SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?

EXCEL および OLE DB

?

0, 1, 2, 3, …

SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?

ADO.NET 接続マネージャとパラメータ

ADO.NET 接続マネージャの要件では、SQL コマンド内のパラメータ マーカーとしてパラメータ名を使用することが必要になります。これは、変数をパラメータに直接マップできることを意味します。たとえば、変数 @varName は、@parName という名前のパラメータにマップされ、パラメータ @parName に変数を提供します。

EXCEL、ODBC、および OLE DB 接続マネージャとパラメータ

EXCEL、ODBC、および OLE DB の各接続マネージャの要件では、SQL コマンド内のパラメータ マーカーとして疑問符 (?) を使用し、パラメータ名として 0 または 1 から始まる序数を使用することが必要になります。SQL 実行タスクで ODBC 接続マネージャが使用される場合、クエリの最初のパラメータにマップされるパラメータ名は 1 になります。それ以外の場合、パラメータ名は 0 になります。後続のパラメータ名の数値は、パラメータ名のマップ先である SQL コマンドのパラメータを示します。たとえば、3 というパラメータ名は、SQL コマンド内の 3 番目の疑問符 (?) で表される、3 番目のパラメータにマップされます。

パラメータに値を提供するプロセスで、変数がパラメータ名にマップされ、SQL 実行タスクがパラメータ名の序数値を使用して、変数からパラメータに値を読み込みます。

実行タスクが OLE DB 接続を使用する場合は、タスクの BypassPrepare プロパティを使用できます。SQL 実行タスクが、パラメータ付きの SQL ステートメントを使用する場合は、このプロパティを true に設定する必要があります。

SQL 実行タスクは OLE DB プロバイダ経由でパラメータ情報を取得することができないため、OLE DB 接続マネージャを使用する場合、パラメータ化されたサブクエリを使用することはできません。ただし、式を使ってパラメータの値をクエリ文字列に連結し、タスクの SqlStatementSource プロパティを設定することはできます。

接続マネージャが使用するプロバイダによっては、一部の OLE DB データ型がサポートされないことがあります。たとえば、Excel ドライバは限定されたデータ型のセットしか認識しません。Excel ドライバでの Jet プロバイダの動作の詳細については、「Excel ソース」を参照してください。

ADO 接続マネージャとパラメータ

ADO 接続マネージャの要件では、SQL コマンド内のパラメータ マーカーとして疑問符 (?) を使用することが必要になりますが、パラメータ名としては任意のユーザー定義名 (整数値を除く) を使用できます。

パラメータに値を提供するプロセスで、変数がパラメータ名にマップされ、SQL 実行タスクがパラメータ リスト内にあるパラメータ名の序数値を使用して、変数からパラメータに値を読み込みます。

WHERE 句でのパラメータの使用

SELECT、INSERT、UPDATE、および DELETE コマンドには、多くの場合、WHERE 句が含まれています。WHERE 句は、SQL コマンドを限定するために、ソース テーブル内の各行が満たすべき条件を定義したフィルタの役割を果たします。パラメータは、WHERE 句で使用されるフィルタ値を提供します。

パラメータ マーカーを使用して、パラメータ値を動的に指定できます。SQL ステートメントで使用できるパラメータ マーカーとパラメータ名に関する規則は、SQL 実行タスクで使用される接続マネージャの種類によって異なります。

次の表に、SELECT コマンドの例を接続マネージャの種類別に示します。INSERT、UPDATE、および DELETE ステートメントでも同様です。この例では、SELECT を使用して、2 つのパラメータで指定された値よりも ProductID の値が大きい製品と小さい製品を、AdventureWorks の Product テーブルから返します。

接続の種類 SELECT 構文

EXCEL、ODBC、OLEDB

SELECT* FROM Production.Product WHERE ProductId > ? AND ProductID < ?

ADO

SELECT* FROM Production.Product WHERE ProductId > ? AND ProductID < ?

ADO.NET

SELECT* FROM Production.Product WHERE ProductId > @parmMinProductID AND ProductID < @parmMaxProductID

この例では、次の名前のパラメータが必要になります。

  • EXCEL 接続マネージャと OLE DB 接続マネージャでは、パラメータ名 0 と 1 を使用します。ODBC 接続では、1 と 2 を使用します。
  • ADO 接続では、Param1 や Param2 など、任意の 2 つのパラメータ名を使用します。ただし、これらのパラメータ名は、パラメータ リストの序数位置によってマップされる必要があります。
  • ADO.NET 接続では、パラメータ名 @parmMinProductID と @parmMaxProductID を使用します。

ストアド プロシージャでのパラメータの使用

ストアド プロシージャを実行する SQL コマンドでは、パラメータ マッピングを使用することもできます。パラメータ マーカーとパラメータ名の使用方法に関する規則は、パラメータ化クエリの規則と同様に、SQL 実行タスクで使用される接続マネージャの種類によって異なります。

次の表に、EXEC コマンドの例を接続マネージャの種類別に示します。この例では、AdventureWorks の uspGetBillOfMaterials ストアド プロシージャを実行します。このストアド プロシージャでは、入力パラメータ @StartProductID と @CheckDate を使用します。

接続の種類 EXEC 構文

EXCEL および OLEDB

EXEC uspGetBillOfMaterials ?, ?

ODBC

{call uspGetBillOfMaterials(?, ?)}

ODBC 呼び出し構文の詳細については、MSDN ライブラリの ODBC プログラマ リファレンスの「プロシージャ パラメータ (Procedure Parameters)」のトピックを参照してください。

ADO

IsQueryStoredProcedure が False に設定されている場合は、EXEC uspGetBillOfMaterials ?, ?

IsQueryStoredProcedure が True に設定されている場合は、uspGetBillOfMaterials

ADO.NET

IsQueryStoredProcedure が False に設定されている場合は、EXEC uspGetBillOfMaterials @StartProductID, @CheckDate

IsQueryStoredProcedure が True に設定されている場合は、uspGetBillOfMaterials

出力パラメータを使用するには、構文で各パラメータ マーカーの後に OUTPUT キーワードを指定する必要があります。たとえば、EXEC myStoredProcedure ? OUTPUT のようにします。

Transact-SQL ストアド プロシージャでの入力パラメータと出力パラメータの使用の詳細については、「パラメータ (データベース エンジン)」、「OUTPUT パラメータを使用してデータを返す処理」、および「EXECUTE (Transact-SQL)」を参照してください。

リターン コードの値の取得

ストアド プロシージャは、リターン コードという整数値を返してプロシージャの実行状態を表すことができます。SQL 実行タスクにリターン コードを実装するには、ReturnValue 型のパラメータを使用します。

次の表に、リターン コードを実装する EXEC コマンドの一部の例を接続の種類別に示します。すべての例で、入力パラメータを使用します。ReturnValue パラメータでのパラメータ マーカーとパラメータ名の使用方法に関する規則は、Input や Output パラメータ型に適用される規則と同じです。

一部の構文では、パラメータのリテラルがサポートされません。その場合は、変数を使用してパラメータ値を指定する必要があります。

接続の種類 EXEC 構文

EXCEL および OLEDB

EXEC ? = myStoredProcedure 1

ODBC

{? = call myStoredProcedure(1)}

ODBC 呼び出し構文の詳細については、MSDN ライブラリの ODBC プログラマ リファレンスの「プロシージャ パラメータ (Procedure Parameters)」のトピックを参照してください。

ADO

IsQueryStoreProcedure が False に設定されている場合は、EXEC ? = myStoredProcedure 1

IsQueryStoreProcedure が True に設定されている場合は、myStoredProcedure

ADO.NET

IsQueryStoreProcedure が True に設定されている場合は、

myStoredProcedure

Transact-SQL ストアド プロシージャでのリターン コードの使用の詳細については、「リターン コードを使用したデータの返却」および「RETURN (Transact-SQL)」を参照してください。

結果セットの種類の指定

結果セットが SQL 実行タスクに返されるかどうかは、SQL コマンドの種類によって決まります。たとえば、通常、SELECT ステートメントは結果セットを返しますが、INSERT ステートメントは返しません。SELECT ステートメントからの結果セットに含まれる行数は、0 行、1 行、または多数行である場合があります。また、ストアド プロシージャは、プロシージャの実行状態を示すリターン コードという整数値を返すこともできます。この場合、結果セットは 1 行で構成されます。

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

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

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

SQL 実行タスクは、SQL ステートメントによって返され、まだ文字列になっていないすべての値を、文字列に変換します。たとえば、SQL Server の uniqueidentifier、bigint、decimal、または numeric データ型の値が、文字列に変換されます。

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

結果セットの種類が、単一行、行セット、または 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 データ型の列を含む結果セットを、数値データ型の変数にマップすることはできません。XML 結果セットをマップできるのは、String または Object データ型の変数のみです。変数が String データ型の場合、SQL 実行タスクは文字列を返し、XML ソースは XML データを使用できます。変数が Object データ型の場合、SQL 実行タスクはドキュメント オブジェクト モデル (DOM) オブジェクトを返します。完全な結果セットは、Object データ型の変数にマップする必要があります。結果は、行セット オブジェクトとして返されます。行セット オブジェクトを参照し、行セット内の列とデータに関する情報にアクセスするカスタム タスクを記述できます。

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

結果セットの種類 変数のデータ型 オブジェクトの種類

単一行

結果セット内の型列と互換性のあるすべての型

適用なし

完全な結果セット

Object

タスクで ADO、OLE DB、Excel、および ODBC 接続マネージャを含むネイティブ接続マネージャ使用する場合、返されるオブジェクトは ADO Recordset です。

タスクで ADO.NET 接続マネージャなどの管理された接続マネージャを使用する場合、返されるオブジェクトは System.Data.DataSet です。

XML

String

String

XML

Object

タスクで ADO、OLE DB、Excel、および ODBC 接続マネージャを含むネイティブ接続マネージャ使用する場合、返されるオブジェクトは MSXML6.IXMLDOMDocument です。

タスクで ADO.NET 接続マネージャなどの管理された接続マネージャを使用する場合、返されるオブジェクトは System.Xml.XmlDocument です。

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

変数を "単一行" 結果セットにマップした場合、SQL ステートメントによって返された文字列以外の値は文字列に変換されます。この変換が行われるかどうか、または、この変換が暗黙的に実行されるか明示的に実行されるかは、使用された接続マネージャの種類によって異なります。

  • ADO.NET 接続マネージャを使用した場合、変換は行われません。
  • ADO 接続マネージャまたは ODBC 接続マネージャを使用した場合、この変換は暗黙的に実行されます。
  • OLE DB 接続マネージャまたは Excel 接続マネージャを使用した場合、特定の型 (DBTYPE_I8、DBTYPE_UI8、DBTYPE_NUMERIC、DBTYPE_GUID、DBTYPE_BYTES) が接続マネージャによって明示的に文字列に変換されます。

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

SQL 実行タスクで使用できるカスタム ログ エントリ

次の表では、SQL 実行タスクのカスタム ログ エントリを説明します。詳細については、「パッケージへのログ機能の実装」および「ログ記録用のカスタム メッセージ」を参照してください。

ログ エントリ 説明

ExecuteSQLExecutingQuery

SQL ステートメントの実行フェーズに関する情報を提供します。タスクがデータベースへの接続を取得したとき、SQL ステートメントの準備が開始されたとき、および SQL ステートメントの実行が完了した後に、ログ エントリが書き込まれます。準備フェーズのログ エントリには、タスクで使用される SQL ステートメントが含まれます。

SQL 実行タスクのトラブルシューティング

Microsoft SQL Server 2005 Service Pack 2 (SP2) からは、SQL 実行タスクによる外部データ プロバイダの呼び出しをログに記録できるようになりました。この新しいログ機能を使用すると、SQL 実行タスクが実行する SQL コマンドに関するトラブルシューティングを行えます。SQL 実行タスクによる外部データ プロバイダの呼び出しのログを記録するには、パッケージ ログ記録を有効にして、パッケージ レベルで Diagnostic イベントを選択する必要があります。詳細については、「パッケージ実行のトラブルシューティング」を参照してください。

SQL コマンドまたはストアド プロシージャによって複数の結果セットが返される場合があります。これらの結果セットには、SELECT クエリの結果として得られた行セットだけでなく、RAISERROR ステートメントまたは PRINT ステートメントの結果として得られた単一の値も含まれます。ODBC 接続マネージャを除く、他のすべての接続マネージャでは、最初の結果セットよりも後に返された結果セットは無視されます。したがって、これらの接続マネージャでは、SQL のコマンドまたはストアド プロシージャによって返されたエラーは、最初の結果セットに含まれていない限り無視されます。

SQL 実行タスクの構成

プロパティはプログラムによって設定するか、または SSIS デザイナから設定できます。

SSIS デザイナで設定できるプロパティの詳細については、次のトピックのいずれかを参照してください。

SSIS デザイナでこれらのプロパティを設定する方法については、次のトピックを参照してください。

プログラムによる SQL 実行タスクの構成

プログラムによってこれらのプロパティを設定する方法の詳細については、次のトピックを参照してください。

参照

処理手順

クエリ パラメータを SQL 実行タスクの変数にマップする方法

概念

Foreach ループ コンテナ
For ループ コンテナ
Integration Services タスク
パッケージの制御フローの作成

その他の技術情報

SQL ステートメントの準備

ヘルプおよび情報

SQL Server 2005 の参考資料の入手

変更履歴

リリース 履歴

2007 年 9 月 15 日
変更内容 :
  • ODBC パラメータ構文の誤りを修正しました。
  • 変数を単一行の結果セットにマップすると、一部の戻り値が文字列に変換される旨の説明を追加しました。
  • 複数の結果セットで発生したエラーのトラブルシューティング情報を追加しました。

2006 年 12 月 12 日
追加内容 :
  • タスクによる外部データ プロバイダの呼び出しに関するトラブルシューティングのためにユーザーが使用できる新しいログ メッセージが、SQL Server 2005 SP2 に含まれているという情報を追加しました。

2006 年 7 月 17 日
変更内容 :
  • カスタム ログ エントリの表を追加しました。
  • 結果セットによって返されるオブジェクトの種類の一覧を示す列を、表に追加しました。
  • XML 文字列と XML オブジェクトの戻り値の型を分類しました。

2006 年 4 月 14 日
変更内容 :
  • 変数と結果セット間で可能なマッピングを示す表を追加しました。
  • 新しい BypassPrepare プロパティの情報を追加しました。

2005 年 12 月 5 日
変更内容 :
  • パラメータ化された SQL ステートメントの実行に関する情報を追加しました。