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 変換先] を使用してテーブルにパイプします。
CREATE TABLE [dbo].[Test](
[nchar](10) COLLATE Japanese_CI_AS NULL,
[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
Next
For count As Int32 = columns.Count To outputColumnCount
columns.Add(DBNull.Value)
Next
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] プロパティを使用します。
この [スクリプトコンポーネント] を使用すると，カンマ区切りでダブルクォーテーションをテキスト修飾文字とするデータを解釈することができます。
図は，データのパイプの状況を [グリッドビューア] で確認したところです。
このシナリオでは，不揃いの CSV ファイルをそのままデータソースとすることにこだわってみました。
しかし，ETL 本来の考え方からすると，この実装はあまり推奨できません。
データソースは，スムーズに変換をかけられるように，決められたフォーマットで保存されるべきです。
このシナリオの場合は，データを出力する際に空のデータをはさむなどとしてカンマの数を揃えます。
もしくは，不揃いなデータなどの「正規化されていない情報」は，データパイプの前段階の処理としてクレンジングされるべきです。
SSIS のステージでこの考え方を実装するなら
- データ出力側の処理においてフォーマットに従ったデータを出力する
- [データフロー タスク] の前に，データをクレンジングする [スクリプト タスク] を設定する
などの対応が考えられるでしょう。
written by TimberLandChapel