Share via

  • [アーティクル]

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.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 接続マネージャでのパラメータの使用

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 の値が大きい製品と小さい製品を、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 ストアド プロシージャを実行します。このストアド プロシージャでは、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 デザイナで設定できる、パラメータとリターン コードのプロパティの詳細については、次のトピックを参照してください。

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

外部リソース

Integration Services のアイコン (小) 最新の Integration Services の入手

マイクロソフトが提供する最新のダウンロード、アーティクル、サンプル、ビデオ、およびコミュニティで選択されたソリューションについては、MSDN または TechNet の Integration Services のページを参照してください。

これらの更新が自動で通知されるようにするには、ページの RSS フィードを購読します。