【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="あｓｄ">
<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ですね。
コード
引数として以下の二つを渡します。
- 接続マネージャーを追加するパッケージのフルパス
- 定義ファイルのフルパス
定義ファイルの内容は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の再生産でしかありませんしね。
