環境
SQL Server 2005, 2008, 2008 R2
発生する問題
SSIS (SQL Server Integration Services) の データクローから「Excel ソース」を使用すると Excel ファイルからデータを取り込むことができます。Excel の中身がヘッダ１行と２行目以降がデータという一般的なテーブルレイアウトであれば、そのままデータベースに取り込めるので結構便利です。
しかし、実際に使ったことがある方は経験されていることが多いと思いますが、Excel のデータの中身が変わると、SSIS デザイナ上でなぜか型が一致しないという警告が出ることがあります。一度詳細を開いて更新すればもとに戻りますが、Excel の中のデータが変わるとまた警告がでる、の繰り返しになります。
また、場合によっては Excel の中にはきちんとデータがあるにも関わらず、実際に読み込んでみると null で読み込まれる場合があります。
これらの現象は SSIS の問題ではなく、SSIS が Excel の読み込みコンポーネントとして使用している「Excel ISAM ドライバ」の仕様の問題で発生します。これは「Microsoft Jet 4.0 OLE DB Provider」でも「Microsoft Office 12.0 Access Database Engine OLE DB Provider」でも同様です。
このドライバが何をしているかというと、データを読み込む際にわざわざ型指定して読み込まなくても、Excel の中身を見て自動的に型を判別してくれるという便利そうな処理をしていますが、その代り、データの中身の型がころころ変わると読み込む際の型情報も変わってしまいます。そのため、SSIS 上のデザイナでも、事前に型を決定しているにもかかわらず、Excel の中身の型が変わってしまうことによって型が不一致であるという警告が表示されるのです。
このあたりの使用は下記のサイトをみるとわかりやすいかと思います。
簡単にまとめると、Excel の上位８行を読み込み、その列で最も多い型を読み込む型として使用するようになっています。例えば８行内に、文字列が３つ、数値が５つあればその列は「数値型」になります。逆に文字列が６つ、数値が２つであればその列は「テキスト型」になります。同じ個数の場合は「数値型」が優先されます。
ちなみに SSIS で説明していますが、ISAM ドライバーの問題なので、これを使うプログラムはすべて同じ問題を抱えています。
対応方法
ではこれを確実に回避するにはどうすればいいかというと、少し手を込んだことをしなければいけません。
まず、今回の対応方法としては前提条件としてすべての列を文字列として読み込みます。もともと数値の列も文字列として読み込むので、読み込んだ後は数値に変換する必要があります。これは臨機応変に対応してください。
ISAMドライバーで読み込む際、「IMEX」というパラメータを指定することができます。これに「1」を指定すると「インポート モード」になり、読み込むすべてのデータがテキストになります。ただし、Excel 内の指定した列のデータがすべて数値の場合は数値になってしまうので注意してください。一つでも文字列があれば読み込む型は文字列になります。
IMEX パラメータは「接続文字列」で指定します。SSIS デザイナで接続文字列を編集する場合は、接続マネージャーで作成した Exce ソースを右クリックし、プロパティを開きます。プロパティエリアに「ConnectionString」という項目がありますので、その文字列を直接編集します。GUI で設定するパターンもありますが、こちらは IMEX が設定できないので注意してください。
IMEX パラメータは下記のように設定します。一番後ろにある「Extended Properties」内にセミコロン区切りで「IMEX=1」を追加します。
- Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:Test.xlsx; Extended Properties="Excel 12.0; HDR=YES; IMEX=1;”
接続文字列については以前まとめましたのでこちらのページを参照してください。
さて、これですべての列は文字列として読み込める「はず」なのですが、前述したとおり、Excel にすべて数値が入力されていた場合、または何も入力されていない場合はどうしても列の型が数値として読み込まれてしまいます。ですので、Excel の１行目か２行目に「１行すべて文字列だけを入力した行」を入れるようにします。こうすればほぼ間違いなく文字列として読み込めるようになります。読み込みもとの Excel ファイルにもよりますが、テキスト行を作ってその行だけ非表示にすれば編集上は問題ないかと思います。
まあ、個人的には Excel で読み込むのではなく、型を意識しない CSV ファイルのほうがよかったりもするんですけどね。Excel 上で編集すると「0000」とテキストとして貼り付けたつもりが数値として 0 になってしまってて気づかなかったりとかあるので。実は CSV も CSV でダブルクォーテーションのエスケープが考慮されてなかったりとかいろいろ問題はありますが (^^;)
]]>
