0

i am having an issue when trying to use a c# script task as a data source for a data flow task in SSIS 2012. I have a much larger query I am going to run, but for now I just wanted to prove out that this would work, but so far it won't. Below is the code, and is only returning one field, however once it gets to the line which is last name = reader.getstring(), it throws an exception saying no data for the row/column is available. I know for a fact the query is returning 10 rows, not sure what is going on. I wrote the code following this link: https://msdn.microsoft.com/en-us/library/ms136060.aspx

Any advice?

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Data.OleDb;


[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

OleDbDataReader reader;
OleDbConnection myConnection;
OleDbCommand myCommand;

public override void PreExecute()
{
    base.PreExecute();


    string sConnectionString = "Provider=MSDAORA.1;User ID = USER;Password=PASS;Data Source=SERVER;persist security info = false";
  //  string oracleQuery = Variables.OracleSQL;

    string oracleQuery = "select Name from Name_Table where rownum < 10";

    myConnection = new OleDbConnection(sConnectionString);
    myCommand = new OleDbCommand(oracleQuery, myConnection);

    myConnection.Open();

    reader = myCommand.ExecuteReader();

}

/// <summary>
/// This method is called after all the rows have passed through this component.
///
/// You can delete this method if you don't need to do anything here.
/// </summary>
public override void PostExecute()
{
    base.PostExecute();

    reader.Close();
    /*
     * Add your code here
     */
}

public override void CreateNewOutputRows()
{
   Output0Buffer.AddRow();
   Output0Buffer.Name = reader.GetString(0);

}

}

user3494110
  • 417
  • 2
  • 9
  • 25

1 Answers1

0

From msdn:

The default position of the OleDbDataReader is before the first record. Therefore, you must call Read to start accessing any data.

You also need to call the Read method when moving to the next row. Therefore, try the following in your script:

public override void CreateNewOutputRows()
{
   while (reader.Read())
   {
      Output0Buffer.AddRow();
      Output0Buffer.Name = reader.GetString(0);
   }
}

In fact, this is the approach taken in the page you linked to.

PMah
  • 686
  • 5
  • 24