none
Handling NULL values returned to an ODBC DataReader

    Question

  • 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
    Tuesday, January 11, 2011 12:54 AM

Answers

  • 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
    • Marked as answer by srinivasable Tuesday, January 11, 2011 7:41 PM
    Tuesday, January 11, 2011 6:15 PM

All replies

  • 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
    Tuesday, January 11, 2011 3:19 AM
  • 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
    Tuesday, January 11, 2011 11:08 AM
  • 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
    Tuesday, January 11, 2011 11:48 AM
  • 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
    Tuesday, January 11, 2011 5:55 PM
  • 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
    • Marked as answer by srinivasable Tuesday, January 11, 2011 7:41 PM
    Tuesday, January 11, 2011 6:15 PM