第 1 章 「DTS の概要」 ～ DTS 編 ～

  • [アーティクル]

NEC

E ラーニング事業部

鈴木   智行

2002 年 11 月 22 日

まえがき - 連載開始にあたって  

DTS (Data Transformation Service) は SQL Server 2000 の優れたサービスの 1 つです。

ただのデータ転送サービスと思われがちですが機能を知れば知るほど奥が深く、様々な業務を実現できることがわかるでしょう。

このコラムではその入口を紹介しますので、 DTS の世界に是非足を踏み入れてください。

目次

1. DTS とは 1. DTS とは
2. データ転送ツールとしての DTS 2. データ転送ツールとしての DTS
3. データ変換ツールとしての DTS 3. データ変換ツールとしての DTS
4. DTS タスク 4. DTS タスク
5. DTS パッケージと保存先 5. DTS パッケージと保存先
6. DTS パッケージの定期的な実行 6. DTS パッケージの定期的な実行

1. DTS とは

DTS (Data Transformation Service) とは SQL Server 7.0 から登場したデータ変換サービスであり、 SQL Server 2000 ではさらに機能強化されています。元々 DTS は SQL Server 用のツールというよりも Microsoft Data Warehousing Framework （ Microsoft 社が提唱するデータウェアハウス構築からデータ分析までのフレームワーク）   上でデータウェアハウスを構築するためのツールとして実現されたものです（図 1-1 ）。

図

図 1-1 DWH を構築するための DTS

構築フェーズで複数の場所にある様々な形で蓄積されたデータをデータウェアハウスとして集中化し、分析フェーズでデータウェアハウスやデータマートから切り出されたキューブを Excel の Pivot Table Service などクライアントアプリケーションからアクセスします。このようにデータウェアハウジングでは   「データ」   を   「情報」   として活用することで組織全体の意思決定能力を向上し、企業活動を優位に展開することが可能になります。この重要なビジネスニーズに対応するために DTS は大きな力を発揮しているのです。

だからといって   「データウェアハウジングは関係ないから DTS は必要ない」   というわけではありません。例えばある環境から別の環境にデータをコピーするという作業は、データ管理という意味でも今日のビジネス環境において一般的なプロセスの 1 つです。このように SQL Server を使用した通常のデータベースシステムでも DTS はその能力を最大限に発揮することで管理者にとっても非常に役立つツールとして活用することが可能です。

2. データ転送ツールとしての DTS

ある側面から見れば、 DTS はデータ転送ツールといってもいいでしょう。データ転送といえば、 SQL Server 2000 では DTS の他に BCP と BULK INSERT が挙げられます。

BCP はコマンドプロンプトユーティリティで SQL Server 4.21a から利用されています。当時は DB-Library を利用していましたが、現在は ODBC を利用して SQL Server とデータファイル間でコピーをすることができます。しかし他のデータベースと直接やりとりをすることはできません。間接的に 2 段階でコピーを行う必要があります   （ BCP でデータベースから一旦データファイルにダウンロードした後に BCP で目的のデータベースにアップロード）。また BCP には数多くの引数がありますが利用される方は最低限、表 2-1 に示される引数を理解しておいてください（大文字／小文字の区別あり）。

引数

説明

-S

サーバー名を指定（既定インスタンス）

-S

サーバー名 \ インスタンス名（名前付きインスタンス）を指定

-U

SQL Server 認証ログインを指定

-P

SQL Server 認証ログインのパスワードを指定

-T

Windows 認証ログインを使用するとみなされる

in/out

一括コピーする方向を示す

-T

フィールド終端文字を指定

-r

行終端文字を指定

表 2-1 BCP の引数

BULK INSERT は Transact-SQL ステートメントで SQL Server 7.0 から利用されています。 BCP ユーティリティの機能を利用して SQL Server にデータファイルからコピー   （インポート）   をすることができますが、 SQL Server からデータファイルにコピー   （エクスポート）   することはできません。 BULK INSERT にも数多くの引数がありますが利用される方は最低限、表 2-2 に示される引数を理解しておいてください。

引数

説明

FIELDTERMINATOR

フィールド終端文字を指定

ROWTERMINATOR

行終端文字を指定

DATAFILETYPE

コピー元データファイルの種類を指定

表 2-2 BULK INSERT の引数

DTS は OLE DB をサポートしているため、 BCP や BULK INSERT とは異なりデータファイルだけでなく様々な種類のデータとやりとりができます。そういった意味では DTS は BCP と BULK INSERT の機能を含んでいると言ってよいでしょう。 OLE DB は ODBC の後継の API であり、 SQL Server 2000 をインストールすると SQL Server や Oracle 用の OLE DB プロバイダなどをはじめとするいろいろな OLE DB プロバイダがインストールされ、 DTS などのアプリケーションは OLE DB プロバイダを使用してデータソースを使用することができます（表 2-3 ）。  

プロバイダ名

データソース

Microsoft OLE DB Provider for SQL Server

