SSIS script component as source Null value

Question

I have package in SSIS that loads data using adomd.Net from essbase cube. With 2 columns package works fine, but when I add 3. column package fails.

Error message says:

Object reference not set to an instance of an object.

Third column in the first row contains null and some values in other rows.

Problem looks in null value in the 3. column. I tried this if statement but I get empty column instead of values.

AdomdDataReader reader = null;
    try
    {
        using (AdomdConnection conn = new AdomdConnection(connectionString))
        {
            conn.Open();
            using (AdomdCommand cmd = new AdomdCommand(query, conn))
            {
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {

                    Output0Buffer.AddRow();

                    Output0Buffer.Column = (reader.GetString(0));
                    Output0Buffer.Column1 = (reader.GetString(1));

                    if (!reader.IsDBNull(2))
                    {
                        Output0Buffer.Column2 = "test";
                    }
                    else
                    {
                        Output0Buffer.Column2 = (reader.GetString(2));
                    }

                    Console.WriteLine("fin");
                }
            }

        }
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex);

        throw;
    }

please add breakpoints and debug the code to identify the row that throws the exception, then you will get better help — Hadi, Jan 25 '18 at 14:12
Right now, the code is only populating a value in the column if it is null `!reader.IsDBNull(2)` -- I think remove the exclamation point and see if that works — Mark Wojciechowicz, Jan 25 '18 at 14:19

score 2 · Accepted Answer · answered Jan 25 '18 at 16:35

This bit of the code is working the opposite way as intended:

 if (!reader.IsDBNull(2))
  {
      Output0Buffer.Column2 = "test";
  }
  else
  {
      Output0Buffer.Column2 = (reader.GetString(2));
  }

!reader.IsDBNull(2) means where the value is not null. Removing the exclamation point will fix the problem

SSIS script component as source Null value

1 Answers1