変更データのクエリを準備する

[アーティクル]
05/23/2023

適用対象: SQL Server Azure Data Factory の SSIS 統合ランタイム

変更データの増分読み込みを実行する Integration Services パッケージの制御フローにおいて、3 番目に行う最後のタスクは、変更データのクエリを準備してデータフロータスクを追加することです。

注意

制御フローの 2 番目のタスクは、選択した間隔の変更データが準備できていることを確認することです。このタスクの詳細については、「データの変更の準備ができているかどうかを判断する」を参照してください。制御フローをデザインするプロセス全体の説明については、「変更データキャプチャ (SSIS)」を参照してください。

デザインに関する考慮事項

変更データを取得するには、間隔のエンドポイントを入力パラメーターとして受け取り、指定した間隔の変更データを返す Transact-SQL テーブル値関数を呼び出します。この関数は、データフローの変換元コンポーネントによって呼び出されます。この変換元コンポーネントに関する詳細については、「変更データを取得および理解する」を参照してください。

OLE DB ソース、ADO ソース、ADO NET ソースなどの最もよく使用される Integration Services 変換元コンポーネントでは、テーブル値関数のパラメーター情報を取得できません。したがって、ほとんどの変換元では、パラメーター化された関数を直接呼び出すことはできません。

入力パラメーターを関数に渡すには、次の 2 つのデザイン方法があります。

パラメーター化クエリを文字列として作成します。スクリプトタスクまたは SQL 実行タスクを使用して、文字列にハードコーディングされたパラメーター値で動的 SQL 文字列を作成できます。その後、この文字列をパッケージ変数に格納したものを使用して、変換元コンポーネントの SqlCommand プロパティを設定できます。変換元コンポーネントでパラメーター情報が不要になるので、この方法は有効です。

注意

発生するオーバーヘッドは、SQL 実行タスクよりも事前コンパイルしたスクリプトの方が少なくなります。
パラメーター化されたラッパーを使用します。パラメーター化されたテーブル値関数を呼び出すラッパーとして、パラメーター化されたストアドプロシージャを作成できます。変換元コンポーネントでストアドプロシージャのパラメーター情報を正常に取得できるので、この方法は有効です。

ここでは最初のデザイン方法を使用し、パラメーター化クエリを文字列として作成します。

クエリの準備

入力パラメーターの値を 1 つのクエリ文字列に連結する前に、クエリで必要なパッケージ変数を設定する必要があります。

パッケージ変数を設定するには

SQL Server Data Tools (SSDT) の [変数] ウィンドウで、SQL 実行タスクによって返されるクエリ文字列を格納する文字列データ型の変数を作成します。

この例では、SqlDataQuery という名前の変数を使用します。

パッケージ変数を作成したら、スクリプトタスクまたは SQL 実行タスクを使用して入力パラメーターの値を連結できます。次の 2 つの手順では、このコンポーネントを構成する方法について説明します。

スクリプトタスクを使用してクエリ文字列を連結するには

[制御フロー] タブで、スクリプトタスクをパッケージの For ループコンテナーの後に追加し、For ループコンテナーをこのタスクに連結します。

注意

この手順では、パッケージによって 1 つのテーブルから増分読み込みが実行されることを前提としています。複数のテーブルから読み込みが実行され、パッケージに親パッケージと複数の子パッケージが存在する場合、このタスクは最初のコンポーネントとして各子パッケージに追加されます。詳細については、「複数のテーブルの増分読み込みを実行する」を参照してください。
[スクリプトタスクエディター]の [スクリプト] ページで、次のオプションを選択します。
1. [ReadOnlyVariables]で [User::DataReady]、 [User::ExtractStartTime]、および [User::ExtractEndTime] を一覧から選択します。
2. [ReadWriteVariables]で [User::SqlDataQuery] を一覧から選択します。
[スクリプトタスクエディター]の [スクリプト] ページで、 [スクリプトの編集] をクリックしてスクリプト開発環境を開きます。

Main プロシージャに、次のいずれかのコードセグメントを入力します。

C# でプログラミングしている場合は、次のコード行を入力します。

int dataReady;  
System.DateTime extractStartTime;  
System.DateTime extractEndTime;  
string sqlDataQuery;  

dataReady = (int)Dts.Variables["DataReady"].Value;  
extractStartTime = (System.DateTime)Dts.Variables["ExtractStartTime"].Value;  
extractEndTime = (System.DateTime)Dts.Variables["ExtractEndTime"].Value;  

if (dataReady == 2)  
  {  
  sqlDataQuery = "SELECT * FROM CDCSample.uf_Customer('" + string.Format("{0:yyyy-MM-dd hh:mm:ss}", extractStartTime) + "', '" + string.Format("{0:yyyy-MM-dd hh:mm:ss}", extractEndTime) + "')";  
  }  
else  
  {  
  sqlDataQuery = "SELECT * FROM CDCSample.uf_Customer(null" + ", '" + string.Format("{0:yyyy-MM-dd hh:mm:ss}", extractEndTime) + "')";  
  }  

Dts.Variables["SqlDataQuery"].Value = sqlDataQuery;

- または -

Visual Basicでプログラミングしている場合は、次のコード行を入力します。

