SQL Server Integration Services（SSIS）初心者向け-パート2

→ パート1

→ パート3



このパートでは、Productsディレクトリをロードするロジックを変更します：

1. 「Union All」コンポーネントを使用して、2つの着信ストリームを1つに結合します。
2. 新しいレコードについては挿入を行い、既に追加されているレコードについては更新を行います。 レコードを追加と更新に分離するには、Lookupコンポーネントを使用します。
3. レコードを更新するには、「OLE DBコマンド」コンポーネントを使用します。

このパートの最後で、出力セットを並列化するためにマルチキャストコンポーネントを検討します。



このセクションでは、合計で、Union All、Lookup、OLE DB Command、およびMulticastの4つの新しいコンポーネントを紹介します。



さらに、多くの写真もあります。

SSISの継続的な紹介

新しいパッケージを作成します。







そして、名前を「 LoadProducts_ver2.dtsx 」に変更します。







[制御フロー]領域で、[データフロータスク]を作成します。







「データフロータスク」要素をダブルクリックし、その「データフロー」領域に移動します。 SourceAおよびSourceB接続用の2つの「ソースアシスタント」要素を作成します。 これらの要素の名前をそれぞれ「ソースA」と「ソースB」に変更します。







ソースAを次のように設定します。







リクエストテキスト：

SELECT ID SourceProductID, Title, Price FROM Products
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

優れた機能を一度に示すために、ここでは意図的にSourceIDをリリースしました。



ソースBを次のように設定します。





リクエストテキスト：

 SELECT ID SourceProductID, 'B' SourceID, Title, Price FROM Products
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

その結果、セットAには3つの列[SourceProductID、Title、Price]があり、セットBには4つの列[SourceProductID、SourceID、Title、Price]があります。



「 Union All 」要素を使用して、2つのセットのデータを1つに結合します。 「ソースA」と「ソースB」からの青い矢印を指すようにしましょう。







2つの受信セット間で列がどのように一致するかは、「Union All」要素をダブルクリックして確認できます。







ご覧のとおり、ここでは名前が一致する列の自動一致が行われます。 必要に応じて、マッピングを実行できます。たとえば、2番目のセットからSourceID列を追加します。







この場合、「ソースA」セットのSourceIDはNULLになります。

この場合の2つのセットの組み合わせは、SSIS側で行われます。 ソースデータベースと受信データベースは異なるサーバー/ SQL Serverのインスタンスに配置できるという事実に注意する価値があります。このため、UNIONまたはJOIN SQL操作を使用して、異なるデータベースのテーブルを使用してSQLクエリを常に作成できるとは限りません。 （以下で説明するLookupの代わりに使用できます）。

NULL値を「A」に置き換えるには、「Union All」からストリームを送信する「Derived Column」コンポーネントを使用します。







派生列エディターをダブルクリックして、次のように構成します。





次のことをしましょう（マウスを助けます）：

1. 「Derived Column」で値「Replace 'SourceID'」を示します-これは、出力で古いSourceID列を新しいものに置き換えることを意味します。
2. REPLACENULL関数を「式」領域にドラッグします。
3. SourceID列をREPLACENULL関数の最初の引数にドラッグします。
4. 2番目の引数として、定数「A」を記述します。

「Union All」を通過した後にデータに何が起こったのかを理解するために、「Union All」から「Derived Column」に向かう矢印の「Enable Data Viewer」を作成します。







実行時にパッケージを実行すると、結果として判明したセットを確認できます。





ここでは、この段階（派生列の前）で、最初のセットの行のSourceID列にNULL値が含まれていることがわかります。



エントリがDemoSSIS_Targetデータベースに以前に追加されたかどうかを判断するために、 Lookupコンポーネントを使用します。







それをダブルクリックすると、この要素が構成されます。







ここでは、一致するものが見つからない行について、「一致しない出力」ストリームにリダイレクトします。 この場合、 出力で2セットの「 一致一致出力 」と「 ルックアップ一致なし 出力 」を取得します 。

たとえば、「Ignore failure」という値を設定すると、TargetIDフィールドで一致がなかった行（下記参照）に値NULLが書き込まれ、すべての行が1組の「 一致一致出力 」を通じて返されます。

「フルキャッシュ」とは、1つのSQLクエリによって参照として使用されるセット（次のタブを参照）がメモリに完全にロードされ、SQL Serverを繰り返し呼び出すことなくキャッシュから行が一致することを意味します。

