none
Script Component + Buffer.NextRow() => skips frist row

    Question

  • Hello,

    In my Script Component, am trying to execute Stored Procedure => which return multiple rows => of which need to generate output rows.

    Code as below:

    /* Microsoft SQL Server Integration Services Script Component 
     *  Write scripts using Microsoft Visual C# 2008. 
     *  ScriptMain is the entry point class of the script.*/ 
     
      using System; 
      using System.Data; 
      using System.Data.SqlClient; 
      using Microsoft.SqlServer.Dts.Pipeline.Wrapper; 
      using Microsoft.SqlServer.Dts.Runtime.Wrapper; 
     
      [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute] 
      public class ScriptMain : UserComponent 
    { 
    SqlConnection cnn = new SqlConnection(); 
    IDTSConnectionManager100 cnManager; 
    //string cmd; 
    SqlCommand cmd = new SqlCommand(); 
     
    public override void AcquireConnections(object Transaction) 
    { 
        cnManager = base.Connections.myConnection; 
        cnn = (SqlConnection)cnManager.AcquireConnection(null); 
    } 
     
    public override void PreExecute() 
    { 
        base.PreExecute(); 
    } 
     
    public override void PostExecute() 
    { 
        base.PostExecute(); 
    } 
     
    public override void InputRows_ProcessInputRow(InputRowsBuffer Row) 
    { 
         while(Row.NextRow())   
         { 
            DataTable dt = new DataTable(); 
            cmd.Connection = cnn; 
            cmd.CommandText = "OSPATTRIBUTE_GetOPNforOP"; 
            cmd.CommandType = CommandType.StoredProcedure; 
            cmd.Parameters.Add("@NK", SqlDbType.VarChar).Value = Row.OPNK.ToString(); 
            cmd.Parameters.Add("@EDWSTARTDATE", SqlDbType.DateTime).Value = Row.EDWEFFECTIVESTARTDATETIME; 
            SqlDataAdapter adapter = new SqlDataAdapter(cmd); 
            adapter.Fill(dt); 
     
            foreach (DataRow dtrow in dt.Rows) 
            { 
                OutputValidBuffer.AddRow(); 
                OutputValidBuffer.OPNK = Row.OPNK; 
                OutputValidBuffer.OSPTYPECODE = Row.OSPTYPECODE; 
                OutputValidBuffer.ORGPROVTYPEDESC = Row.ORGPROVTYPEDESC; 
                OutputValidBuffer.HEALTHSECTORCODE = Row.HEALTHSECTORCODE; 
                OutputValidBuffer.HEALTHSECTORDESCRIPTION = Row.HEALTHSECTORDESCRIPTION; 
                OutputValidBuffer.EDWEFFECTIVESTARTDATETIME = Row.EDWEFFECTIVESTARTDATETIME; 
                OutputValidBuffer.EDWEFFECTIVEENDDATETIME = Row.EDWEFFECTIVEENDDATETIME; 
                OutputValidBuffer.OPQI = Row.OPQI; 
     
                OutputValidBuffer.OPNNK = dtrow[0].ToString(); 
                OutputValidBuffer.OSPNAMETYPECODE = dtrow[1].ToString(); 
                OutputValidBuffer.NAMETYPEDESC = dtrow[2].ToString(); 
                OutputValidBuffer.OSPNAME = dtrow[3].ToString(); 
                OutputValidBuffer.EDWEFFECTIVESTARTDATETIME1 = Row.EDWEFFECTIVESTARTDATETIME; 
                OutputValidBuffer.EDWEFFECTIVEENDDATETIME1 = Row.EDWEFFECTIVEENDDATETIME; 
                OutputValidBuffer.OPNQI = dtrow[6].ToString(); 
     
            } 
     
         } 
    } 
    public override void ReleaseConnections() 
    { 
        cnManager.ReleaseConnection(cnn); 
    } 
    

    }

    This is always skipping the first row.

    while(Row.NextRow()) is always bringing the second row of the input buffer.

    What am I doing wrong.

    Thanks

    Thursday, June 03, 2010 5:09 AM

Answers

  • public override void InputRows_ProcessInputRow(InputRowsBuffer Row)  will execute for each row. What is the purpose of "while" here? Try remove the while loop.
    Thursday, June 03, 2010 7:07 AM

All replies

  • public override void InputRows_ProcessInputRow(InputRowsBuffer Row)  will execute for each row. What is the purpose of "while" here? Try remove the while loop.
    Thursday, June 03, 2010 7:07 AM
  • Thanks.

    Yes  I fixed it .  Other problem was my SqlCommand cmd = new SqlCommand(); 
    should be in local scope of  InputRows_ProcessInputRow(InputRowsBuffer Row).

     

    Thursday, June 03, 2010 10:41 PM
  • Then declare it inside

    public override void InputRows_ProcessInputRow(InputRowsBuffer Row) 

    Am I got your question correct? If not explain it in detail.

    Friday, June 04, 2010 6:25 AM