[SSIS] OLE DB プロバイダーを使用してフラットファイルをインポートすると正しくない結果となる

  • Article

SQL Server Integration Service パッケージにて、OLE DB プロバイダーを使用してフラットファイルをインポートすると正しくない結果となる場合があります。
この事象について例を基に対処方法について紹介します。

例
以下の様なシンプルな Integration Services パッケージを想定してください。

取り込み先のテーブル定義
CREATE TABLE [dbo].[Table_1](
[Col1] [int] NULL,
[Col2] [int] NULL,
[Col3] [int] NULL,
[Col4] [int] NULL,
[Col5] [int] NULL,
[Col6] [int] NULL
)

CSV ファイル

パッケージ
この様に非常にシンプルなパッケージを想定してください。

 

パッケージ実行結果
パッケージ実行の結果、Table_1 には以下の様にデータが格納されます。

CSV ファイルの 2、4、5 列目は空文字であるにも関わらず、その前に位置する数値が挿入される結果となります。
本来ならば、「数値型へ空文字を挿入できない」というエラーが発生するか、以下の様な結果を期待すると思います。

 

要因
申し訳ございませんが、Microsoft OLE DB Provider for SQL Server の Bug です。

条件
この現象が発生する条件は、以下の通りとなります。

・データベースへアクセスするプロバイダーとして Microsoft OLE DB Provider for SQL Server を使用している(*1)
・取り込み先対象のテーブルに、数値型の列（int や float、smallint 等）が複数存在する
・フラットファイルのデータが空文字でサイズが 0 である
・空文字が存在するうち、フラットファイルの先の列に適切な（エラーとならない）数値型のデータが存在する(*2)

(*1) 使用しているプロバイダーは接続マネージャーから確認できます。

(*2)
「1,2,<空文字>」のように空文字の前に有効な数値データが存在する場合、本現象が発生します。
「<空文字>,1,2」の様な場合だと、以下の様なエラーが発生します。

[OLE DB 変換先 [34]] エラー : 入力 "OLE DB 変換先の入力" (47) の 入力列 "列 0" (57) にエラーがありました。返された列の状態は "データが失われる可能性があるため、値を変換できませんでした。" でした。

 

対処方法
以下のいずれかの対処方法が有効となります。
どのような実行結果としたいか、によって対処方法を検討してください。

・データソースの NULL 値を保持する扱いとする
・プロバイダを変更する
・列型を文字列型に変更する

 対処方法 期待する実行結果  変更箇所 
データソースの NULL 値を保持する扱いとする  テーブルに NULL としてデータが格納される  パッケージの [フラット ファイル ソース]
プロバイダを変更する  パッケージ実行時にエラーを発生させる  パッケージの [接続マネージャー]
列型を文字列型に変更する  テーブルの列が文字型となり、文字型としてデータが格納される  SQL Server のテーブル定義

 

データソースの NULL 値を保持する扱いとする
空文字は、NULL として扱い、そのままテーブルに取り込みますと、空文字が Null として扱われ、以下の結果となります。

-- 設定手順
(1) フラット ファイル ソース エディターを開きます。
(2) [接続マネージャー] のページから「データソースの NULL 値をデータ フローで NULL 値として保持する」のチェックを ON にします。

この設定を実施すると、以下の様な結果となります。

プロバイダを変更する
Microsoft OLE DB Provider for SQL Server ではなく、SQL Server Native Client を使用します。

本来、SSIS パッケージ上で、フラットファイルの空文字をデータベースに取り込む場合、取り込み先が int などの数値型の場合、型が合致しない旨のエラーが発生します。
SQL Server Native Client を使用した場合には、本来のエラーである「型が合致しない」というエラーが発生するようになります。

空文字を 数値列に取り込まない、という考えになり、データのチェック機構が働くこととなります。

[OLE DB 変換先 [34]] エラー : 入力 "OLE DB 変換先の入力" (47) の 入力列 "列 1" (58) にエラーがありました。返された列の状態は "データが失われる可能性があるため、値を変換できませんでした。" でした。

 

列型を文字列型に変更する
int を、空文字が取り込めるように、varchar 等の文字列型に変更する案となります。