Executing Stored Procedure for each InputRow + SSIS Script Component.
- by Nev_Rahd
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
