Handling NULL values returned to an ODBC DataReader
You can call the IsDBNull method on the ODBCDataReader (http://msdn.microsoft.com/en-us/library/system.data.odbc.odbcdatareader.isdbnull.aspx). You will need to know the position of the column (0 is first column, etc). Or you can find the position by name using GetOrdinal method (http://msdn.microsoft.com/en-us/library/system.data.odbc.odbcdatareader.getordinal.aspx). I would assume that the column name argument is case sensitive Something like: Dim cmd As New System.Data.Odbc.OdbcCommand("Select FirstColumn, SecondColumn from table") Dim rdr As System.Data.Odbc.OdbcDataReader = cmd.ExecuteReader() Dim iColFirstColumn As Integer = rdr.GetOrdinal("FirstColumn") ' Alternately could use constant 0 Dim iColSecondColumn As Integer = rdr.GetOrdinal("SecondColumn") ' Alternately could use constant 1 rdr.Read() If rdr.IsDBNull(iColFirstColumn) And rdr.IsDBNull(iColSecondColumn) Then End If Russel Loski, MCT
January 15th, 2011 6:57am

Thanks for the reply. I have below code in my script component. I would like to apply isnull on rw.Item("dws_value") column. Please let me know what you find. 'player rank order If found4.Length > 0 Then For Each rw In found4 If CType(Row.playerrankmax, String) = CType(rw.Item("source_value"), String) Then rank_order = CType(rw.Item("dws_value"), Int32) End If Next End If outputBuffer.AddRow() outputBuffer.PLAYERKEY = Row.PLAYERKEY outputBuffer.playeradtcategory = adt_cat outputBuffer.playeradtorder = adt_order outputBuffer.playerfirstplaydate = Row.firstplaydate outputBuffer.playerlastplaydate = Row.lastplaydate outputBuffer.playerlastplaycategory = last_play_cat outputBuffer.playerlastplaycategoryorder = last_play_cat_order outputBuffer.playertype = player_type_desc outputBuffer.playerrankmax = Row.playerrankmax outputBuffer.playertiermax = Row.playertiermax outputBuffer.playertiermaxorder = Row.playertiermaxorder outputBuffer.playerrankmaxorder = rank_order End Sub Public Overrides Sub PreExecute() MyBase.PreExecute() Dim conn As New OleDb.OleDbConnection(Me.Connections.mardw.ConnectionString) Dim ole1 As New OleDb.OleDbDataAdapter("select * from dbo.tab_dw_sys_mapping_between_xref (nolock)", conn) Dim ole2 As New OleDb.OleDbDataAdapter("select * from dbo.tab_dw_sys_mapping_xref (nolock)", conn) ole1.Fill(tab1) ole2.Fill(tab2) tab1.Select(Nothing, "dws_column") tab2.Select(Nothing, "dws_column_name") conn.Close() End Sub Public Overrides Sub PostExecute() MyBase.PostExecute() tab1.Dispose() tab2.Dispose() End Sub srinivas
Free Windows Admin Tool Kit Click here and download it now
January 15th, 2011 7:04am

Thanks for the reply. I have below code in my script component. I would like to apply isnull on rw.Item("dws_value") column. Please let me know what you find. Your code is not using an ODBCDataReader, but rather a DataTable. rw.IsNull ("dws_value") will return whether the value in that column is null or not. http://msdn.microsoft.com/en-US/library/system.data.datarow.isnull(v=VS.80).aspx. Try the following: If rw.IsNull("source_value") = false andalso CType(Row.playerrankmax, String) = CType(rw.Item("source_value"), String) Then rank_order = CType(rw.Item("dws_value"), Int32) End If Russel Loski, MCT
January 15th, 2011 7:24am

Hi All, I have a SSIS package with script component (SQL Server 2008 R2). I am using sql query to read data inside the script component and writting it to output buffer. But in my source few columns had null values. How Can I handle NULL values inside the script component for OLEDB source query. Thank you Srini srinivas
Free Windows Admin Tool Kit Click here and download it now
January 15th, 2011 8:09am

we will find "_isNUll" option for the columns which are comming as input columns to script component not for the select query inside the script component. But I am using select query inside the script component. For the select query columns I want to use isnull. How Can I check ISNULL functionality? Thank you Srini srinivas
January 15th, 2011 8:19am

Each column in your Script Component should have a related column that ends in "_IsNull" It will tell you if the column is a null.Russel Loski, MCT
Free Windows Admin Tool Kit Click here and download it now
January 15th, 2011 8:31am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics