locked
SSIS Script task JSON error RRS feed

  • Question

  • I have built a package that updates a json file every time I run it, the next stage is to load single row of data to a table.

    I am using script task to load it.

    following is my json file: https://query2.finance.yahoo.com/v7/finance/quote?symbols=AAPL

    the script component needs to just identify the different key-value pairs as column headers and column value.

    Configured the outputs accordingly:

    #region Namespaces
    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
    using Microsoft.SqlServer.Dts.Runtime.Wrapper;
    using System.Web.Script.Serialization;
    using Microsoft.SqlServer.Dts.Pipeline;
    #endregion
    
    namespace SC_ce793b89c26a4be48b4f5354892c6d24
    { 
    
    [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
    public class ScriptMain : UserComponent
    {
    
    public override void PreExecute()
    {
        base.PreExecute();
    
    }
    
    public override void PostExecute()
    {
    
            base.PostExecute();
    
    }
    
    class result
    {
        public string language { get; set;}
        public string quoteType { get; set; }
        public string quoteSourceName { get; set; }
        public string currency { get; set; }
        public decimal sharesOutstanding { get; set; }
        public decimal bookValue { get; set; }
        public decimal fiftyDayAverage { get; set; }
        public decimal epsTrailingTwelveMonths { get; set; }
        public decimal forwardPE { get; set; }
        public decimal priceToBook { get; set; }
        public decimal trailingAnnualDividendYield { get; set; }
        public string tradeable { get; set; }
        public decimal fiftyDayAverageChange { get; set; }
        public decimal fiftyDayAverageChangePercent { get; set; }
        public decimal twoHundredDayAverage { get; set; }
        public decimal twoHundredDayAverageChange { get; set; }
        public decimal priceHint { get; set; }
        public decimal fiftyTwoWeekLowChange { get; set; }
        public string fullExchangeName { get; set; }
        public string longName { get; set; }
        public string financialCurrency { get; set; }
        public decimal averageDailyVolume3Month { get; set; }
        public decimal averageDailyVolume10Day { get; set; }
        public decimal fiftyTwoWeekLow { get; set; }
        public decimal fiftyTwoWeekHigh { get; set; }
        public decimal dividendDate { get; set; }
        public string shortName { get; set; }
        public string marketState { get; set; }
        public decimal regularMarketChangePercent { get; set; }
        public decimal regularMarketPreviousClose { get; set; }
        public decimal bid { get; set; }
        public decimal ask { get; set; }
        public decimal bidSize { get; set; }
        public decimal askSize { get; set; }
        public string messageBoardId { get; set; }
        public decimal epsForward { get; set; }
        public decimal twoHundredDayAverageChangePercent { get; set; }
        public decimal marketCap { get; set; }
        public decimal exchangeDataDelayedBy { get; set; }
        public string exchange { get; set; }
        public decimal regularMarketPrice { get; set; }
        public decimal regularMarketTime { get; set; }
        public decimal regularMarketChange { get; set; }
        public decimal regularMarketOpen { get; set; }
        public decimal regularMarketDayHigh { get; set; }
        public decimal regularMarketDayLow { get; set; }
        public decimal regularMarketVolume { get; set; }
        public string market { get; set; }
        public decimal sourceInterval { get; set; }
        public string exchangeTimezoneName { get; set; }
        public string exchangeTimezoneShortName { get; set; }
        public decimal gmtOffSetMilliseconds { get; set; }
        public decimal earningsTimestamp { get; set; }
        public decimal earningsTimestampStart { get; set; }
        public decimal earningsTimestampEnd { get; set; }
        public decimal trailingAnnualDividendRate { get; set; }
        public decimal trailingPE { get; set; }
        public decimal fiftyTwoWeekLowChangePercent { get; set; }
        public decimal fiftyTwoWeekHighChange { get; set; }
        public decimal fiftyTwoWeekHighChangePercent { get; set; }
        public string symbol { get; set; }
    }
    class quoteResponse
    { public result result { get; set; }
        }
    
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        JavaScriptSerializer js = new JavaScriptSerializer();
    
        BlobColumn combinedColumn = Row.Column0;
    
        string reviewConverted = System.Text.Encoding.ASCII.GetString(combinedColumn.GetBlobData(0, Convert.ToInt32(combinedColumn.Length)));
    
        quoteResponse quoteResponse = js.Deserialize<quoteResponse>(reviewConverted);
    
        Row.regularMarketChangePercent = quoteResponse.result.regularMarketChangePercent;
        Row.regularMarketChange = quoteResponse.result.regularMarketChange;
        Row.regularMarketDayHigh = quoteResponse.result.regularMarketDayHigh;
        Row.regularMarketDayLow = quoteResponse.result.regularMarketDayLow;
        Row.epsTrailingTwelveMonths = quoteResponse.result.epsTrailingTwelveMonths;
        Row.regularMarketPrice = quoteResponse.result.regularMarketPrice;
        Row.marketCap = quoteResponse.result.marketCap;
        Row.trailingPE = quoteResponse.result.trailingPE;
        Row.symbol = quoteResponse.result.symbol;
        Row.regularMarketVolume = quoteResponse.result.regularMarketVolume;
        Row.fiftyTwoWeekHigh = quoteResponse.result.fiftyTwoWeekHigh;
        Row.fiftyTwoWeekLow = quoteResponse.result.fiftyTwoWeekLow;
        Row.xask = quoteResponse.result.ask;
        Row.xaskSize = quoteResponse.result.askSize;
        Row.xaverageDailyVolume10Day = quoteResponse.result.averageDailyVolume10Day;
        Row.xaverageDailyVolume3Month = quoteResponse.result.averageDailyVolume3Month;
        Row.xbid = quoteResponse.result.bid;
        Row.xbidSize = quoteResponse.result.bidSize;
        Row.xbookValue = quoteResponse.result.bookValue;
        Row.xcurrency = quoteResponse.result.currency;
        Row.xdividendDate = quoteResponse.result.dividendDate;
        Row.xearningsTimestamp = quoteResponse.result.earningsTimestamp;
        Row.xearningsTimestampEnd = quoteResponse.result.earningsTimestampEnd;
        Row.xearningsTimestampStart = quoteResponse.result.earningsTimestampStart;
        Row.xepsForward = quoteResponse.result.epsForward;
        Row.xexchange = quoteResponse.result.exchange;
        Row.xexchangeDataDelayedBy = quoteResponse.result.exchangeDataDelayedBy;
        Row.xexchangeTimezoneName = quoteResponse.result.exchangeTimezoneName;
        Row.xexchangeTimezoneShortName = quoteResponse.result.exchangeTimezoneShortName;
        Row.xfiftyDayAverage = quoteResponse.result.fiftyDayAverage;
        Row.xfiftyDayAverageChange = quoteResponse.result.fiftyDayAverageChange;
        Row.xfiftyDayAverageChangePercent = quoteResponse.result.fiftyDayAverageChangePercent;
        Row.xfiftyTwoWeekHighChange = quoteResponse.result.fiftyTwoWeekHighChange;
        Row.xfiftyTwoWeekHighChangePercent = quoteResponse.result.fiftyTwoWeekHighChangePercent;
        Row.xfiftyTwoWeekLowChange = quoteResponse.result.fiftyTwoWeekLowChange;
        Row.xfiftyTwoWeekLowChangePercent = quoteResponse.result.fiftyTwoWeekLowChangePercent;
        Row.xfinancialCurrency = quoteResponse.result.financialCurrency;
        Row.xforwardPE = quoteResponse.result.forwardPE;
        Row.xfullExchangeName = quoteResponse.result.fullExchangeName;
        Row.xgmtOffSetMilliseconds = quoteResponse.result.gmtOffSetMilliseconds;
        Row.xlanguage = quoteResponse.result.language;
        Row.xlongName = quoteResponse.result.longName;
        Row.xmarket = quoteResponse.result.market;
        Row.xmarketState = quoteResponse.result.marketState;
        Row.xmessageBoardId = quoteResponse.result.messageBoardId;
        Row.xpriceHint = quoteResponse.result.priceHint;
        Row.xpriceToBook = quoteResponse.result.priceToBook;
        Row.xquoteSourceName = quoteResponse.result.quoteSourceName;
        Row.xquoteType = quoteResponse.result.quoteType;
        Row.xregularMarketOpen = quoteResponse.result.regularMarketOpen;
        Row.xregularMarketPreviousClose = quoteResponse.result.regularMarketPreviousClose;
        Row.xregularMarketTime = quoteResponse.result.regularMarketTime;
        Row.xsharesOutstanding = quoteResponse.result.sharesOutstanding;
        Row.xshortName = quoteResponse.result.shortName;
        Row.xsourceInterval = quoteResponse.result.sourceInterval;
        Row.xtradeable = quoteResponse.result.tradeable;
        Row.xtrailingAnnualDividendRate = quoteResponse.result.trailingAnnualDividendRate;
        Row.xtrailingAnnualDividendYield = quoteResponse.result.trailingAnnualDividendYield;
        Row.xtwoHundredDayAverage = quoteResponse.result.twoHundredDayAverage;
        Row.xtwoHundredDayAverageChange = quoteResponse.result.twoHundredDayAverageChange;
        Row.xtwoHundredDayAverageChangePercent = quoteResponse.result.twoHundredDayAverageChangePercent;
    
            }
                            }
    }
    I do not get any error on build but I get one on execute:

    at SC_ce793b89c26a4be48b4f5354892c6d24.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row) at UserComponent.Input0_ProcessInput(Input0Buffer Buffer) at UserComponent.ProcessInput(Int32 InputID, String InputName, PipelineBuffer Buffer, OutputNameMap OutputMap) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponent.ProcessInput(Int32 InputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

    [Script Component [63]] Error: System.NullReferenceException: Object reference not set to an instance of an object.
       at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)
       at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)
       at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper100 wrapper, Int32 inputID, IDTSBuffer100 pDTSBuffer, IntPtr bufferWirePacket)

    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Script Component" (63) failed with error code 0x80004003 while processing input "Input 0" (75). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

    As far as I know I am not doing anything wrong, just can't get this thing to run. Any help is appreciated.




    Dhananjay Rele


    • Edited by DJ Rele Thursday, November 9, 2017 4:11 PM Correction
    Thursday, November 9, 2017 4:04 PM