SQL Server 6.5 以降

Microsoft OLE DB Provider for Oracle Version 2.6

Oracle データベース

Microsoft OLE DB Provider for Jet Version 4.00

ACCESS データベース
Excel ワークシート

Microsoft OLE DB Provider for ODBC

ODBC データソース

Microsoft OLE DB Provider for OLAP Services

キューブ   （ Analysis Services ）

Microsoft OLE DB Provider for Microsoft Directory Services

Microsoft Windows 2000 Directory

表 2-3 OLE DB プロバイダ（抜粋）

3. データ変換ツールとしての DTS

特に異種データソース間でデータを移行する際には、単純なコピーだけで済むとは限りません。必要に応じてデータを抜き出したり、値を変更したり、計算結果を格納したりする必要もあるでしょう。こういったニーズに合わせて DTS はデータ転送だけではなく、単純なデータ変換から VBScript や Jscript を使ったロジックを組み込んで処理をおこなう複雑なデータ変換もサポートしています。例えば以下のような変換も可能です。

  • 複数の列を 1 つの列にまとめる

    姓と名の列を氏名の列に統合する（図 3-1 ）

    dts3-1.gif

    図 3-1 複数の列を 1 つにまとめる

  • データ形式を変更する

    　　性別を日本語に変更する（図 3-2 ）

    dts3-2.gif

    図 3-2 データ形式を変更する

  • データの整合性を図る

    　　所属会社の表現方法を統一する（図 3-3 ）

    dts3-3.gif

    図 3-3 データの整合性を図る

4. DTS タスク

データ転送／変換作業にはそれに付随していろいろなタスクが必要になる場合があります。例えば転送する前に転送先のデータベースやテーブルを作成したり、転送が終了したらメール送信したりすることなどです。 DTS ではそういったタスクをデータ転送／変換作業中に組み込むことが可能です。このタスクのことを DTS タスクと呼び、各タスクは実行する作業項目をデータ転送／変換のプロセスの一部として、または実行されるジョブとして定義することができます。 DTS タスクには表 4-1 に挙げられるような種類があります。

種類

説明

ファイル転送プロトコルタスク

リモート   サーバーまたはインターネット

上からデータ   ファイルをダウンロードします

ActiveX スクリプトタスク

DTS デザイナ内の他のタスクで利用できない関数を、スクリプト   コードを使用して実行します

データ変換タスク

多種多様な OLEDB 準拠のデータ   ソース間でポイント   ツー   ポイントのコピーおよび変換を行うことができます

プロセス実行タスク

実行可能なプログラムまたはバッチ   ファイルを実行します

SQL 実行タスク

SQL ステートメントを実行し、クエリの結果得られたデータを保存します

データドリブンクエリタスク

柔軟な Transact-SQL ベースのデータ操作を実行できます

SQL Server のオブジェクトのコピータスク

SQL Server オブジェクトを SQL Server のあるインスタンスから別のインスタンスにコピー、または作成します

メール送信タスク

タスクとしてメールを送信します

一括挿入タスク

大量のデータをテキストファイルから SQL Server のテーブルまたはビューにコピーします

パッケージ実行タスク

ほかの DTS パッケージを実行します

メッセージキュータスク

メッセージキューイングを使用して、 DTS パッケージ間でメッセージを送受信します

エラーメッセージ転送タスク

sp_addmessage システムストアドプロシージャで作成したユーザー定義エラーメッセージを SQL Server のあるインスタンスから別の SQL Server 2000 のインスタンスにコピーします

データベース転送タスク

SQL Server データベースを SQL Server のあるインスタンスから別の SQL Server 2000 のインスタンスに移動、コピーします

Master ストアドプロシージャ転送タスク

Master データベースのストアドプロシージャを SQL Server のあるインスタンスから別の SQL Server 2000 のインスタンスにコピーします

ジョブ転送タスク

ジョブを SQL Server のあるインスタンスから別の SQL Server 2000 のインスタンスにコピーします

ログイン転送タスク

ログインを SQL Server のあるインスタンスから別の SQL Server 2000 のインスタンスにコピーします

動的プロパティタスク

パッケージの実行時に DTS パッケージの外部にあるソースから値を取得し、選択されたパッケージ   プロパティに割り当てます

表 4-1 DTS タスクの種類

以上のように DTS タスクは様々あり、タスクを組み合わせることで管理作業を十分にカバーすることが可能になります。

5. DTS パッケージと保存先

