Lookup File - Quote

Foreach 迴圈 &資料流程 & 檔案系統工作

Foreach 迴圈

目的：設定 Quote JSON 檔案路徑
Foreach 迴圈容器列舉值：Foreach 檔案列舉值
資料夾：C:\Stock LINE Notify\Quote
檔案：Stock_Code.txt（包含完整路徑與檔案名稱）
變數對應：User::SourceFileName_Quote

資料流程工作

指令碼元件

目的：解析 JSON 檔案
ReadOnlyVariables 屬性：User::SourceFileName_Quote
輸出資料行

Name

DataType

ApiDatetime

日期 [DT_DATE]

StockNo

字串 [DT_STR]

DayHighPrice

十進位 [DT_DECIMAL]

DayHighTime

日期 [DT_DATE]

DayLowPrice

十進位 [DT_DECIMAL]

DayLowTime

日期 [DT_DATE]

TransDatetime

日期 [DT_DATE]

Script

using System.IO;
using System.Web.Script.Serialization;
using System.Collections.Generic;
using static SC_80b43ba3c41a483aa94ea9c1fd4ce6f0.Stock_Quote;
using Newtonsoft.Json;

// ......

// SSIS Package not support NutGet
static ScriptMain()
{
    AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentDomain_AssemblyResolve);
}
static System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
{
    if (args.Name.ToUpper().Contains("NEWTONSOFT"))
    {
        string path = @"Your JSON dll's folder";
        return System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(path, "Newtonsoft.Json.dll"));
    }
    return null;
}

// ......

public override void CreateNewOutputRows()
{
    /*
      Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
      For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
    */

    ReadStockQuote();
}

public void ReadStockQuote()
{
    string file = File.ReadAllText(Variables.SourceFileNameQuote);
    String jsonFileContent = "[" + file + "]";
    var roots = JsonConvert.DeserializeObject<IList<RootQuote>>(jsonFileContent);

    try
    {
        foreach (RootQuote root in roots)
        {
            OutputBuffer.AddRow();
            OutputBuffer.ApiDatetime = root.data.info.lastUpdatedAt;
            OutputBuffer.StockNo = root.data.info.symbolId;
            OutputBuffer.DayHighPrice = root.data.quote.priceHigh.price;
            OutputBuffer.DayHighTime = root.data.quote.priceHigh.at;
            OutputBuffer.DayLowPrice = root.data.quote.priceLow.price;
            OutputBuffer.DayLowTime = root.data.quote.priceLow.at;
             OutputBuffer.TransDatetime = DateTime.Now;
        }
    }
    catch (Exception ex)
    {
        //MessageBox.Show(ex.ToString());
    }
}

public class Stock_Quote
{
    public class Info
    {
        public DateTime date { get; set; }
        public string type { get; set; }
        public string exchange { get; set; }
        public string market { get; set; }
        public string symbolId { get; set; }
        public string countryCode { get; set; }
        public string timeZone { get; set; }
        public DateTime lastUpdatedAt { get; set; }
    }

    public class Total
    {
        public DateTime at { get; set; }
        public int transaction { get; set; }
        public double tradeValue { get; set; }
        public int tradeVolume { get; set; }
        public int tradeVolumeAtBid { get; set; }
        public int tradeVolumeAtAsk { get; set; }
        public int serial { get; set; }
    }

    public class Trial
    {
        public DateTime at { get; set; }
        public double bid { get; set; }
        public double ask { get; set; }
        public double price { get; set; }
        public int volume { get; set; }
    }

    public class Trade
    {
        public DateTime at { get; set; }
        public double bid { get; set; }
        public double ask { get; set; }
        public double price { get; set; }
        public int volume { get; set; }
        public int serial { get; set; }
    }

    public class Bid
    {
        public double price { get; set; }
        public int volume { get; set; }
    }

    public class Ask
    {
        public double price { get; set; }
        public int volume { get; set; }
    }

    public class Order
    {
        public DateTime at { get; set; }
        public List<Bid> bids { get; set; }
        public List<Ask> asks { get; set; }
    }

    public class PriceHigh
    {
        public decimal price { get; set; }
        public DateTime at { get; set; }
    }

    public class PriceLow
    {
        public decimal price { get; set; }
        public DateTime at { get; set; }
    }

    public class PriceOpen
    {
        public double price { get; set; }
        public DateTime at { get; set; }
    }

    public class PriceAvg
    {
        public double price { get; set; }
        public DateTime at { get; set; }
    }

    public class Quote
    {
        public bool isCurbing { get; set; }
        public bool isCurbingFall { get; set; }
        public bool isCurbingRise { get; set; }
        public bool isTrial { get; set; }
        public bool isDealt { get; set; }
        public bool isOpenDelayed { get; set; }
        public bool isCloseDelayed { get; set; }
        public bool isHalting { get; set; }
        public bool isClosed { get; set; }
        public Total total { get; set; }
        public Trial trial { get; set; }
        public Trade trade { get; set; }
        public Order order { get; set; }
        public PriceHigh priceHigh { get; set; }
        public PriceLow priceLow { get; set; }
        public PriceOpen priceOpen { get; set; }
        public PriceAvg priceAvg { get; set; }
        public double change { get; set; }
        public double changePercent { get; set; }
        public double amplitude { get; set; }
        public int priceLimit { get; set; }
    }

    public class Data
    {
        public Info info { get; set; }
        public Quote quote { get; set; }
    }

    public class RootQuote
    {
        public string apiVersion { get; set; }
        public Data data { get; set; }
    }
}

OLE DB 目的地

連接管理員：Stock_API
連接資料表：[dbo].[Stock_Quote]

檔案系統工作

IsDestinationPathVariable 屬性：False
DestinationConnection 屬性：Quote_Backup
OverwriteDestination 屬性：True
Operation 屬性：移動檔案
IsSourcePathVariable 屬性：True
SourceVariable 屬性：User::SourceFileName_Quote

Last updated 3 years ago