How to insert rows one by one with Script Component's Outputbuffer

714 views Asked by At

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.

0

There are 0 answers