【SSIS Lightning】項目数が不揃いな CSV ソースファイルの欠落部分を NULL 値としてパイプする

シナリオと要件

SQL Server 2000 の DTS では，フラットファイル変換元の CSV ファイルに項目数の不揃いなファイルを指定しても柔軟に対応してくれていました。

一方，SSIS では，列区切り記号と行区切り記号の解釈が厳密になったため，以前のような柔軟性が失われています。

例えば，

1
1,2,3
1,2
1,
2,"3,4"
5
6,6,7
6,7

というような CSV ファイルが存在していたとします。
残念ながら，このデータを列数３のカンマ区切りのフラットファイルソースとして認識させることはできません。
無理やり認識させようとすると，カンマの数を基準に行区切りを無視してデータが認識されてしまいます。

このデータを次のようなデータとして認識させます。

Column0	Column1	Column2
1	NULL	NULL
1	2	3
1	2	NULL
1		NULL
2	"3,4"	NULL
5	NULL	NULL
6	6	7
6	7	NULL

この記事は，
SQL Server 2005 Integration Services Service Pack 1 に基づいて記述しています。

フラットファイルソースの設定

このシナリオのポイントは，[フラットファイル接続先] に列の区切りを判断させないことです。
[フラットファイル接続先] には，改行で行を区切る１列だけのソースとして認識させます。
列を区切る処理は，[フラットファイル接続先] からデータがパイプされた後に行います。

不揃いなデータが接続先でエラーを起こさないように設定します。

１ [データフロータスク] を作成して，[フラットファイル接続先] を１つ作成する。

２ [型の推測] を行わせず，１行のデータが充分収まる [DT_WSTR] 型の列を１つだけ作成する。

３ [行区切り記号] のみを [{CR}{LF}] に指定する。

不揃いなデータのパイプ

作成した [フラットファイル接続先] に [フラットファイルソース] を接続して，パイプされたデータを [派生列] コンポーネントで加工することもできます。
しかし，[派生列] を使用した方法では，[変数] や [式] を複雑に組み合わせなければならず，非常に煩雑な変換になってしまいます。

そこで，ここでは [スクリプトコンポーネント] を [変換元] として定義して，カンマ区切りの認識と NULL 値の補足の両方を一気に片付けてしまいましょう。

[スクリプトコンポーネント] は [変換元]，[変換先]，[変換] としてそれぞれ定義することのできる，VB.NET を使用した非常に柔軟なコンポーネントです。
ただし，濫用するとパッケージを VB.NET で最初から作成したほうがよいような複雑な処理を構築してしまう場合もあるので注意しましょう。

データは [nvarchar] 型の列を３つ持つ単純なテーブルに挿入します。
[OLE DB 変換先] を使用してテーブルにパイプします。

テーブルの DDL

CREATE TABLE [dbo].[Test](

[nchar](10) COLLATE Japanese_CI_AS NULL,

[nchar](10) COLLATE Japanese_CI_AS NULL

) ON [PRIMARY]

１ [OLE DB 変換先] ひとつと [スクリプトコンポーネント] を作成する。
[スクリプトコンポーネントの種類を選択] ダイアログでは，[変換元] を選択する

２ [スクリプト変換エディタ] を開いて，[入力および出力] ページでパイプする列を追加する。
[出力列] に [列の追加] ボタンを使用して３つの列を設定する。
ここで設定する列の [Name] プロパティが，スクリプトコード内で使用するため，半角英数の名前を指定する。
列の型は，テーブルにあわせるために，[DT_WSTR] の長さ 10 とする。

３ [接続マネージャ] ページを開いて，作成した CSV ファイルの [フラットファイル接続先] を追加する。
ここでの [名前] もスクリプトコード内で使用するため，半角英数の名前を指定する。

４ [スクリプト] ページの [スクリプトのデザイン] をクリックして次のソースコードを入力する。
５ [OLE DB 変換先] に３つの列をパイプする。

スクリプトのコード

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports System.IO

Public Class ScriptMain

Inherits UserComponent

Private myCSVReader As StreamReader

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

Dim connectionManager As IDTSConnectionManager90 = _

Me.Connections.CSVConnection

Dim csvSourceFilePath As String = _

CType(connectionManager.AcquireConnection(Nothing), String)

myCSVReader = New StreamReader(csvSourceFilePath)

End Sub

Public Overrides Sub CreateNewOutputRows()

Const delimiter As Char = ","c

