読者です 読者をやめる 読者になる 読者になる

【SSIS】フラットファイル接続マネージャーをテキストファイルから生成する

SSISのデータ型はなるべく接続マネージャー or SQLで合わせておいた方がいいみたいな話をしたんですが、SQLはともかくフラットファイル接続マネージャーの設定は結構面倒です。

列名ぐらいなら定義時にヘッダをつけておけばいいんですが、データ型はちまちま自分で設定しなくてはなりません。推測してくれる機能もあるんですが、わざわざフル桁のデータを作るのもだるいです。面倒で面倒で嫌になったので、定義情報を適当なテキストファイルに書き出し、そいつを喰わせれば自動でフラットファイル接続マネージャーを作ってくれるツールを作りました。

dtsxを読む

大昔に書いた[C#]Microsoft.SqlServer.Dts.Runtimeを使ってDTSXを生成する[C#]Microsoft.SqlServer.Dts.Runtimeを使ってパラメータマッピングと結果セットを持つSQL実行タスクを作成するって記事でもちょっとだけやったんですが、SSDTで生成されるパッケージ(dtsx)は単なるXMLファイルですので、やろうと思えばLINQ to XMLでも書けます。

パッケージで使用されている名前空間「DTS」は一応仕様も公開されているんですが、流石にここから欲しい情報だけを抽出するのは辛いので、事前に適当なフラットファイル接続マネージャーだけを持つパッケージを作成し、その情報を解析していこうと思います。

とりあえずパッケージ名は「Package.dtsx」、接続マネージャーは「TEST_1」と「TEST_2」の二つとしてみましょう。

  <DTS:ConnectionManagers>
    <DTS:ConnectionManager
      DTS:refId="Package.ConnectionManagers[TEST_1]"
      DTS:CreationName="FLATFILE"
      DTS:DTSID="{44576533-199A-4D02-9906-80FE0262F3BC}"
      DTS:ObjectName="TEST_1">
      <DTS:ObjectData>
        <DTS:ConnectionManager
          DTS:Format="Delimited"
          DTS:LocaleID="1041"
          DTS:HeaderRowDelimiter="_x000D__x000A_"
          DTS:ColumnNamesInFirstDataRow="True"
          DTS:RowDelimiter=""
          DTS:TextQualifier="_x003C_none_x003E_"
          DTS:CodePage="932"
          DTS:ConnectionString="asd">
          <DTS:FlatFileColumns>
            <DTS:FlatFileColumn
              DTS:ColumnType="Delimited"
              DTS:ColumnDelimiter="_x002C_"
              DTS:MaximumWidth="50"
              DTS:DataType="129"
              DTS:TextQualified="True"
              DTS:ObjectName="文字列"
              DTS:DTSID="{47A7D7F2-4C43-4842-8E91-ECA5DC234F53}"
              DTS:CreationName="" />
            <DTS:FlatFileColumn
              DTS:ColumnType="Delimited"
              DTS:ColumnDelimiter="_x002C_"
              DTS:DataType="131"
              DTS:DataPrecision="18"
              DTS:TextQualified="True"
              DTS:ObjectName="数値"
              DTS:DTSID="{FA6CECD2-3354-41B2-9D18-02E0F7D85066}"
              DTS:CreationName="" />
            <DTS:FlatFileColumn
              DTS:ColumnType="Delimited"
              DTS:ColumnDelimiter="_x002C_"
              DTS:DataType="131"
              DTS:DataPrecision="18"
              DTS:DataScale="2"
              DTS:TextQualified="True"
              DTS:ObjectName="数値_小数点つき"
              DTS:DTSID="{475BA5C2-BDD1-4BB6-A631-A378E143D5D6}"
              DTS:CreationName="" />
            <DTS:FlatFileColumn
              DTS:ColumnType="Delimited"
              DTS:ColumnDelimiter="_x002C_"
              DTS:DataType="7"
              DTS:TextQualified="True"
              DTS:ObjectName="日付"
              DTS:DTSID="{4FBA6AAA-2825-4010-A50A-E246357E4672}"
              DTS:CreationName="" />
            <DTS:FlatFileColumn
              DTS:ColumnType="Delimited"
              DTS:ColumnDelimiter="_x000D__x000A_"
              DTS:DataType="135"
              DTS:TextQualified="True"
              DTS:ObjectName="日時"
              DTS:DTSID="{56004E07-0B67-4745-9D57-D7390B5FCE93}"
              DTS:CreationName="" />
          </DTS:FlatFileColumns>
        </DTS:ConnectionManager>
      </DTS:ObjectData>
    </DTS:ConnectionManager>
    <DTS:ConnectionManager
      DTS:refId="Package.ConnectionManagers[TEST_2]"
      DTS:CreationName="FLATFILE"
      DTS:DTSID="{9E57E9AE-C8A6-4D2D-B79C-5D20846E028D}"
      DTS:ObjectName="TEST_2">
      <DTS:ObjectData>
        <DTS:ConnectionManager
          DTS:Format="Delimited"
          DTS:LocaleID="1041"
          DTS:HeaderRowDelimiter="_x000D__x000A_"
          DTS:RowDelimiter=""
          DTS:TextQualifier="_x003C_none_x003E_"
          DTS:CodePage="932"
          DTS:ConnectionString="あsd">
          <DTS:FlatFileColumns>
            <DTS:FlatFileColumn
              DTS:ColumnType="Delimited"
              DTS:ColumnDelimiter="_x0009_"
              DTS:DataType="129"
              DTS:TextQualified="True"
              DTS:ObjectName="タブ"
              DTS:DTSID="{85B36827-EF01-4790-86BD-92AA741EA2EB}"
              DTS:CreationName="" />
            <DTS:FlatFileColumn
              DTS:ColumnType="Delimited"
              DTS:ColumnDelimiter="_x000D__x000A_"
              DTS:DataType="129"
              DTS:TextQualified="True"
              DTS:ObjectName="ぶ"
              DTS:DTSID="{7005D0BA-A0EB-4157-A0E0-D4F6EC8A3C0D}"
              DTS:CreationName="" />
          </DTS:FlatFileColumns>
        </DTS:ConnectionManager>
      </DTS:ObjectData>
    </DTS:ConnectionManager>
  </DTS:ConnectionManagers>

色んなAttributeを除外するとこんな構造になっています。

<DTS:ConnectionManagers>
  <DTS:ConnectionManager>
    <DTS:ObjectData>
      <DTS:ConnectionManager>
        <DTS:FlatFileColumns>
          <DTS:FlatFileColumn />
          ...
        </DTS:FlatFileColumns>
      </DTS:ConnectionManager>
    </DTS:ObjectData>
  </DTS:ConnectionManager>
  ...
</DTS:ConnectionManagers>

じゃあ順を追って各要素のAttributeの中身を調べていきましょう。

あ、ちなみにこれが全部のAttributeってわけではなく、色々設定したら他のAttributeがくっついたりすると思います。

DTS:ConnectionManagerのAttribute

Attribute 概説・値
refId 「(パッケージ名).ConnectionManagers[(接続マネージャー名)]
CreationName FLATFILE固定。この値でどの接続マネージャーを使うか決定する
DTSID 適当なGUID
ObjectName 接続マネージャー名

DTS:ObjectDataのAttribute

とくになし。

ObjectData - DTS:ConnectionManagerのAttribute

同じ名前にすんなよ…。

Attribute 概説・値
Format 区切り記号、固定幅の設定(Delimitedだと区切り記号)
LocaleID 言語(1041は日本語らしい)
HeaderRowDelimiter ヘッダ行区切り記号
ColumnNamesInFirstDataRow 先頭行をカラム名と認識する(Boolean)
RowDelimiter 行区切り記号(SSDTからは設定できない?)
TextQualifier テキスト修飾子(「_x003C_none_x003E_」は「なし」)
CodePage フラットファイルのコードページ(932はSJIS
ConnectionString ファイルパス

区切り記号は若干面倒臭いです。以下のような変換をかける必要があります。

記号 DTSでの値
CR _x000D_
LF _x000A_
CRLF _x000D__x000A_(要はCRとLFを結合する)
タブ _x0009_
カンマ _x002C_

ちなみにこれ、フラットファイル接続マネージャーのExpressionに設定するときも同じ値を使うので、「こんな風に表現するんだなー」って言うのは知っておいてもいいと思います。

DTS:FlatFileColumnsのAttribute

とくになし。

要素名の通り、この配下にカラムの要素を詰め込んでいきます。

DTS:FlatFileColumnのAttribute

Attribute 概説・値
ColumnType 区切り方(Delimitedだと区切り記号形式)
ColumnDelimiter 区切り記号
MaximumWidth 最大バイト数(文字列系統のみ?)
DataType データ型(後述)
DataPrecision 最大桁数(有効桁数、と言ったほうがいいのかも。数値系統のみ?)
DataScale 小数桁数(数値系統のみ?)
TextQualified テキスト修飾子の有無(Boolean)
ObjectName カラム名
DTSID 適当なGUID
CreationName 不明。作成者?

DataTypeはDT_STRとか、そう言うのです。数値で表現されています。

DT_STR 129
DT_NUMERIC 131
DT_DATE 7
DT_DATETIME 135

直近で必要だったものだけを調べたので、とりあえずこんな感じなんだなと思ってもらえれば。

後はこれを再現するコードを書けばOKですね。

コード

引数として以下の二つを渡します。

  1. 接続マネージャーを追加するパッケージのフルパス
  2. 定義ファイルのフルパス

定義ファイルの内容は39〜46行目とMetadataクラスの中身から察してください。(解説の放棄)

超絶適当に作ったので色々とやるべきValidationをほとんどすべてすっとばしています。一応パッケージのバックアップは取るようにしていますが、コードを丸々コピペして痛い目を見ても一切責任を取るつもりはありません。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using System.IO;

namespace FlatFlieManagerCreater
{
    class Program
    {
        private const string TAB = "_x0009_";
        private const string CR = "_x000D_";
        private const string LF = "_x000A_";
        private const string COMMA = "_x002C_";

        static void Main(string[] args)
        {
            var dtsxName = Path.GetFileNameWithoutExtension(args[0]);
            var dtsx = XElement.Load(args[0]);
            XNamespace dts = "www.microsoft.com/SqlServer/Dts";

            // backup
            var prefix = DateTime.Now.ToString("yyyyMMdd_HHmmss_");
            File.Copy(args[0], Path.GetDirectoryName(args[0]) + "\\" + prefix + dtsxName + "_bak.dtsx");

            var metadataPath = args[1];
            var metadataFileName = Path.GetFileNameWithoutExtension(metadataPath);
            var metadatas = new List<Metadata>();

            using (var sr = new StreamReader(metadataPath, Encoding.GetEncoding(932)))
            {
                sr.ReadLine();

                while (!sr.EndOfStream)
                {
                    var buf = sr.ReadLine().Split('\t');

                    metadatas.Add(new Metadata
                    {
                        Name = buf[0],
                        Type = buf[1],
                        Length = buf[2],
                        Scale = buf[3],
                        Delimiter = buf[4],
                    });
                }
            }

            // DTS:ConnectionManager
            var connection = new XElement(dts + "ConnectionManager")
                            .SetDtsAttribute("refId", string.Format("{0}.ConnectionManagers[{1}]", dtsxName, metadataFileName))
                            .SetDtsAttribute("CreationName", "FLATFILE")
                            .SetDtsAttribute("DTSID", "{" + Guid.NewGuid().ToString() + "}")
                            .SetDtsAttribute("ObjectName", metadataFileName);


            // DTS:ObjectData
            var objectData = new XElement(dts + "ObjectData");

            // DTS:ConnectionManager
            var element = new XElement(dts + "ConnectionManager")
                            .SetDtsAttribute("Format", "Delimited")
                            .SetDtsAttribute("LocaleID", "1041")
                            .SetDtsAttribute("HeaderRowDelimiter", CR + LF)
                            .SetDtsAttribute("ColumnNamesInFirstDataRow", "true")
                            .SetDtsAttribute("RowDelimiter", string.Empty)
                            .SetDtsAttribute("TextQualifier", "_x003C_none_x003E_")
                            .SetDtsAttribute("CodePage", "932")
                            .SetDtsAttribute("ConnectionString", string.Empty);

            // DTS:FlatFileColumns
            var columns = new XElement(dts + "FlatFileColumns");

            columns.Add(metadatas.Select(x =>
            {
                // DTS:FlatFileColumn
                var column = new XElement(dts + "FlatFileColumn")
                                .SetDtsAttribute("ColumnType", "Delimited")
                                .SetDtsAttribute("ColumnDelimiter", x.Delimiter)
                                .SetDtsAttribute("DataType", x.Type)
                                .SetDtsAttribute("TextQualified", "True")
                                .SetDtsAttribute("ObjectName", x.Name)
                                .SetDtsAttribute("DTSID", "{" + Guid.NewGuid().ToString() + "}")
                                .SetDtsAttribute("CreationName", string.Empty);

                // サイズだけはいろいろみて判断する
                switch (x.GetOriginalType())
                {
                    case DataType.DT_STR:
                        column.SetDtsAttribute("MaximumWidth", x.Length);
                        break;
                    case DataType.DT_NUMERIC:
                        column.SetDtsAttribute("DataPrecision", x.Length);
                        if (x.Scale != string.Empty) column.SetDtsAttribute("DataScale", x.Scale);
                        break;
                    case DataType.DT_DATE:
                        break;
                    case DataType.DT_DATETIME:
                        break;
                    default:
                        break;
                }


                return column;
            }).ToArray());

            //<DTS:ConnectionManagers>
            //  <DTS:ConnectionManager>
            //    <DTS:ObjectData>
            //      <DTS:ConnectionManager>
            //        <DTS:FlatFileColumns>
            //          <DTS:FlatFileColumn />
            //          ...
            //        </DTS:FlatFileColumns>
            //      </DTS:ConnectionManager>
            //    </DTS:ObjectData>
            //  </DTS:ConnectionManager>
            //  ...
            //</DTS:ConnectionManagers>
            element.Add(columns);
            objectData.Add(element);
            connection.Add(objectData);

            if (dtsx.Descendants(dts + "ConnectionManagers").Any())
            {
                dtsx.Descendants(dts + "ConnectionManagers").First().Add(connection);
            }
            else
            {
                var connectionManager = new XElement(dts + "ConnectionManagers");
                connectionManager.Add(connection);
                dtsx.Add(connectionManager);
            }

            dtsx.Save(args[0]);

            Console.WriteLine("finish");

        }
    }

    public static class XElementExtention
    {
        private static XNamespace dts = "www.microsoft.com/SqlServer/Dts";

        public static XElement SetDtsAttribute(this XElement element, string name, object value)
        {
            element.SetAttributeValue(dts + name, value);
            return element;
        }
    }

    class Metadata
    {
        public string Name { get; set; }

        private DataType _type;
        public string Type
        {
            get
            {
                return ((int)_type).ToString();
            }
            set
            {
                switch (value)
                {
                    case "文字列":
                        _type = DataType.DT_STR;
                        break;
                    case "数値":
                        _type = DataType.DT_NUMERIC;
                        break;
                    case "日付":
                        _type = DataType.DT_DATE;
                        break;
                    case "日時":
                        _type = DataType.DT_DATETIME;
                        break;
                }
            }
        }
        public string Length { get; set; }
        public string Scale { get; set; }

        private string _delimiter;
        public string Delimiter
        {
            get
            {
                return _delimiter;
            }
            set
            {
                switch (value)
                {
                    case "タブ":
                        _delimiter = "_x0009_";
                        break;
                    case "カンマ":
                        _delimiter = "_x002C_";
                        break;
                    case "CR":
                        _delimiter = "_x000D_";
                        break;
                    case "LF":
                        _delimiter = "_x000A_";
                        break;
                    case "CRLF":
                        _delimiter = "_x000D__x000A_";
                        break;
                }
            }
        }

        public DataType GetOriginalType()
        {
            return _type;
        }
    }

    enum DataType : int
    {
        DT_STR = 129,
        DT_NUMERIC = 131,
        DT_DATE = 7,
        DT_DATETIME = 135
    }
}

キモはXNamespace dtsの存在です。そんぐらいですね。

まとめ

とまぁ、こんな感じでちゃんと解析していけばパッケージの中身を自動生成できます。

今回のように別途定義ファイルを用意してもいいですし、固定の文字列を持たせて「バッチを叩くだけである程度の雛形を自動生成」なんてことをしてもいいと思います。

ま、どこまでやるかは内容次第ですが。あんまり凝ったものを作ると、単なるSSDTの再生産でしかありませんしね。