DTS での作業は 1 回限りではなく、繰り返し行う場合もあるでしょう。このような場合には DTS での作業内容を登録し再利用することができます。この登録された DTS タスクの集合のことを DTS パッケージと呼びます。 DTS パッケージは以下の 4 つの保存先に保存できます。

  • 　SQL Server

    ほとんどのケースでは、この既定のオプションで問題ないでしょう。このオプションを選択すると DTS パッケージが msdb システムデータベースの sysdtspackages テーブルに BLOB （ Binary Large Object ）   データとして保存されます。 SQL Server Enterprise Manager では [ データ変換サービス ] - [ ローカルパッケージ ] にパッケージが保存されます。

    sysdtspackages テーブルでは自動的にバージョン管理がなされており、 SQL Server Enterprise Manager では複数のパッケージバージョンがある場合、対象パッケージを右クリックして [ バージョン ] メニューから、その履歴を表示し必要なバージョンを開くことができます   （画面 5-1 ）。

    dts5-1.gif

    画面 5-1 複数のパッケージバージョンから選択する (1)

    バージョンを選択しない場合には最新のバージョンが選択され、必要ないバージョンは個別に削除することが可能です。

  • 構造化ストレージファイル

    このオプションは SQL Server データベースにパッケージを格納せずに、 dts 拡張子がつけられたファイルとしてディスクに保存します。構造化ストレージファイルとして保存すれば簡単にネットワーク上でパッケージのコピー、移動、送信が可能です。構造化ストレージ形式でも複数のパッケージバージョンを 1 つのファイルに保存できます。 SQL Server Enterprise Manager では複数のパッケージバージョンがある場合、 [ データ変換サービス ] を右クリックし、 [ パッケージを開く ] メニューから必要なバージョンを開くことができます   （画面 5-2 ）。

    dts5-2.gif

    画面 5-2 複数のパッケージバージョンから選択する (2)

    必要ないバージョンは個別に削除することができません。個別に削除したいときは SQL Server に保存する場合とは違って、構造化ストレージファイルに保存する際は新しいファイル名をつけて保存することが必要です。

  • Visual Basic ファイル

    このオプションは SQL Server データベースにパッケージを格納せずに、 bas 拡張子がつけられた Visual Basic ファイルとしてディスクに保存します。 Visual Basic ファイルでは DTS パッケージが Visual Basic コードとして表現され   （画面 5-3 ）、 Visual Basic 開発環境を持っていれば簡単にコードをカスタマイズし、パッケージの定義を変更することが可能です。

    dts5-3.gif

    画面 5-3 Visual Basic ファイルとして保存された DTS パッケージ （一部）

    Visual Basic ファイルとして保存しても自動的にバージョンは設定されませんが、 Microsoft Visual SourceSafe を利用すればバージョン管理は可能です。

  • Meta Data Services

    このオプションはパッケージをリポジトリデータベースに格納します。リポジトリデータベースには型情報またはオブジェクト   インスタンス   データ、そしてオブジェクトのリレーションシップをマップしたり管理したりするためのテーブルが格納されており、既定ではリポジトリテーブル   （ Rtb××× ）   は msdb データベースで定義されています。このテーブルではデータ系列情報なども保存されるため、例えば各データの元の形とそのデータに適用された変換などを追跡することも可能です。もちろんバージョン履歴も保存され、 SQL Server Enterprise Manager の [ データ変換サービス ] - [ メタデータ ] でパッケージタブから参照することもできます   （画面 5-4 ）。

    dts5-4.gif

    画面 5-4 メタデータブラウザでバージョン情報を確認

6. DTS パッケージの定期的な実行

DTS パッケージとして保存すれば、もちろん手動実行することもできますが   （実行方法は第 2 章）、 SQL Server Agent と連携して定期的に実行することも可能です。例えば SQL Server に保存した場合の DTS パッケージは、 SQL Server Enterprise Manager の [ データ変換サービス ] - [ ローカルパッケージ ] から対象のパッケージを右クリックして、 [ パッケージのスケジュール設定 ] メニューから簡単に設定できます   （画面 6-1 ）。

画面

画面 6-1 DTS パッケージのスケジュール設定

スケジュール設定は SQL Server Agent が動作していなくても構いませんが、実行時には SQL Server Agent を開始しておく必要があります   （画面 6-2 ）。

dts6-2.gif

画面 6-2 スケジュール登録時に SQL Server Agent の開始は不要

登録後はジョブで管理されるため、スケジュール変更が必要な場合は [SQL Server エージェント ] - [ ジョブ ] から編集を行ってください   （画面 6-3 ）。

画面

画面 6-3 スケジュール登録後はジョブとして管理

次回の第 2 章では DTS パッケージを簡単に作成できる DTS インポート／エクスポートウィザードを中心にご紹介します。

dts.jpg

鈴木 智行: NEC E ラーニング事業部に所属。   入社以来、インストラクタとして教育業務に従事。汎用機、 UNIX を経て、 1994 年より   マイクロソフト認定トレーナー (MCT) として、管理者向け教育を担当。 SQL Server は 4.21a から携わっており、現在は主に SQL Server 2000 に関わるデータベース教育を中心に担当。 Windows 2000 および SQL Server 2000 での MCSA , MCSE , MCDBA を取得しており、情報処理技術者試験のテクニカルエンジニア   （データベース）   も取得済。最近は MCA の 3 科目   （データベース、 OS ／ネットワーク、アプリケーション構築）   全てに合格し、 C# を勉強中。