Answers

  • Hi DJ Rele,

    Please do mask your sensitive data before uploading the file.

    It seems that the model the JSon object with c# is not correct, please refer to following code snippet.

        public override void CreateNewOutputRows()
        {
         
    
            JavaScriptSerializer js = new JavaScriptSerializer();
            byte[] jsonbyte = System.IO.File.ReadAllBytes(connMgr.ConnectionString);
            string reviewConverted = System.Text.Encoding.ASCII.GetString(jsonbyte);
    
            RootObject rootObject= js.Deserialize<RootObject>(reviewConverted);
    
            Output0Buffer.AddRow();
    
            foreach(Result r in rootObject.quoteResponse.result)
            {
                Output0Buffer.xlanguage = r.language;
            }
    
        }
    
    }
    
    public class Result
    {
        public string language { get; set; }
    }
    
    public class QuoteResponse
    {
        public List<Result> result { get; set; }
        public object error { get; set; }
    }
    
    public class RootObject
    {
        public QuoteResponse quoteResponse { get; set; }
    }

    There is a free tool available that will take your JSON and give the respective c# object structure. See: json2csharp

    Based on my test, it should work.

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    • Proposed as answer by Visakh16MVP Monday, November 13, 2017 8:00 AM
    • Marked as answer by DJ Rele Monday, November 13, 2017 10:15 PM
    • Edited by Pirlo Zhang Wednesday, November 29, 2017 9:10 AM
    Monday, November 13, 2017 7:54 AM

