Tips 更新

最近また忙しかったりしてブログにも Twitter にも書き込んでいない状態でしたが少し落ち着いてきたので久々に書き込みます。

sorceryforce.net の方で Tips を4つほど公開しました。SQL Server 関連ですが、大体はインストールとかセットアップとかそんなところの内容になってます。しかも CTP 版や Preview 版なのであんまり Tips に載せるべきものでもないような気がしますが (^^;)

後、ページ一覧をグループ分けして表示できるように作りました。前まではとにかくページをずらずら並べているだけだったのでちょっと見にくかったかもしれません。こういうのって HTML で直書きすると一瞬で終わるのにプログラムで作ろうとするとかなり手間なんですよね…。

Share this...

[SSIS] Excel から型情報を意識せずに確実に読み込む方法

環境

SQL Server 2005, 2008, 2008 R2

発生する問題

SSIS (SQL Server Integration Services) の データクローから「Excel ソース」を使用すると Excel ファイルからデータを取り込むことができます。Excel の中身がヘッダ1行と2行目以降がデータという一般的なテーブルレイアウトであれば、そのままデータベースに取り込めるので結構便利です。

しかし、実際に使ったことがある方は経験されていることが多いと思いますが、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 の上位8行を読み込み、その列で最も多い型を読み込む型として使用するようになっています。例えば8行内に、文字列が3つ、数値が5つあればその列は「数値型」になります。逆に文字列が6つ、数値が2つであればその列は「テキスト型」になります。同じ個数の場合は「数値型」が優先されます。

ちなみに 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 の1行目か2行目に「1行すべて文字列だけを入力した行」を入れるようにします。こうすればほぼ間違いなく文字列として読み込めるようになります。読み込みもとの Excel ファイルにもよりますが、テキスト行を作ってその行だけ非表示にすれば編集上は問題ないかと思います。

 

まあ、個人的には Excel で読み込むのではなく、型を意識しない CSV ファイルのほうがよかったりもするんですけどね。Excel 上で編集すると「0000」とテキストとして貼り付けたつもりが数値として 0 になってしまってて気づかなかったりとかあるので。実は CSV も CSV でダブルクォーテーションのエスケープが考慮されてなかったりとかいろいろ問題はありますが (^^;)

Share this...

SSIS で Shift_JIS で表現できない文字(文字コード)を含む CSV ファイルの読み書き

かなりピンポイントなメモです。

【読み込み】

Shift_JISで扱えない文字を Shift_JIS で保存するわけにはいかないので、通常は Unicode の形式で保存します。SSIS のフラット ファイル ソースでは Unicode ファイルの読み込みに対応しており、フラット ファイル接続マネージャーで「Unicode」にチェックを入れることによって読む込むことができます。Unicode といってもいくつか形式はありますが、「UTF-8」「UTF-16LE」での読込は確認しています。

【書き出し】

フラット ファイル変換先で Shift_JISに対応していない文字を Shift_JIS で書き出そうとすると必ずエラーになります。事前に Shift_JIS に対応していない文字を変換するか、Unicode などで保存する必要があります。

Share this...

別コンピューターから SSIS パッケージを実行する方法

メモです。SQL Server エージェントを登録する方法のプログラムで実行できました。

開発サーバーにしか SQL Server が入ってなく、SSIS パッケージもそこでしか実行できないので SQL Server エージェントに実行を任せています。

手順を簡単に書くと、

  1. SSIS プロジェクトでパッケージを作成
  2. SQL Server Management Studio から SQL Server データベース に接続し、SQL Server エージェント を起動 (今後常に使う場合は OS 起動時に常に起動させる設定にしておくといいです。)
  3. ジョブを作成し、ステップに SSIS パッケージ実行を追加。今回 SSIS パッケージはサーバー内のローカルフォルダにおいてあるので ファイル システムから SSIS パッケージを選択しました。
  4. ジョブを登録したら上記リンク先にあるプログラムでバッチ プログラムを作成。接続文字列やパッケージ名は環境に合わせて変えてください。(「jobConnection = new SqlConnection("Data Source~」の部分と「jobParameter.Value = "RunSSISPackage";」の部分)
  5. 後は実行して動作するか確認
Share this...

SSIS で Excel ファイルを読み込む際に IMEX パラメータを使用する方法

メモです。

接続マネージャーで「Excel 接続マネージャー」を作成後、Excel 接続マネージャーを選択しプロパティを表示する。

プロパティから「ConnectionString」の項目を探し、接続文字列に直接「IMEX=1」を追加する。

【例】

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:xxxyyy.xlsx;Extended Properties="EXCEL 12.0;HDR=YES";

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:xxxyyy.xlsx;Extended Properties="EXCEL 12.0;HDR=YES;IMEX=1";

Share this...