Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Dynamic Table to Excel 2005 Sql Expand / Collapse
Posted Tuesday, August 6, 2013 10:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 7, 2013 8:07 AM
Points: 5, Visits: 13
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()
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)
Reader = Cmd.ExecuteReader()
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)


Dim MaxField = Reader.FieldCount
Dim columnName As String
While (Reader.Read())
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.

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 ?

Post #1481431
Posted Tuesday, September 17, 2013 7:41 AM

Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, April 24, 2015 5:10 AM
Points: 1,118, Visits: 2,233
I would say propInfo is NULL. That's why you are getting the error.

SSIS Tasks Components Scripts Services |

Post #1495508
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse