【SSIS】カスタムオブジェクトの作成と配置
SSISで使用するタスクやコンポーネントは自作することが出来ます。
よく使う処理なんだけど通常のタスク/コンポーネントでは実現できず、毎回スクリプトを書かなきゃいけないような処理は自作を検討してみるのも一つの手段です。
ただし、自作のタスク/コンポーネントはDLLを規定のフォルダに設置し、GACに登録しなくてはなりません。つまり、開発者に配布するコストや、実行環境に設置するコストも考慮しなくてはなりません。
要は本格的に使うならインストーラも用意しないと後々面倒だよ、ってことです。
実行環境
わざわざ書くのは、インストールされているWindowsのバージョンとかSQL Serverのバージョンとか.NETのバージョンとかVisual Studioのバージョンによって設置先のディレクトリが変わる可能性があるからです。
- Windows 7 Professional SP1
- .NET 4.0
- SQL Server 2012
- Visual Studio 2010 Professional(Version 4.0.30319 SP1Rel)
自作できるカスタムオブジェクト
とりあえずドキュメントを読みましょう。TechNetにIntegration Services 用のカスタム オブジェクトの開発と言うドキュメントがあるので、それを参考にします。
自作できるオブジェクトは以下の5つです。
- タスク
- 接続マネージャー
- ログ プロバイダー
- 列挙子
- データフローコンポーネント
それぞれMicrosoft.SqlServer.Dts.Runtime 名前空間にある基本クラスを継承し、同名前空間にある属性(Attribute)を設定する必要があります。また、必要に応じてMicrosoft.SqlServer.Dts.Runtime.Design 名前空間の基本クラスやインターフェースを継承/実装することで、編集時のUIを制御することも可能です。
TechNetにもまとまった表があるのですが、一応まとめておきます。
| オブジェクト | 基本クラス | 属性 | UI基本クラス | デフォルトUI |
|---|---|---|---|---|
| タスク | Task | DtsTaskAttribute | IDtsTaskUI | [プロパティ]ウィンドウのみ |
| 接続マネージャー | ConnectionManagerBase | DtsConnectionAttribute | IDtsConnectionManagerUI | [プロパティ]ウィンドウのみ |
| ログ プロバイダー | LogProviderBase | DtsLogProviderAttribute | IDtsLogProviderUI | [構成] 列のテキスト ボックス |
| 列挙子 | ForEachEnumerator | DtsForEachEnumeratorAttribute | ForEachEnumeratorUI | [プロパティ] ウィンドウのみ |
| データフローコンポーネント | PipelineComponent | DtsPipelineComponentAttribute | IDtsComponentUI | 詳細エディター |
今回はデータフローコンポーネントを自作してみようと思います。いきなり複雑なことをやっても混乱するだけなので、「データの行数に応じて行番号を付与するコンポーネント」を作ってみます。
参照設定
このドキュメントによると、とりあえず以下のdllがあればよさそうです。
- Microsoft.SqlServer.PipelineHost
- Microsoft.SqlServer.DTSPipelineWrap
- Microsoft.SQLServer.ManagedDTS
- Microsoft.SqlServer.DTSRuntimeWrap
私の環境だと「C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies」配下にいました。SQL Serverのバージョンによっては100にいたりするかもしれません。
あ、言い忘れてましたが、作成するプロジェクトの種類はクラスライブラリです。
Attributeの設定
とりあえず必要なものを参照設定にいれ、適当にusingでインポートし、PipelineComponentを継承してみましょう。
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
namespace RowCounter
{
public class RowCounterComponent : PipelineComponent
{
}
}
次に、SSISツールボックスで表示される情報をDtsPipelineComponentAttributeで設定します。
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
namespace RowCounter
{
[DtsPipelineComponentAttribute(ComponentType = ComponentType.Transform
, DisplayName = "行番号"
, Description = "行番号を付与します。"
, NoEditor = true)]
public class RowCounterComponent : PipelineComponent
{
}
}
特にどのプロパティが必須、と言うことも書いてないんですが、使用例を見る限りDisplayNameとComponentTypeぐらいには何か入れておいた方が良さそうです。
IconResourceとかなくてもいいのかな？と思ったら、謎のデフォルトアイコンが設定されました。UIも自作する場合はUITypeNameにアセンブリの修飾名を指定する必要があります。今回は別にいらないので、指定しません。
また、今回は特に詳細エディターを表示したくない、むしろ表示させたくないので、NoEditorプロパティをtrueにしています。
ProvideComponentPropertiesの実装
さてさて。ここからが地獄です。
まずはProvideComponentPropertiesメソッドを実装します。
このメソッドはデータフローに追加された瞬間に呼ばれます。このドキュメントに実装の手引きとサンプルコードが書いてあるので引用してみましょう。
public override void ProvideComponentProperties()
{
/// TODO: Reset the component.
/// TODO: Add custom properties.
/// TODO: Add input objects.
/// TODO: Add output objects.
}
ふざけるなァ！
普通にProvideComponentPropertiesメソッドのドキュメントを読んだほうがいいです。こっちの使用例はちゃんと例になっています。
public override void ProvideComponentProperties()
{
// Reset the component.
base.RemoveAllInputsOutputsAndCustomProperties();
IDTSInput100 input = ComponentMetaData.InputCollection.New();
input.Name = "Input";
IDTSOutput100 output = ComponentMetaDAta.OutputCollection.New();
output.Name = "Output";
output.SynchronousInputID = input.ID;
IDTSCustomProperty100 property = ComponentMetaData.CustomPropertyCollection.New();
property.Name = "RuntimeVariable";
property.Description = "Identifies a Variable in the package.";
}
まぁこれも何のこっちゃって感じですが。
実際に今回実装したのはこんな感じです。今回はツール側で行番号を追加するので、最初から出力列を追加してしまいます。
public override void ProvideComponentProperties()
{
// コンポーネントの初期化
base.RemoveAllInputsOutputsAndCustomProperties();
// 入力の追加
IDTSInput100 input = ComponentMetaData.InputCollection.New();
input.Name = "入力0";
// 出力の追加
IDTSOutput100 output = ComponentMetaData.OutputCollection.New();
output.Name = "出力0";
output.SynchronousInputID = input.ID;
// 出力列の追加
IDTSOutputColumn100 outputColumn = output.OutputColumnCollection.New();
outputColumn.Name = "行番号";
outputColumn.Description = "行番号";
outputColumn.SetDataTypeProperties(DataType.DT_I4, 0, 0, 0, 0);
}
コンポーネントの初期化を行うRemoveAllInputsOutputsAndCustomPropertiesメソッドに関してはドキュメントを読めばそれでOKです。これ以外の初期化方法にどんなものがあるのか謎です。
ここでキモとなるのはPipelineComponentが持っているComponentMetaDataプロパティです。このプロパティにはIDTSComponentMetaData100インターフェイスが入っています。
IDTSComponentMetaData100が持つInputCollectionプロパティで入力を、OutputCollectionプロパティで出力を、CustomPropertyCollectionプロパティでコンポーネント独自のプロパティをそれぞれ設定することが出来ます。
InputCollectionとOutputCollectionは絶対に設定しなければなりません。（少なくともComponentTypeをTransformにした場合は。）でないと、データフローでコンポーネント同士をくっつけられませんからね。
また、IDTSOutput100インターフェイスのSynchronousInputIDに入力のIDを指定することで、自動で入力列が出力に追加されます。逆に言えば、これを指定しないと出力列が消えます。
SynchronousInputIDをあえて指定せず、入力列と出力列の同期をとりたくない（全体結合とかマージ結合の処理をイメージするとわかりやすい）場合、PrimeOutputメソッドを明示的に実装する必要があります。SynchronousInputIDを指定している場合はこのメソッドは呼ばれないらしいです。なので今回は実装しません。
実際にカラムを追加する場合はIDTSOutput100のOutputColumnCollectionプロパティからNewメソッドを呼び出してIDTSOutputColumn100インターフェイスを取得し、NameプロパティとかDescriptionプロパティを設定してやりつつSetDataTypePropertiesメソッドで型などのメタデータを設定してあげます。
SetDataTypePropertiesで設定できるDataType列挙体はこれだけあるのに、なぜかSetDataTypePropertiesの説明には一部しか紹介されていません。これもふざけるなァ案件ですが、常識の範囲内でlLengthとかを設定してあげてください。
たったこれだけのことにこれほどドキュメントを漁らないといけないあたり、カスタムオブジェクト作成のコストの高さがわかりますね。
ProcessInputの実装
次にProcessInputメソッドを実装していきます。このメソッドのシグネチャにあるPipelineBufferからSetで始まるメソッドを呼ぶことで実際に値を設定することが出来ます。
今回の実装はこのようになります。
private int _rowCountColumnIndex;
public override void PreExecute()
{
var input = ComponentMetaData.InputCollection[0];
// ProvideComponentPropertiesで設定した出力列（[行番号]カラム）の取得
var rowCountColumn = ComponentMetaData.OutputCollection[0].OutputColumnCollection[0];
// BufferManagerから[行番号]カラムがbufferのどこにあるかを特定する
_rowCountColumnIndex = BufferManager.FindColumnByLineageID(input.Buffer, rowCountColumn.LineageID);
}
public override void ProcessInput(int inputID, PipelineBuffer buffer)
{
var count = 0;
if (!buffer.EndOfRowset)
{
while (buffer.NextRow())
{
// [行番号]カラムに行番号を付与する
buffer.SetInt32(_rowCountColumnIndex, ++count);
}
}
}
PipelineBufferのSetから始まるメソッドはcolumnIndexと言うシグネチャを持っています。これは文字通り、データをセットするカラムがbufferのどこにあるのかを特定するために使います。BufferManagerのFindColumnByLineageIDメソッドを使うことで、メタデータの入力/出力列がbufferのどこにあるのかを調べることが出来ます。
SynchronousInputIDを設定している場合、出力の情報もinput側のBufferが所持しています。ですが、今回紐付けるLineageIDはoutput側に設定した列が所持しています。ちょっとわかりにくいので気をつけましょう。
また、このカラム位置はパフォーマンスの観点からProcessInputではなくPreExecuteで特定すべきであるとこのドキュメントに書かれているので、PreExecuteをoverrideし、一旦メンバ変数に渡しています。
ProcessInputに関しては見たまんまなので特に説明しません。
DLLのビルドと配置とGAC
さて、とりあえず必要なコードは書き終えたので、SSDTで使えるようにDLLを配置していきます。
カスタム オブジェクトのビルド、配置、およびデバッグと言うドキュメントを読んで面倒臭がらずにちゃんとやれば出来ます。
アセンブリの署名はプロジェクトのプロパティから設定することが出来ます。ちゃんとパスワードも設定しましょう。
ビルドし終わったDLLは規定のディレクトリにセットする必要があります。基本的には「C:\Program Files\Microsoft SQL Server\110\DTS」配下にあるディレクトリにおけばいいんですが、念のためレジストリの「HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\110\SSIS\Setup\DtsPath」キーをチェックしておくことをオススメします。私の環境ではProgram Files(x86)の方でした。
いちいちビルドした後コピーするのは面倒なので、プロジェクトのプロパティからビルドイベントを設定しておくと楽です。ついでにGacUtilからGACも登録してしまいましょう。
ビルド前イベントは以下のように設定します。
"C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\bin\NETFX 4.0 Tools\gacutil.exe" /u $(TargetName)
ビルド後イベントは以下のように設定します。
copy $(TargetFileName) "C:\Program Files (x86)\Microsoft SQL Server\110\DTS\PipelineComponents" "C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\bin\NETFX 4.0 Tools\gacutil.exe" /i "C:\Program Files (x86)\Microsoft SQL Server\110\DTS\PipelineComponents\$(TargetFileName)"
ドキュメントだとGacUtilで登録してからコピーしてるんですが、「Microsoft SQL Server\110\DTS」配下のDLLをGACに登録しないとSSISツールボックスに表示されなかったので先にコピーしています。
デバッグ
自作したカスタムオブジェクトをデバッグする場合は、devenv.exeもしくはDtsDebugHost.exeにアタッチして行うのが楽です。
デザイン時のメソッド（今回の場合であればProvideComponentProperties）をデバッグする場合はdevenv.exe、実行時のメソッド（今回の場合であればPreExecuteとProcessInput）をデバッグする場合はDtsDebugHost.exeです。
デザイン時は簡単なんですが、実行時はデータビューアの設定等を行ってカスタムオブジェクトが実行される前に一旦プロセスを止めないといけません。
また、このことからもわかる通り、デザイン時と実行時で使われるメソッドが違うので、デザイン時のメソッドでメンバ変数に値を入れ、実行時のメソッドで使用するなんてことはできません。
まとめ
正直非常にしんどいです。やっぱりオススメできるもんじゃないです。
