桜技録

🐈🐈🐈🐈🐘

SQL Server インポートおよびエクスポートウィザードでExcel文字列型→datetime2型

SQL Server

SQL Server インポートおよびエクスポートウィザード (DTSWizard) でExcelからSQL Server上のテーブルへデータ・インポートしようとして発生した型変換エラーとその対処の記録。

なおここに書いた対処法は勘に頼ったもので公式ドキュメント等での裏付けは一切していない。詳しい人教えて……。

前提

  • DTSWizardはSQL Server Management Studio v17.9.1に付属のもの
  • データソースはそれぞれ「Microsoft Excel」と「SQL Server Native Client 11.0」
  • 投入元Excelの該当列のデータ型は文字列型
  • 投入先テーブルの該当列のデータ型はdatetime2型

事象

「データ型マッピングの確認」まで来たところでこんなエラーが出た。

n個の不明な列の型変換が見つかりました
パッケージの保存のみ許可されています

「列変換の詳細」はこんな感じ。

[変換元の情報]
変換元の場所: ▲▲▲.xlsx
変換元プロバイダー: Microsoft.ACE.OLEDB.12.0
テーブル: ▲▲▲
列: ▲▲▲
列の型: VarChar
SSIS 型: Unicode 文字列 [DT_WSTR]
(SSIS 型への) マッピング ファイル: C:\Program Files (x86)\Microsoft SQL Server\140\DTS\MappingFiles\AceToSSIS.xml

[変換先の情報]
変換先の場所: ▲▲▲
変換先プロバイダー: SQLNCLI11
テーブル: ▲▲▲
列: ▲▲▲
列の型: datetime2
SSIS 型: 有効桁数が設定されたデータベース タイムスタンプ [DT_DBTIMESTAMP2]
(SSIS 型への) マッピング ファイル: C:\Program Files (x86)\Microsoft SQL Server\140\DTS\MappingFiles\MSSQLToSSIS10.XML

[変換の手順]
変換が不明です...
SSIS 変換ファイル: C:\Program Files (x86)\Microsoft SQL Server\140\DTS\binn\DtwTypeConversion.xml

対処

結論: DT_WSTR から DT_DBTIMESTAMP2 への変換をSSIS変換ファイル C:\Program Files (x86)\Microsoft SQL Server\140\DTS\Binn\DtwTypeConversion.xml に追加してやる。

【2019-04-13追記】下掲の設定例は冗長です。より簡潔な設定は後ろの方を参照。

<?xml version="1.0" encoding="utf-8"?>
<dtw:DtwConversionTables
  xmlns:dtw="http://www.microsoft.com/SqlServer/Dts/DtwTypeConversion.xsd"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  Version="1.0">
  <dtw:ImplicitConversion>

    <!-- 略 -->

    <!-- Convert from DT_WSTR-->
    <dtw:ConversionEntry>
      <dtw:SourceType>DT_WSTR</dtw:SourceType>

      <!-- 略 -->

      <!-- ここから -->
      <dtw:DestinationType TypeName="DT_DBTIMESTAMP2">
        <dtw:ConversionStep StepNum="1" ConvertToType="DT_DBTIMESTAMP"/>
        <dtw:ConversionStep StepNum="2" ConvertToType="DT_DBTIMESTAMP2"/>
      </dtw:DestinationType>
      <!-- ここまで -->
    </dtw:ConversionEntry>

    <!-- 略 -->

  </dtw:ExplicitConversion>
</dtw:DtwConversionTables>

以下結論に至るまでの推理。

  1. エラー内容の通りSSIS変換ファイルに DT_WSTR から DT_DBTIMESTAMP2 への変換は定義されてない

  2. DT_WSTR から DT_DBTIMESTAMP 及び DT_DBTIMESTAMP から DT_DBTIMESTAMP2 は定義されている

  3. 名前が「Step」だし StepNum インクリメントして dtw:ConversionStep 増やせば段階踏んだ型変換を定義出来るのでは?

  4. 出来た

ちなみにこのへんだと DT_DBTIMESTAMP2 は他の日時型と同様に文字列から変換出来るような書きっぷりなので、変換の実装はされていて定義さえ入れてやれば1ステップで済むのではという予感もする。

【2019-04-13追記】

予感の通り2ステップ経ずとも1ステップで変更が可能だった。なので最終的なSSIS変換ファイルは以下の通りとなる。

<?xml version="1.0" encoding="utf-8"?>
<dtw:DtwConversionTables
  xmlns:dtw="http://www.microsoft.com/SqlServer/Dts/DtwTypeConversion.xsd"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  Version="1.0">
  <dtw:ImplicitConversion>

    <!-- 略 -->

    <!-- Convert from DT_WSTR-->
    <dtw:ConversionEntry>
      <dtw:SourceType>DT_WSTR</dtw:SourceType>

      <!-- 略 -->

      <!-- ここから -->
      <dtw:DestinationType TypeName="DT_DBTIMESTAMP2">
        <dtw:ConversionStep StepNum="1" ConvertToType="DT_DBTIMESTAMP2"/>
      </dtw:DestinationType>
      <!-- ここまで -->
    </dtw:ConversionEntry>

    <!-- 略 -->

  </dtw:ExplicitConversion>
</dtw:DtwConversionTables>