SQL 実行タスクにおけるパラメーターとリターン コードの使用

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

注
SQL 実行タスクでは、データ プロバイダーがサポートしている場合のみ、パラメーターを使用できます。

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

SQL 実行タスクでパラメーターやリターン コードを使用すると、タスクでサポートされるパラメーターの型やこれらのパラメーターのマップ方法を把握するだけでなく、その他の情報も取得できます。SQL 実行タスクでパラメーターおよびリターン コードを正しく使用する際に必要となる、追加の使用要件やガイドラインがあります。以降では、こうした使用要件およびガイドラインについて説明します。

• パラメーター名とパラメーター マーカーの使用

• 日付と時刻のデータ型のパラメーターの使用

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

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

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

• SQL 実行タスクのパラメーターとリターン コードの構成

パラメーター名とパラメーター マーカーの使用

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.Person WHERE BusinessEntityID = ?
ADO.NET	@<parameter name>	@<parameter name>	SELECT FirstName, LastName, Title FROM Person.Person WHERE BusinessEntityID = @parmBusinessEntityID
ODBC	?	1, 2, 3, …	SELECT FirstName, LastName, Title FROM Person.Person WHERE BusinessEntityID = ?
EXCEL および OLE DB	?	0, 1, 2, 3, …	SELECT FirstName, LastName, Title FROM Person.Person WHERE BusinessEntityID = ?

ADO.NET 接続マネージャーおよび ADO 接続マネージャーでのパラメーターの使用

ADO.NET 接続マネージャーおよび ADO 接続マネージャーでは、パラメーターを使用する SQL コマンドに関する特定の要件があります。

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

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

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

EXCEL、ODBC、および OLE DB 接続マネージャーでのパラメーターの使用

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

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

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

OLE DB 接続マネージャーでのパラメーターの使用

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

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

日付と時刻のデータ型のパラメーターの使用

ADO.NET 接続マネージャーおよび ADO 接続マネージャーでの日付と時刻のパラメーターの使用

SQL Server 型 (time および datetimeoffset) のデータを読み取る場合、ADO.NET 接続マネージャーまたは ADO 接続マネージャーのいずれかを使用する SQL 実行タスクには、次の追加要件があります。

• time 型のデータの場合、ADO.NET 接続マネージャーでは、パラメーターの型が Input または Output で、データ型が string のパラメーターにこのデータを格納する必要があります。

• datetimeoffset 型のデータの場合、ADO.NET 接続マネージャーでは、次のいずれかのパラメーターにこのデータを格納する必要があります。

  • パラメーターの型が Input で、データ型が string のパラメーター。

  • パラメーターの型が Output または ReturnValue で、データ型が datetimeoffset、string、または datetime2 のパラメーター。データ型が string または datetime2 のパラメーターを選択した場合、Integration Services ではデータが string または datetime2 に変換されます。

• ADO 接続マネージャーでは、time 型または datetimeoffset 型のデータを、パラメーターの型が Input または Output で、データ型が adVarWchar のパラメーターに格納する必要があります。

SQL Server データ型と、それらが Integration Services データ型にどのようにマップされるかの詳細については、「データ型 (Transact-SQL)」および「Integration Services のデータ型」を参照してください。

OLE DB 接続マネージャーでの日付と時刻のパラメーターの使用

OLE DB 接続マネージャーを使用する場合、SQL 実行タスクには、SQL Server データ型 (date、time、datetime、datetime2、および datetimeoffset) のデータに関して特定のストレージ要件があります。このデータは、次のいずれかの型のパラメーターに格納する必要があります。

• NVARCHAR データ型の入力パラメーター。

• 次の表に示す、適切なデータ型の出力パラメーター。

  Output パラメーターの型	Date データ型
  DBDATE	date
  DBTIME2	time
  DBTIMESTAMP	datetime, datetime2
  DBTIMESTAMPOFFSET	datetimeoffset

データが適切な入力パラメーターまたは出力パラメーターに格納されないと、パッケージは失敗します。

ODBC 接続マネージャーでの日付と時刻のパラメーターの使用