Dim dataReady As Integer  
Dim extractStartTime As Date  
Dim extractEndTime As Date  
Dim sqlDataQuery As String  

dataReady = CType(Dts.Variables("DataReady").Value, Integer)  
extractStartTime = CType(Dts.Variables("ExtractStartTime").Value, Date)  
extractEndTime = CType(Dts.Variables("ExtractEndTime").Value, Date)  

If dataReady = 2 Then  
  sqlDataQuery = "SELECT * FROM CDCSample.uf_Customer('" & _  
      String.Format("{0:yyyy-MM-dd hh:mm:ss}", extractStartTime) & _  
      "', '" & _  
      String.Format("{0:yyyy-MM-dd hh:mm:ss}", extractEndTime) & _  
      "')"  
Else  
  sqlDataQuery = "SELECT * FROM CDCSample.uf_Customer(null" & _  
      ", '" & _  
      String.Format("{0:yyyy-MM-dd hh:mm:ss}", extractEndTime) & _  
      "')"  
End If  

Dts.Variables("SqlDataQuery").Value = sqlDataQuery

スクリプトの実行から DtsExecResult.Success を返す既定のコード行はそのまま使用します。
スクリプト開発環境と [スクリプトタスクエディター]を閉じます。

SQL 実行タスクを使用してクエリ文字列を連結するには

[制御フロー] タブで、SQL 実行タスクをパッケージの For ループコンテナーの後に追加し、For ループコンテナーをこのタスクに連結します。

注意

この手順では、パッケージによって 1 つのテーブルから増分読み込みが実行されることを前提としています。複数のテーブルから読み込みが実行され、パッケージに親パッケージと複数の子パッケージが存在する場合、このタスクは最初のコンポーネントとして各子パッケージに追加されます。詳細については、「複数のテーブルの増分読み込みを実行する」を参照してください。
[SQL 実行タスクエディター]の [全般] ページで、次のオプションを選択します。
1. [ResultSet]で [単一行]を選択します。
2. ソースデータベースへの有効な接続を構成します。
3. [SQLSourceType]で [直接入力]を選択します。
4. [SQLStatement]に、次の SQL ステートメントを入力します。
```
declare @ExtractStartTime datetime,  
@ExtractEndTime datetime,   
@DataReady int  

select @DataReady = ?,   
@ExtractStartTime = ?,   
@ExtractEndTime = ?  

if @DataReady = 2  
select N'select * from CDCSample.uf_Customer'  
+ N'('''+ convert(nvarchar(30),@ExtractStartTime,120)  
+ ''', '''  
+ convert(nvarchar(30),@ExtractEndTime,120) + ''') '   
as SqlDataQuery  
else  
select N'select * from CDCSample.uf_Customer'  
+ N'(null, '''  
+ convert(nvarchar(30),@ExtractEndTime,120)  
+ ''') '  
as SqlDataQuery  
```
  注意
  
  このサンプルの else 句では、開始日時として NULL 値を渡すことによって変更データの最初の読み込みのクエリが生成されます。このサンプルは、変更データキャプチャを有効にする前に行われた変更もデータウェアハウスにアップロードする必要があるシナリオには対応していません。
[SQL 実行タスクエディター] の [パラメーターマッピング]ページで、次のマッピングを行います。
1. DataReady 変数をパラメーター 0 にマップします。
2. ExtractStartTime 変数をパラメーター 1 にマップします。
3. ExtractEndTime 変数をパラメーター 2 にマップします。
[SQL 実行タスクエディター] の [結果セット]ページで、結果名を SqlDataQuery 変数にマップします。

結果名は、返される単一列の名前 SqlDataQuery になります。

上記の手順で、ハードコーディングされた入力パラメーターの文字列値を使用してクエリ文字列を準備するタスクを構成しました。次に、このクエリ文字列のコード例を示します。

select * from CDCSample. uf_Customer('2007-06-11 14:21:58', '2007-06-12 14:21:58')

データフロータスクの追加

パッケージの制御フローをデザインする最後の手順として、データフロータスクを追加します。

データフロータスクを追加して制御フローを完成させるには

[制御フロー] タブで、データフロータスクを追加し、クエリ文字列を連結したタスクを連結します。

次の手順

クエリ文字列を準備してデータフロータスクを構成したら、次にデータベースから変更データを取得するテーブル値関数を作成します。

次のトピック: 変更データを取得する関数を作成する

次の方法で共有

変更データのクエリを準備する

デザインに関する考慮事項

クエリの準備

パッケージ変数を設定するには

スクリプトタスクを使用してクエリ文字列を連結するには

SQL 実行タスクを使用してクエリ文字列を連結するには

データフロータスクの追加

データフロータスクを追加して制御フローを完成させるには

次の手順

フィードバック

その他のリソース

次の方法で共有

変更データのクエリを準備する

デザインに関する考慮事項

クエリの準備

パッケージ変数を設定するには

スクリプト タスクを使用してクエリ文字列を連結するには

SQL 実行タスクを使用してクエリ文字列を連結するには

データ フロー タスクの追加

データ フロー タスクを追加して制御フローを完成させるには

次の手順

フィードバック

その他のリソース

スクリプトタスクを使用してクエリ文字列を連結するには

データフロータスクの追加

データフロータスクを追加して制御フローを完成させるには