locked
Capture error rows using RowUpdated and UpdateBatchSize issue. RRS feed

  • Question

  • I have used a datadapter to load data from source table to destination table using insertcommand.command text and used

    DataApater.UpdateBatchSize = 1;

    And used  delegate DataApater_RowUpdated(object sender, SqlRowUpdatedEventArgs e)

    and try to capture error rows into an text file using streamwriter, When i ued DataApater.UpdateBatchSize = 1;  Able to capture data as it processing row by row.

    then to reduce the number of SQL call, I updated DataApater.UpdateBatchSize = 1000;

    Now it return an batch 1000 failures or may be 100 failure within the batch of 1000, when I try to capture errors it fetch only top most error and some times it fail with following error:

    Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.NullReferenceException: Object reference not set to an instance of an object.
       at ST_72f588e6164d40f4ac4bf7a94aeaf038.csproj.ScriptMain.da2_RowUpdated(Object sender, SqlRowUpdatedEventArgs e) in C:\Documents and Settings\dondapa1\Local Settings\Temp\SSIS\4c62f949625b4abf9d1582dcbe2a483e\ScriptMain.cs:line 145
       at System.Data.SqlClient.SqlDataAdapter.OnRowUpdated(RowUpdatedEventArgs value)
       at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
       at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
       at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
       at ST_72f588e6164d40f4ac4bf7a94aeaf038.csproj.ScriptMain.Main() in C:\Documents and Settings\dondapa1\Local Settings\Temp\SSIS\4c62f949625b4abf9d1582dcbe2a483e\ScriptMain.cs:line 125
       --- End of inner exception stack trace ---
       at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
       at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
       at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
       at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
       at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
       at System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture)
       at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

    How can i capture error rows when UpdateBatchsize is greater than 1.

    Code used in Dataadapter_Updatedrow:

     void da2_RowUpdated(object sender, SqlRowUpdatedEventArgs e)
            {
                if (e.Status == UpdateStatus.ErrorsOccurred)
                {
                    try
                    {
                       
                     
                        string RowInfo = "";
                        var sw = new StreamWriter("C:\\errorrows" + tablename+ ".log", true);
                   
                        for (int columnIndex = 0; columnIndex < e.Row.ItemArray.Length; columnIndex++)
                        {
                            string rowvalue = e.Row[columnIndex].ToString(); ;
                            RowInfo += rowvalue + " ";
                        }
                        sw.WriteLine(DateTime.Now.ToString() + "  " + RowInfo);
                        sw.Close();

                    }
                    catch (Exception ex)
                    {
                        DataRow[] test = new DataRow[e.RowCount];
                        e.CopyToRows(test);

                    }
                    
                }
            }

    When I increase the batch it always goes to catch block.


    Navin.D http://dnavin.wordpress.com

    • Moved by ArthurZ Friday, September 21, 2012 2:02 PM Does not relate to SSIS (From:SQL Server Integration Services)
    Friday, September 21, 2012 1:59 AM

All replies

  • Hi Navind,

    I'm doing research on this issue now, it may need sometime, thanks for your understanding.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Monday, September 24, 2012 2:04 AM
  • Hi Navind,

    In your 'da2_RowUpdated' event handler, you can access the row which throws error by 'e.Row'.

    var row = e.Row;

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Allen_MSDN Friday, September 28, 2012 2:06 AM
    • Unmarked as answer by Navind Monday, October 15, 2012 2:54 PM
    Monday, September 24, 2012 5:08 AM
  • I could not get all the rows with errors in the 10,000 batch but only one row returned, Can you provide detail code rather than one line answer. Suppose if its an batch size of 10,000 only 10 rows got error, i want to pick those 10 error rows and push them to text file.

    Dont provide half solution and mark as answer.....


    Navin.D http://dnavin.wordpress.com


    • Edited by Navind Monday, October 15, 2012 2:57 PM not answered by moderator properly
    Monday, October 15, 2012 2:56 PM
  • Hi Navind,

    Sorry for the incomplete solution. Please refer to the code below.

    protected static void OnRowUpdated(object sender, SqlRowUpdatedEventArgs e)
    {
        if (e.Status == UpdateStatus.ErrorsOccurred)
        {
            // Reference to row which throws error
            var row = e.Row;
    
            row.RowError = e.Errors.Message;
            e.Status = UpdateStatus.SkipCurrentRow;
    
            // Do something more
        }
    }
    

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, October 18, 2012 5:19 AM
  • It fetched only one row not all error rows :(

    Navin.D http://dnavin.wordpress.com

    Monday, October 29, 2012 1:11 PM