ODBC 接続マネージャーを使用する場合、SQL 実行タスクには、SQL Server データ型 (date、time、datetime、datetime2、または datetimeoffset) のデータに関して特定のストレージ要件があります。このデータは、次のいずれかの型のパラメーターに格納する必要があります。

• SQL_WVARCHAR データ型の input パラメーター。

• 次の表に示す、適切なデータ型の output パラメーター。

  Output パラメーターの型	Date データ型
  SQL_DATE	date
  SQL_SS_TIME2	time
  SQL_TYPE_TIMESTAMP - または - SQL_TIMESTAMP	datetime, datetime2
  SQL_SS_TIMESTAMPOFFSET	datetimeoffset

データが適切な入力パラメーターまたは出力パラメーターに格納されないと、パッケージは失敗します。

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

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

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

次の表に、SELECT コマンドの例を接続マネージャーの種類別に示します。INSERT、UPDATE、および DELETE ステートメントでも同様です。この例では、SELECT を使用して、2 つのパラメーターで指定された値よりも ProductID の値が大きい製品と小さい製品を、AdventureWorks2008R2 の 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 コマンドの例を接続マネージャーの種類別に示します。この例では、AdventureWorks2008R2 の uspGetBillOfMaterials ストアド プロシージャを実行します。このストアド プロシージャでは、input パラメーター @StartProductID と @CheckDate を使用します。

接続の種類	EXEC 構文
EXCEL および OLEDB	EXEC uspGetBillOfMaterials ?, ?
ODBC	{call uspGetBillOfMaterials(?, ?)} ODBC の呼び出し構文の詳細については、MSDN ライブラリの ODBC プログラマ リファレンスにある「プロシージャのパラメーター」を参照してください。
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 コマンドの一部の例を接続の種類別に示します。すべての例で、input パラメーターを使用します。パラメーター マーカーとパラメーター名の使用方法に関する規則は、すべてのパラメーター型 (Input、Output、および ReturnValue) に適用される規則と同じです。

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

接続の種類	EXEC 構文
EXCEL および OLEDB	EXEC ? = myStoredProcedure 1
ODBC	{? = call myStoredProcedure(1)} ODBC の呼び出し構文の詳細については、MSDN ライブラリの ODBC プログラマ リファレンスにある「プロシージャのパラメーター」を参照してください。
ADO	IsQueryStoreProcedure が False に設定されている場合は EXEC ? = myStoredProcedure 1 IsQueryStoreProcedure が True に設定されている場合は myStoredProcedure
ADO.NET	IsQueryStoreProcedure が True に設定されている場合は myStoredProcedure

前の表に示した構文では、SQL 実行タスクは [直接入力] ソース タイプを使用してストアド プロシージャを実行します。SQL 実行タスクは [ファイル接続] ソース タイプを使用してストアド プロシージャを実行することもできます。SQL 実行タスクで [直接入力] または [ファイル接続] のどちらのソース タイプを使用するかに関係なく、ReturnValue 型のパラメーターを使用してリターン コードを実装します。SQL 実行タスクで実行される SQL ステートメントのソース タイプの構成方法の詳細については、「[SQL 実行タスク エディタ] ([全般] タブ)」を参照してください。

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

SQL 実行タスクのパラメーターとリターン コードの構成

SSIS デザイナーで設定できる、パラメーターとリターン コードのプロパティの詳細については、次のトピックを参照してください。

• [SQL 実行タスク エディタ] ([パラメータ マッピング] ページ)

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

• タスクまたはコンテナのプロパティを設定する方法

外部リソース

• blogs.msdn.com のブログ「Stored procedures with output parameters」

• msftisprodsamples.codeplex.com の CodePlex サンプル「Execute SQL Parameters and Result Sets」

最新の Integration Services の入手 Microsoft が提供する最新のダウンロード、アーティクル、サンプル、ビデオ、およびコミュニティで選択されたソリューションについては、MSDN または TechNet の Integration Services のページを参照してください。 MSDN の Integration Services のページを参照する TechNet の Integration Services のページを参照する これらの更新が自動で通知されるようにするには、ページの RSS フィードを定期受信します。

関連項目

概念

SSIS SQL 実行タスク

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