[部分キャッシュ]または[キャッシュなし]を選択すると、[詳細設定]タブで、着信セットの各行に一致するように実行されるパラメーターで要求を登録できます。 楽しみのために、このプロパティをいじって、SQL Server Profilerを介して、パッケージの実行時に生成されるクエリを確認できます。

次のタブで、ディレクトリとして機能するセットを定義する必要があります。







ここにリクエストを登録しました：

 SELECT SourceID, SourceProductID, ID TargetID FROM Products
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

次のタブでは、ディレクトリで行を検索するフィールドと、ディレクトリのどの列を出力セットに追加する必要があるかを指定する必要があります（必要な場合）。







関係を判別するには、マウスを使用してSourceProductIDフィールドをSourceProductIDにドラッグし、SourceIDフィールドをSourceIDにドラッグします。



「Destination Assistant」コンポーネントを追加して、「Lookup No Match Output」ストリームからレコードを挿入します。







青い矢印を「ルックアップ」から「OLE DB宛先」にドラッグし、ダイアログボックスで「ルックアップ一致しない出力」ストリームを選択します。







その結果、次の結果が得られます。







「OLE DB Destination」をダブルクリックして設定します：











新しいレコードを挿入する処理を行いました。



次に、以前に挿入されたレコードを更新するために、OLE DBコマンドコンポーネントを使用して、青い矢印をLookupからそれに移動します。







「Match Match Output」ストリームは、このコンポーネントに自動的に送られます。 先ほど選択した「Lookup No Match Output」ストリーム：







「OLE DBコマンド」をダブルクリックして設定します。











次の更新リクエストを作成します。

 UPDATE Products SET Title=?, Price=? WHERE ID=?
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

次のタブでは、着信セット「ルックアップ一致出力」の行のデータに基づいてパラメーターがどのように設定されるかを示します。







SSMSを使用して、DemoSSIS_SourceBデータベースに新しい製品を追加します。

 USE DemoSSIS_SourceB GO --    SET IDENTITY_INSERT Products ON INSERT Products(ID,Title,Price)VALUES (6,N'',NULL), (7,N'',NULL), (8,N' ',NULL) SET IDENTITY_INSERT Products OFF GO
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

データがどのように変更されたかを追跡するために、実行のためにパッケージを開始する前に、必要な場所で「Enable Data Viewer」を実行できます。







パッケージを実行して実行します：







その結果、OLE DB Destinationコンポーネントを使用して3行が挿入され、OLE DB Commandコンポーネントを使用して10行が更新されたことがわかります。



「OLE DBコマンド」で規定されているクエリは、着信セットの各行に対して実行されました。 この例では10回です。



「OLE DBコマンド」では、たとえば、タイトルまたは価格が変更されたかどうかを確認し、値のいずれかが異なる場合にのみ対応する行を更新するなど、TSQLでより複雑なロジックを記述できます。



明確にするため、DemoSSIS_TargetデータベースのProductsテーブルに新しい列を追加します。

 USE DemoSSIS_Target GO ALTER TABLE Products ADD UpdatedOn datetime GO
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

次のコマンドを書きましょう。







チームテキスト：

 DECLARE @TargetID int=? DECLARE @Title nvarchar(50)=? DECLARE @Price money=? IF(EXISTS( SELECT Title,Price FROM Products WHERE ID=@TargetID EXCEPT SELECT @Title,@Price ) ) BEGIN UPDATE Products SET Title=@Title, Price=@Price, UpdatedOn=GETDATE() WHERE ID=@TargetID END
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

また、これらすべてをストアドプロシージャの形式で配置し、「EXEC ProcName？、？、？」を呼び出して登録することもできます。 ここでは、誰にとっても便利だと思います。時には、すべてを1か所にまとめる方が便利な場合もあります。 SSISプロジェクト。 ただし、プロシージャを使用すると、利便性も得られます。この場合、プロシージャを変更するだけで、SSISプロジェクトの再作成と再展開を回避できます。

その後、コマンドテキストの順序に従ってパラメーターバインディングを再定義します。







DemoSSIS_SourceAデータベースを更新してみましょう。

 USE DemoSSIS_SourceA GO UPDATE Products SET Price=30 WHERE ID=2 -- 
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

