SSIS Script Component reading web data but not creating the Output0Buffer object

Question

I am using SSIS with a Script Component to retrieve data from a web service and put it into SQL Server. However, in the output, the Output0 object (default name) is visible on designtime but is no object at runtime.

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Collections.Generic;
using System.Text;
using System.Web.Script.Serialization;
using System.IO;
using System.Net;
using System.Diagnostics; // For trace         
//https://stackoverflow.com/questions/6446619/how-to-debug-a-script-    
component-in-ssis
using smsnamespace;
#endregion

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

/// Started with this:
/// https://www.mssqltips.com/sqlservertip/3495/extracting-api-data- 
using-powershell-and-loading-into-sql-server/
/// continued with this, being more flexible
/// 
https://gist.github.com/danieljarolim/1b6e2c2575f17d8a477f3135d36f99c9
/// 

public String downloadURL = "https://web.mywebsite.com/api/query? 
entity=sms&api_key=c652414a6&take=100";
String jsonFileContent;

public static string DownloadJson(string downloadURL)
{
    using (WebClient client = new WebClient())
    {
        return client.DownloadString(downloadURL);
    }
}


public override void PreExecute()
{  // This works fine
    base.PreExecute();
    Trace.WriteLine("SSIS download!");
    jsonFileContent = DownloadJson(downloadURL);
    Trace.WriteLine("SSIS download done!");
}


public override void PostExecute()
{
    base.PostExecute();
    Trace.WriteLine("SSIS " + downloadURL);
    JavaScriptSerializer js = new JavaScriptSerializer();
    js.MaxJsonLength = 500 * 1000000;

    Trace.WriteLine("SSIS downloaded");

    dynamic sfResult = js.DeserializeObject(jsonFileContent);

    int i = 0;
    foreach (var therecord in sfResult)
        i++;
    Trace.WriteLine("SSIS lines:"+i);  //<< this works fine!

    CreateNewOutputRows(); // <<just a try

    foreach (var therecord in sfResult)

    {
        Trace.WriteLine("SSIS Id");
        //Trace.WriteLine("SSIS " + therecord.Id);            
        Output0Buffer.AddRow(); // << *******  THIS FAILS! ^*****
                                // but is recognized by Intellisense.
        Trace.WriteLine("SSIS Ia");
        // 
        Output0Buffer.Id = (uint)therecord ["Id"];
        //.....
    }
    Trace.WriteLine("SSIS PE finished!");
}

public override void CreateNewOutputRows()
{    // NOTE Allegedly this method is never invoked if there is no Input 
       source
    /*
      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".
    */

    // 
    Trace.WriteLine("SSIS outputrows A");
    Output0Buffer.AddRow(); // <<<< *********** THIS FAILS! **********
    Trace.WriteLine("SSIS outputrows B");
}
}

Program fails at Output0Buffer.AddRow(). Output0 is indeed an Output object, with some 20 columns. Any suggestions?

this might help: https://stackoverflow.com/a/55229423/10376537 — Tim Mylott, Jun 14 '19 at 18:00

score 0 · Answer 1 · answered Jun 14 '19 at 17:51

Try ditching the base.PreExecute() call inside of PreExecute method. Also, don't call CreateNewOutputRows inside of PostExecute; it should be called automatically by the lower classes without you triggering it. Give this code a shot:

private readonly string _downloadUrl = "https://web.mywebsite.com/api/query?entity=sms&api_key=c652414a6&take=100";
private dynamic _sfResult;

public static string DownloadJson(string downloadURL)
{
    using (WebClient client = new WebClient())
    {
        return client.DownloadString(downloadURL);
    }
}    

public override void PreExecute()
{  
    Trace.WriteLine("SSIS download!");
    var jsonFileContent = DownloadJson(_downloadUrl);
    Trace.WriteLine("SSIS download done!");

    JavaScriptSerializer js = new JavaScriptSerializer();
    js.MaxJsonLength = 500 * 1000000;
    _sfResult = js.DeserializeObject(jsonFileContent);
}

public override void CreateNewOutputRows()
{    
    foreach (var therecord in _sfResult)
    {
        Trace.WriteLine("SSIS Id");        
        Output0Buffer.AddRow();        // 
        Output0Buffer.Id = (uint)therecord ["Id"];
        //.....
    }
}

score 0 · Answer 2 · answered Jun 15 '19 at 05:04

The main problem is that you are calling CreateNewOutputRows within PostExecute method since this method will be called automatically. I suggest reading the following guidline provided by Microsoft to learn more about each function:

Creating a Source with the Script Component

SSIS Script Component reading web data but not creating the Output0Buffer object

2 Answers2