Executing Stored Procedure for each InputRow + SSIS Script Component.

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

Answer 1

Can you change the while for a do while loop instead?

do
{
   // all the gubbings here
} while (Row.NextRow());

Answer 2

Hello All,

Got it, my SqlCommand need to be in local scope of InputRows_ProcessInputRow and doesnt need to do NextRow(). Thanks

Answer 3

Is that because at the time of invocation of script component the pointer is at the first row and if you specify while loop with NextRow() always goes to second row?