Const quote As Char = """"c

Const outputColumnCount As Int32 = 3

Dim nextLine As String

Dim splited As String()

Dim columns As New Collections.ArrayList

nextLine = myCSVReader.ReadLine

Do While nextLine IsNot Nothing

splited = nextLine.Split(delimiter)

columns.Clear()

Dim concatString As String

Dim isQuoted As Boolean = False

For Each current As String In splited

If current.IndexOf(quote).Equals(-1) OrElse _

(current.StartsWith(quote) AndAlso current.EndsWith(quote)) Then

If isQuoted Then

concatString &= current

Else

columns.Add(current)

End If

ElseIf current.StartsWith(quote) Then

isQuoted = True

concatString = current

ElseIf current.EndsWith(quote) Then

isQuoted = False

concatString &= current

columns.Add(concatString)

End If

For count As Int32 = columns.Count To outputColumnCount

columns.Add(DBNull.Value)

With ColumnOutputBuffer

.AddRow()

If columns.Item(0) Is DBNull.Value Then

.Column0_IsNull = True

Else

.Column0 = Convert.ToString(columns.Item(0))

End If

If columns.Item(1) Is DBNull.Value Then

.Column1_IsNull = True

Else

.Column1 = Convert.ToString(columns.Item(1))

End If

If columns.Item(2) Is DBNull.Value Then

.Column2_IsNull = True

Else

.Column2 = Convert.ToString(columns.Item(2))

End If

End With

nextLine = myCSVReader.ReadLine

Loop

End Sub

Public Overrides Sub ReleaseConnections()

myCSVReader.Close()

End Sub

End Class

スクリプトの解説を少ししておきましょう。

ほとんどのコードは [ScriptMain] クラスの [CreateNewOutputRows] メソッドに記述します。
このメソッドが列にパイプするデータを定義する部分です。

メソッドの最初の定数は，この変換元で使用する「列区切り文字」と「テキスト修飾文字」を定義しています。
この定数を変更することで，２つの文字を変更することができます。

スクリプトでは次に，[フラットファイル接続先] で定義されている CSV ファイルを [StreamReader] で読み取って，１行ずつ処理を実行します。
「列区切り文字」で [Split] を行い，「テキスト修飾文字」を基準に解釈を修正します。
さらに列数に不足するデータは NULL 値として解釈します。

列の解釈が完了したら，[ColumnOutputBuffer] に列を追加します。この追加した列がパイプされるデータになります。
それぞれの列にデータをパイプする際に，NULL 値を設定する場合は [_IsNull] プロパティを使用します。

この [スクリプトコンポーネント] を使用すると，カンマ区切りでダブルクォーテーションをテキスト修飾文字とするデータを解釈することができます。
図は，データのパイプの状況を [グリッドビューア] で確認したところです。

本来の ETL としては

このシナリオでは，不揃いの CSV ファイルをそのままデータソースとすることにこだわってみました。
しかし，ETL 本来の考え方からすると，この実装はあまり推奨できません。

データソースは，スムーズに変換をかけられるように，決められたフォーマットで保存されるべきです。
このシナリオの場合は，データを出力する際に空のデータをはさむなどとしてカンマの数を揃えます。

もしくは，不揃いなデータなどの「正規化されていない情報」は，データパイプの前段階の処理としてクレンジングされるべきです。

SSIS のステージでこの考え方を実装するなら

データ出力側の処理においてフォーマットに従ったデータを出力する
[データフロータスク] の前に，データをクレンジングする [スクリプトタスク] を設定する

などの対応が考えられるでしょう。

written by TimberLandChapel

Comments

# re: 【SSIS Lightning】項目数が不揃いな CSV ソースファイルの欠落部分を NULL 値としてパイプする@ 2007年11月8日 15:18

お世話になっております

上記記事は大変参考にないました。

そこで連続処理をすべくチュートリアルのループの追加を

参考に取り組んでいるのですが下記エラーのため滞ています

ファイル名を変数で渡す事はできないのでしょうか？

解決策のヒントをいただけるとありがたいです。

ご教授お願いします。

エラー1検証エラー。データフロータスク: スクリプトコンポーネント [18]: System.ArgumentException: パス名を空にすることはできません。場所 Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) 場所 Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.AcquireConnections(Object transaction) 場所 Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)Package.dtsx00

by woodstock

# re: 【SSIS Lightning】項目数が不揃いな CSV ソースファイルの欠落部分を NULL 値としてパイプする@ 2007年11月18日 22:46

すいません。カメレスになってしまいました。

エラーのスタックトレースから考えるに，

DTS パイプラインのコネクションがうまく設定できていないように思えます。

ループの設定とは無関係なところですね。

おそらくフラットファイル接続を使用されているでしょうから，

接続の状況を確認してみてください。

# re: 【SSIS Lightning】項目数が不揃いな CSV ソースファイルの欠落部分を NULL 値としてパイプする@ 2007年11月19日 11:18

レスありがとうございます。

上記記事の通り単一のファイル指定では成功します。

しかしForeachループコンテナの中に入れて連続処理を

するためにフラットファイル接続のExprssionsにて

ConnectionSting にForeachループコンテナで設定した

変数 @User::varFileNameを設定すると実効までファイル

はセットされないためエラーになっているようです。

まだ解決まで至っていません。

お力をお貸し願います。

by woodstock

# re: 【SSIS Lightning】項目数が不揃いな CSV ソースファイルの欠落部分を NULL 値としてパイプする@ 2007年11月19日 20:50

お疲れ様です。

検証し直していないんで，感覚的な回答ですが，

SSISの検証ステップで有効なパスが通ってないと蹴られた気がします。

接続の有効性を判定する検証を緩和するか

最初の設定時には仮のパスを与えておいて，それをあとから動的に書き換えるようにしておく

ことで対応できていたような気がします。

For Each コンテナのサンプルは一応

# re: 【SSIS Lightning】項目数が不揃いな CSV ソースファイルの欠落部分を NULL 値としてパイプする@ 2007年11月20日 11:37

ありがとうございます。

まだ解決できないですが上記アドバイスを

元にがんばります。

余談ですがFor Each コンテナのサンプルは

マッピングあたりが現状のものと違っていました。

by woodstock