また、実行時にプロジェクトを開始します。 その結果、実行のためにパッケージを次に開始した後、このレコードに対してのみ、UPDATEを1回だけ実行する必要があります。







パッケージの実行後、クエリを使用してこれを確認します。

 USE DemoSSIS_Target GO SELECT * FROM Products ORDER BY UpdatedOn DESC
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

このパートのフレームワークでは、 マルチキャストコンポーネントも検討します。 このコンポーネントを使用すると、1つのストリームから複数を取得できます。 これは、同じデータを2つ以上の異なる場所に記録する必要がある場合に役立ちます。 1つのセットが入りますが、必要なだけコピーが出され、このセットの各コピーで、私たちは何でもできます。



たとえば、DemoSSIS_Targetデータベースに別のLastAddedProductsテーブルを作成します。

 USE DemoSSIS_Target GO CREATE TABLE LastAddedProducts( SourceID char(1) NOT NULL, --     SourceProductID int NOT NULL, -- ID   Title nvarchar(50) NOT NULL, Price money, CONSTRAINT PK_LastAddedProducts PRIMARY KEY(SourceID,SourceProductID), CONSTRAINT CK_LastAddedProducts_SourceID CHECK(SourceID IN('A','B')) ) GO
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

このテーブルをクリアするには、「SQLフローの実行」コンポーネントを「制御フロー」エリアに追加し、「TRUNCATE TABLE LastAddedProducts」コマンドを記述します。











「データフロータスク」コンポーネントの「データフロー」領域に移動し、次のようにコンポーネントを追加します。







黄色の感嘆符に注意してください-これは、UpdatedOn列を追加し、バインドしなかったためです。 「OLE DB Destination」要素に移動し、「マッピング」タブに移動し、UpdatedOn列を入力フィールドとして無視して「OK」をクリックします。







別の「OLE DB Destination」要素を作成し、Multicast要素から2番目の青い矢印をその要素にドラッグします。







わかりやすくするために名前を変更します。







「To LastAddedProducts」を設定します。











最後の3つの挿入されたレコードをSSMSで削除します。

 USE DemoSSIS_Target GO DELETE Products WHERE SourceID='B' AND SourceProductID>=6
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

そして、パッケージを実行して実行します：







その結果、製品とLastAddedProductsの2つのテーブルで追加が行われました。

第二部の結論

このパートでは、小さなディレクトリを同期する方法を検討しました。 ここでは、もちろん、ソース内のデータがまだ削除できる瞬間は考慮されていませんが、必要に応じて、自分で試してみることができます。 削除するときは、追加の要素を考慮する必要がある場合があります。たとえば、他のテーブルから削除されたレコードへのリンクがある場合があります（次の部分ではこれを行う予定です）。

参照整合性に違反しないように、受信テーブルのエントリが論理的に削除される場合があります。たとえば、タイプビット（論理削除フラグ）の削除済みフィールドまたは日時タイプ（論理削除日時）のDeletedOnをこのテーブルに追加できます。

場合によっては、ターゲットデータベースが配置されているサーバー上に、補助的な中間データベースが作成され（通常はステージングと呼ばれます）、ソースからの「生の」データが最初にロードされます。 ターゲットとステージングは​​同じサーバー上にあるため、2番目のステップは、これらの両方のデータベースのセットを操作するSQLクエリ（SQL MERGEコンストラクトまたはJOINコンストラクトを使用するクエリなど）を簡単に記述することです。

SSISは非常に興味深いツールであり、場合によっては統合プロセスを大幅に簡素化できるので、私が思うに、このツールは武器庫に持っていても害はありません。 しかし、当然ながら、すべての重量を量る状況もあります。たとえば、リンクサーバーを使用して純粋なTSQLでプロシージャを記述したり、OOPのすべての機能を使用して他のプログラミング言語でユーティリティを記述したりするなど、他の方法を使用して統合を記述する方が合理的です。



教材を勉強するときは、好奇心を見せましょう。たとえば、表示しなかったタブをクリックし、情報を調べて分析し、矢印をクリックします。独自のプロパティと設定もあります。 あなたにとって興味深いと思われるすべてのものを試してみてください。小さなテストケースを怠ることはありません。 これが例外につながるようにスキームを変更し、この状況から最も適切な方法を見つけようとするコンポーネントにより適切なパラメーターを選択します。



ご清聴ありがとうございました！ 頑張って



→ パート3