Dynamic Table to Excel 2005 Sql

• AAAV

  SSC Veteran

  Points: 287

  More actions

  August 6, 2013 at 10:18 am

  #278334

  I have a script task which has 100 predefined output columns

  I have a source table whose column count changes

  I am trying to set the values to the output columns if the column is there.

  If the column is not there it will not be set (null)

  I have a script task to do this.

  Public Overrides Sub PreExecute()

  MyBase.PreExecute()

  Dim Conn As System.Data.SqlClient.SqlConnection

  Dim Cmd As System.Data.SqlClient.SqlCommand

  Conn = New System.Data.SqlClient.SqlConnection("Data Source=servername;Initial Catalog=databasename;Integrated Security=SSPI;")

  Cmd = New System.Data.SqlClient.SqlCommand("Select * from dbo.Temp_Totals", Conn)

  Conn.Open()

  Reader = Cmd.ExecuteReader()

  ''Reader.c()

  End Sub

  Public Overrides Sub CreateNewOutputRows()

  '

  ' Add rows by calling AddRow method on member variable called "<Output Name>Buffer"

  ' E.g., MyOutputBuffer.AddRow() if your output was named "My Output"

  '

  Dim output = ComponentMetaData.OutputCollection(0)

  'MsgBox(Microsoft.VisualBasic.Information.TypeName(output))

  Dim MaxField = Reader.FieldCount

  Dim columnName As String

  While (Reader.Read())

  OutputValuesBuffer.AddRow()

  For i As Integer = 0 To MaxField - 1

  columnName = "column" + i.ToString()

  Dim propInfo As System.Reflection.PropertyInfo

  propInfo = OutputValuesBuffer.GetType().GetProperty(columnName)

  propInfo.SetValue(OutputValuesBuffer, Reader(i), Nothing)

  'OutputValuesBuffer.GetType().GetProperty(columnName).SetValue(OutputValuesBuffer, Reader(i), Nothing) ---> i just want to set the column value from the reader.

  Next

  End While

  End Sub

  I am getting object reference not set to an instance of an object in the line

  propInfo.SetValue(OutputValuesBuffer, Reader(i), Nothing)

  What am I doing wrong ?

  Thanks

• COZYROC

  One Orange Chip

  Points: 28499

  More actions

  September 17, 2013 at 7:41 am

  #1650794

  I would say propInfo is NULL. That's why you are getting the error.

  ---
  SSIS Tasks Components Scripts Services | http://www.cozyroc.com/