Object Reference not set to an instance of an object at ScriptMain.CreateNewOutputRows()

Question

I have this dataset:

001abcdefghijklmnopqrstuvwxyz
003jfaoijfpoisajeoaijefoaijofija
005;lksajdf;lkjsafd;lkjdf
006;ldsjfa;ojfda;okjdfa;lkjfda
009;akjfd;lakjfd;lajfd;lakjfd;la
013jjsjfkdjf;ldsajfljkd;fjkdfjdfj
001kfjfdjfkdjfosjisjfojesfljsijfesli   <-- New Record

I am using a Script Transformation to transform these rows into columns:

    private StreamReader SR;
    private string File1;

    public override void AcquireConnections(object Transaction)
    {
        // Get the connection for File1
        IDTSConnectionManager100 CM = this.Connections.OILFILEMGR;
        File1 = (string)CM.AcquireConnection(null);
    }

    public override void PreExecute()
    {
        // Create a reader for File1
        base.PreExecute();
        SR = new StreamReader(File1);
    }

    public override void PostExecute()
    {
        // Close the reader
        base.PostExecute();
        SR.Close();
    }

    public override void CreateNewOutputRows()
    {
        // Declare variables
        string nextLine;
        int LineNumber;

        nextLine = SR.ReadLine();
        LineNumber = 1;

        while (nextLine != null)
        {
                //MessageBox.Show(nextLine);

                // Add a row
                Output0Buffer.AddRow();

                Output0Buffer.APINumber = nextLine.Substring(5, 6);

                Output0Buffer.County = nextLine.Substring(2, 3);

                Output0Buffer.District = nextLine.Substring(14, 2);

                nextLine = SR.ReadLine();
                LineNumber = LineNumber + 1;

                while (nextLine.Substring(0, 2) != "01")
                {

                        if (nextLine.Substring(0, 2) == "02")
                        {
                            Output0Buffer.LeaseNumber = nextLine.Substring(5, 5);
                        }

                        if (nextLine.Substring(0, 2) == "09")
                        {
                            Output0Buffer.FormationName = nextLine.Substring(5, 32);
                        }

                        if (nextLine.Substring(0, 2) == "13")
                        {
                            Output0Buffer.Latitude = nextLine.Substring(132, 10);
                            Output0Buffer.Longitude = nextLine.Substring(142, 10);
                        }

                    nextLine = SR.ReadLine();
                    LineNumber = LineNumber + 1;

                }

         }

    }

}

I tried to split the datasets in half, and I noticed that the number of rows being processed went down with each split by half as well. This leads me to believe the code works, but is not closing out correctly. I believe when the last record is recorded, somehow the script errors out.

What am I missing here? Thanks.

If your database is SQL Server load the data as it is in the database and use PIVOT in sql script, it will be much faster to transform rows to columns [link](http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server). — observer, Apr 27 '17 at 05:37
that may work, but the requirement given to me is that it must be done in one shot. no staging. I figured out the problem. it runs 35K/s. good enough for me. — arcee123, Apr 27 '17 at 13:59

score 0 · Accepted Answer · answered Apr 27 '17 at 14:05

After much tinkering, I ran into this C#.net staple. If you have a null line, don't try to Subset it.

I changed:

while (nextLine.Substring(0, 2) != "01")

to:

while(nextLine != null && nextLine.Substring(0, 2) != "01")

Since the nextLine = SR.ReadLine(); is prior to the substring, you have to check against null.

Enjoy.

Object Reference not set to an instance of an object at ScriptMain.CreateNewOutputRows()

1 Answers1