时间戳

首页 > 解决方案 > SSIS——读取行集中的标签并存储在表中

xml - SSIS——读取行集中的标签并存储在表中

问题描述

我有一个独特的问题。我正在使用 SSIS 加载多个文件，并且在 ROWSET 中我有文件名和 RowCount。我如何在我的 ETL 日志表中存储包 ID - SRC_FILE_NM - 读取行 -

我正在读取同一个包中的多个文件。

<ROWSET xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance">
    <SRC_FILE_NM>tauth_type_fullext_2018-10-08_11-32-07.312.xml</SRC_FILE_NM>
    <RECORDCOUNT>2</RECORDCOUNT>
    <ROW>  <AUTH_TYPE_CDE>LCL</AUTH_TYPE_CDE>  <AUTH_TYPE_DESC>Local</AUTH_TYPE_DESC> </ROW>
    <ROW>  <AUTH_TYPE_CDE>GLB</AUTH_TYPE_CDE>  <AUTH_TYPE_DESC>Global</AUTH_TYPE_DESC> </ROW>
</ROWSET>

标签： xmlssis

解决方案

脚本组件答案：

将这 2 个命名空间添加到顶部

using System.Xml.Serialization;
using System.Collections.Generic;

在 CreateNewOutputRows() 上方放置以下内容以定义您的 XML。我将您的示例粘贴到https://xmltocsharp.azurewebsites.net/以进行转换。

[XmlRoot(ElementName = "ROW")]
public class ROW
{
    [XmlElement(ElementName = "AUTH_TYPE_CDE")]
    public string AUTH_TYPE_CDE { get; set; }
    [XmlElement(ElementName = "AUTH_TYPE_DESC")]
    public string AUTH_TYPE_DESC { get; set; }
}

[XmlRoot(ElementName = "ROWSET")]
public class ROWSET
{
    [XmlElement(ElementName = "SRC_FILE_NM")]
    public string SRC_FILE_NM { get; set; }
    [XmlElement(ElementName = "RECORDCOUNT")]
    public int RECORDCOUNT { get; set; }
    [XmlElement(ElementName = "ROW")]
    public List<ROW> ROW { get; set; }
    [XmlAttribute(AttributeName = "xsi", Namespace = "http://www.w3.org/2000/xmlns/")]
    public string Xsi { get; set; }
}

这是您在输出行中读取 xml 的代码：

public override void CreateNewOutputRows()
{
    string filePath = ""; //put your filepath here. The best way is from a variable in SSIS. Also best since you need a foreach loop to get all the files you are processing.
    System.IO.StreamReader sr = new System.IO.StreamReader(filePath);

    ROWSET xml;

    XmlSerializer serializer = new XmlSerializer(typeof(ROWSET));
    xml = (ROWSET)serializer.Deserialize(sr);
    sr.Close(); //So important, otherwise you can't move file to processed

    Output0Buffer.AddRow();
    Output0Buffer.fname = xml.SRC_FILE_NM;
    Output0Buffer.rc = xml.RECORDCOUNT;
    //Note you can get everything at this point but you didn't ask for it.
}

推荐阅读