All replies

  • Hi DJ Rele,

    Just process the JSON using any .net library. Once converted into tabular format load into a staging table.

    And then you can update it however.


    Arthur

    MyBlog


    Twitter

    Thursday, November 9, 2017 8:42 PM
  • Hi DJ Rele,

    Please do mask your sensitive data before uploading the file.

    It seems that the model the JSon object with c# is not correct, please refer to following code snippet.

        public override void CreateNewOutputRows()
        {
         
    
            JavaScriptSerializer js = new JavaScriptSerializer();
            byte[] jsonbyte = System.IO.File.ReadAllBytes(connMgr.ConnectionString);
            string reviewConverted = System.Text.Encoding.ASCII.GetString(jsonbyte);
    
            RootObject rootObject= js.Deserialize<RootObject>(reviewConverted);
    
            Output0Buffer.AddRow();
    
            foreach(Result r in rootObject.quoteResponse.result)
            {
                Output0Buffer.xlanguage = r.language;
            }
    
        }
    
    }
    
    public class Result
    {
        public string language { get; set; }
    }
    
    public class QuoteResponse
    {
        public List<Result> result { get; set; }
        public object error { get; set; }
    }
    
    public class RootObject
    {
        public QuoteResponse quoteResponse { get; set; }
    }

    There is a free tool available that will take your JSON and give the respective c# object structure. See: json2csharp

    Based on my test, it should work.

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    • Proposed as answer by Visakh16MVP Monday, November 13, 2017 8:00 AM
    • Marked as answer by DJ Rele Monday, November 13, 2017 10:15 PM
    • Edited by Pirlo Zhang Wednesday, November 29, 2017 9:10 AM
    Monday, November 13, 2017 7:54 AM