PostExecute() Output Buffer Foreach Rows

Question

I have the following code within a Script Component in SSIS.

I am trying to de-serialize the JSON Output and output the response to Database.

The de-serialize part is returning the response.

However I am getting an "Object reference not set to an instance of an object." error on the OutputBuffer.AddRow();

I am going around in circles. What am I doing wrong?

public class ScriptMain : UserComponent
{

   public override void PostExecute()
    {
        base.PostExecute();

        string vOpportunityURL = Variables.pardotopportunityurl;
        System.Windows.Forms.MessageBox.Show(vOpportunityURL);

        int vMaxOpportunityId = Variables.pardotopportunitymaxid;
        System.Windows.Forms.MessageBox.Show(Convert.ToString(vMaxOpportunityId));

        int vProcessedRecordCount = Variables.pardotrecordcnt;
        System.Windows.Forms.MessageBox.Show(Convert.ToString(vProcessedRecordCount));

        var vProcessDate = Variables.pardotprocessdt;
        System.Windows.Forms.MessageBox.Show(Convert.ToString(vProcessDate));

        RootObject oppOutputResponse = GetWebServiceResult(vOpportunityURL);

        foreach (Opportunity op in oppOutputResponse.result.opportunity)
        {

            OpportunityDataBuffer.AddRow();
            OpportunityDataBuffer.ID = op.id;
            System.Windows.Forms.MessageBox.Show(Convert.ToString(op.id));
            OpportunityDataBuffer.Name = op.name;
            System.Windows.Forms.MessageBox.Show(Convert.ToString(op.name));

        }


    }


    private RootObject GetWebServiceResult(string vOpportunityURL)
    { 
        // Create API WEeb Service Request

        HttpWebRequest opportunityFullDataReq = (HttpWebRequest)WebRequest.Create(vOpportunityURL);

        var opportunityDataPostStr = "user_key=";
            opportunityDataPostStr += Variables.pardotauthusrkey;
            opportunityDataPostStr += "&api_key=";
            opportunityDataPostStr += Variables.pardotapikey;
            opportunityDataPostStr += "&output=full";
            opportunityDataPostStr += "&format=json";
            opportunityDataPostStr += "&sort_by=id";
            opportunityDataPostStr += "&sort_order=ascending";
            opportunityDataPostStr += "&id_greater_than=";
            opportunityDataPostStr += Variables.pardotopportunitymaxid;

            System.Windows.Forms.MessageBox.Show(Convert.ToString(opportunityDataPostStr));
            System.Windows.Forms.MessageBox.Show(vOpportunityURL + Convert.ToString(opportunityDataPostStr));

        var opportunityPostStream = Encoding.ASCII.GetBytes(opportunityDataPostStr);

        opportunityFullDataReq.Method = "POST";
        opportunityFullDataReq.ContentType = "application/x-www-form-urlencoded";
        opportunityFullDataReq.ContentLength = opportunityPostStream.Length;

        using(var opportunityStream = opportunityFullDataReq.GetRequestStream())
        {
            opportunityStream.Write(opportunityPostStream, 0, opportunityPostStream.Length);
        }

        // Capture Web Service Respose

        HttpWebResponse opportunityFullDataResponse = (HttpWebResponse)opportunityFullDataReq.GetResponse();

        RootObject opportunityWSResponse = null;

        Stream opportunityJsonStream = opportunityFullDataResponse.GetResponseStream();
        string wsResponseString = null;

        using (StreamReader wsResponseReader = new StreamReader(opportunityJsonStream))
        {
            wsResponseString = wsResponseReader.ReadToEnd();
            wsResponseReader.Close();
        }

        JavaScriptSerializer wsResponseJson = new JavaScriptSerializer();

        //var serialJsonResponseString = wsResponseJson.Serialize(wsResponseString);
        System.Windows.Forms.MessageBox.Show(wsResponseString.ToString());

        opportunityWSResponse = wsResponseJson.Deserialize<RootObject>(wsResponseString);

        return opportunityWSResponse;
   }

Andrew Wei · Answer 1 · 2021-07-08T18:31:59.830

1

The output buffer doesn't exist in PostExecute.

You can check to see if the last row or you've gone through the buffer before calling the method to add things to the buffer.

Similar issue here: Can I add rows to Output Buffer in SSIS Script Component in PostExecute?

edited Jul 08 '21 at 18:31

answered Jul 08 '21 at 18:23

Andrew Wei

870
1
7
12

PostExecute() Output Buffer Foreach Rows

1 Answers1