SSIS Script Component Closing Reader

Question

I am having an issue with my SSIS Script Component. It seems that my Reader is getting closed before my CreateNewOutputRows method gets a hold of it. Would anyone be able to help me out?

OdbcConnection odbcConn;
OdbcCommand odbcCmd;
OdbcDataReader odbcReader;

public override void PreExecute()
{
    base.PreExecute();
    using (odbcConn = new OdbcConnection(this.Connections.InformixODBC.ConnectionString))
    {
        odbcConn.Open();
        string cmdText = Variables.INFORMIXQUERY;
        cmdText = cmdText.Replace("{{START_DATETIME}}", "'" + Variables.STARTDATETIME + "'");
        cmdText = cmdText.Replace("{{END_DATETIME}}", "'" + Variables.ENDDATETIME + "'");

        odbcCmd = new OdbcCommand(cmdText, odbcConn);
        odbcReader = odbcCmd.ExecuteReader();
    }
}

This is how I have the reader currently set up. I stepped through and it seems to exit out of the PreExecute method and go into the CreateNewOutputRows method, but I cant do any of my AddRow calls because the reader is closed.

Any help would be much appreciated. Thanks!

score 1 · Accepted Answer · answered Oct 14 '14 at 18:08

A Reader is a connected via your Connection. The Connection is disposed of once the using statement goes out of scope and voila, your reader is now disconnected from the source.

Instead, you'd need to load your results into a DataSet/DataTable in your PreExecute and then enumerate the results whenever you're ready.

That or push your PreExecute logic into the CreateNewOutputRows method.

MSDN Links for working with ResultSet and filling a DataSet

SSIS Script Component Closing Reader

1 Answers1