0

I have a Script Component in SSIS 2012 which takes the data from WebService(json), deserialize it and inserts into table.

This works correct but sometimes the Webservice is down and I get the 500 error. So I wanted to log these errors to the LogTable using another OutputBuffer.

So I came up with this:

for (int attempts = 0; attempts < 10; attempts++)
{
    try
        response = wr.GetResponse();
        break;
        // success = true;
    }
    catch (WebException ex)
    {
        //success = false;
        Output2Buffer.AddRow();
        Output2Buffer.Message = "Error 500, Waiting 30s and retrying";
        System.Threading.Thread.Sleep(30000);
    }
}

The problem is that the ammount of data I am retrieving from the webservice is huge and sometimes it takes hours to get these milions of records and If I do logging like this, it won't insert the Message to the log table immediately when the error occurs but only when the buffer will fill up to the 10000 rows / 10 MB's.

How to force OutputBuffer to pass rows one by one. Not waiting for the buffer to fill up.

Emile Bergeron
  • 17,074
  • 5
  • 83
  • 129
cybernetic87
  • 285
  • 1
  • 13
  • I dont think you can set the row size of a buffer inside the script component. The PipelineBuffer object which has RowSize property is not exposed and even then, the RowSize property is get only. The data flow task allows you to set the max rows/buffer size, but the buffer size param has a lower end which means that you may not get it low enough where only one row fits into the buffer. – Sam Kolli Dec 29 '16 at 17:07
  • Aside from your question, why do you need to insert the message immediately? Can it not wat? If this is something that needs to happen immediately, may be you can try using a SqlConnection (or similiar) to do an insert in your catch block? – Sam Kolli Dec 29 '16 at 17:11
  • I am adding some derived columns - one of them is a time when the issue happened. The other thing why I want insert to be immediate because of possibility of furher script failure - then nothing would get inserted. Script can fail in many steps, I wanted to create try / catch blocks in a few places in code and log immediately to the table using this Buffer. Awww... is there really no other option besides SqlConnection and inserting directly from C# script, I dont like this idea. It's just not like SSIS is supposed to work. Logging to the file is not an option for me,I do all the logging in db. – cybernetic87 Dec 29 '16 at 20:25
  • One more reason why I want this immediate is for monitoring purposes. Let's say I want to load 10mln records. This would take about 36 hours (3rd party client Webservice is slow - nothing can be done about it). I dont want to wait 2 days to figure out that the webservice was down for a whole day. I want to know it at the time when it is loading. This is data warehouse, when the process gets stuck, I want to know why. And I want to no why immediately, whenever I want. Not just when it finishes (or not) its job. You get my idea? – cybernetic87 Dec 29 '16 at 20:29
  • I have connection managers to few databases in my project. I don't want to connect to db via c# just because this stupid limitation.... there HAS to be some workaround for it. Sam, please think of something :(. I've tried the SetEndOfRowset method but it ends rowset for good. Maybe if there was a way to open the rowset again ? – cybernetic87 Dec 29 '16 at 20:34
  • I am afraid that (even with some "creative use" of PrimeOutput method), it is not possible to "reopen" the ScriptBuffer object. If you look at the code in the PrimeOutput method, you can see that ScriptBuffer object is created; I tried to recreate the same again after setting EndRowSet, but the EndRowSet property of the newly "created" ScriptBuffer object is still true. It is almost feels like, it is a static object and no new instances can be created. But even it can be, I would rather not mess around with this API and go into "creative" uses. – Sam Kolli Dec 29 '16 at 23:55
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/131849/discussion-between-sam-kolli-and-cybernetic87). – Sam Kolli Dec 29 '16 at 23:59

0 Answers0