SSIS script and null values

Thursday, March 07, 2013 9:24 PM

I have been trying to check the value of the first field and checking if it is a null, but every effort results in the same error. Licence is a string 6

Const r_licence As Integer = 0
Public Overrides Sub CreateNewOutputRows()
Try
            While sqlReader.Read
                If IsDBNull(sqlReader.GetValue(r_licence)) = True Then
                    MsgBox("NO LICENCE")
                Else
                    MsgBox(sqlReader.GetValue(r_licence))
                End If
            End While
           ' MainBuffer.SetEndOfRowset()
          '  If HasErrors = True Then
          '      ErrorBuffer.SetEndOfRowset()
          '  End If
        Catch ex As Exception

            '            Me.ComponentMetaData.FireError(0, "Script Component", "A Transformation error occurred. Check the corresponding Text File ", "", 0, True)

        End Try
    End Sub

Error Data is Null. This method or property cannot be called on Null values.

Dont ask me .. i dont know

All Replies

Thursday, March 07, 2013 9:47 PM

0

I think the line is:

sqlReader.IsDBNull(sqlReader.GetValue.....
Chuck Pedretti | Magenic – North Region | magenic.com
- Reply
- Quote

Thursday, March 07, 2013 10:01 PM

Hi Chuck ,

I have just tried that and caome back with the same result. The datasource is a stored procedure and if i run that stored procedure in SQL manager, licence is a null value.

if licence has a value then 

Me.MainBuffer.licence = sqlReader.GetString(sqlReader.GetOrdinal("licence"))

wotks fine. Im trying to error trap and fail the task if licence is null

If sqlReader.IsDBNull(sqlReader.GetOrdinal("licence")) Then
                    MsgBox("NO LICENCE")
                Else
                    MsgBox(sqlReader.GetString(sqlReader.GetOrdinal("licence")))
                End If

Data is Null. This method or property cannot be called on Null values.

Dont ask me .. i dont know

Thursday, March 07, 2013 10:19 PM

0

Try:

sqlReader.GetValue( r_licence ).Equals( DBNull.Value )
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
- Reply
- Quote
Thursday, March 07, 2013 10:29 PM

0

Hi same error.

the rest of the error message

at System.Data.SqlClient.SqlBuffer.get_String()
   at SC_4217a89b8a194b43b63ba59bebcf744b.ScriptMain.CreateNewOutputRows()
   at SC_4217a89b8a194b43b63ba59bebcf744b.UserComponent.PrimeOutput(Int32 Outputs, Int32[] OutputIDs, PipelineBuffer[] Buffers, OutputNameMap OutputMap)
   at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)
Dont ask me .. i dont know
- Reply
- Quote
Thursday, March 07, 2013 10:35 PM

0
Pete,

I don't see .String in the code I have sent. What is the code you are using?

p.s.

I think .NET is doing code optimization. Okay so what happens when you call:

sqlReader.IsDBNull( r_licence )

SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
- Edited by COZYROC Thursday, March 07, 2013 10:38 PM
- Reply
- Quote

Thursday, March 07, 2013 10:53 PM

same problem. It's not making sense to me, here is my code. As you can see i have commented out all the output buffers

    Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
        connMgr = Me.Connections.MyConnection
        sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)
    End Sub

   Public Overrides Sub PreExecute()
        MyBase.PreExecute()
        sqlCmd = New SqlCommand("sp_GetImport_nonCBI", sqlConn)
        sqlCmd.CommandType = CommandType.StoredProcedure
        sqlParam = New SqlParameter("@FileName", SqlDbType.NVarChar, 250)
        sqlParam.Direction = ParameterDirection.Input
        sqlParam.Value = Me.Variables.xmlFileName
        sqlCmd.Parameters.Add(sqlParam)
        sqlReader = sqlCmd.ExecuteReader
    End Sub


    Public Overrides Sub PostExecute()
        MyBase.PostExecute()
        sqlReader.Close()
    End Sub

    Const r_licence As Integer = 0
    Public Overrides Sub CreateNewOutputRows()

        Try
            Dim i As Integer = 0
            Dim HasErrors As Boolean = False
            While sqlReader.Read


                If Not sqlReader.IsDBNull(r_licence) Then
                    MsgBox("NO LICENCE")
                Else
                    MsgBox("XXXX")
                End If

                '                If i < 1 Then
                ' MainBuffer.AddRow()
                ' Me.MainBuffer.licence = sqlReader.GetString(sqlReader.GetOrdinal("licence"))
                ' Me.MainBuffer.serial = sqlReader.GetString(sqlReader.GetOrdinal("serial"))
                ' Me.MainBuffer.transsetid = sqlReader.GetString(sqlReader.GetOrdinal("transsetid"))
                ' Me.MainBuffer.rnumber = sqlReader.GetString(sqlReader.GetOrdinal("r_number"))
                ' Me.MainBuffer.rtype = sqlReader.GetString(sqlReader.GetOrdinal("r_type"))
                ' Me.MainBuffer.rdate = sqlReader.GetDateTime(sqlReader.GetOrdinal("r_date"))
                ' Me.MainBuffer.rtime = sqlReader.GetString(sqlReader.GetOrdinal("r_time"))
                ' Me.MainBuffer.rprocessingdate = sqlReader.GetDateTime(sqlReader.GetOrdinal("r_processingdate"))
                ' Me.MainBuffer.sreferencenumber = sqlReader.GetString(sqlReader.GetOrdinal("s_referencenumber"))
                ' Me.MainBuffer.smedia = sqlReader.GetString(sqlReader.GetOrdinal("s_media"))
                ' Me.MainBuffer.sbureauName = sqlReader.GetString(sqlReader.GetOrdinal("s_bureauName"))
                'Me.MainBuffer.svolumeserial = sqlReader.GetString(sqlReader.GetOrdinal("s_volumeserial"))
                ' Me.MainBuffer.ssponser = sqlReader.GetString(sqlReader.GetOrdinal("s_sponser"))
                ' Me.MainBuffer.sbureaunumber = sqlReader.GetString(sqlReader.GetOrdinal("s_bureaunumber"))
                ' Me.MainBuffer.uffilemessage = sqlReader.GetString(sqlReader.GetOrdinal("uf_filemessage"))
                ' Me.MainBuffer.ufiusernumber = sqlReader.GetString(sqlReader.GetOrdinal("ufi_usernumber"))
                ' Me.MainBuffer.ufiusername = sqlReader.GetString(sqlReader.GetOrdinal("ufi_username"))
                ' Me.MainBuffer.ufiuserfilenumber = sqlReader.GetString(sqlReader.GetOrdinal("ufi_userfilenumber"))
                ' Me.MainBuffer.ufisequencenumber = sqlReader.GetString(sqlReader.GetOrdinal("ufi_sequencenumber"))
                ' Me.MainBuffer.uficurrency = sqlReader.GetString(sqlReader.GetOrdinal("ufi_currency"))
                ' Me.MainBuffer.ufiuhl1code = sqlReader.GetString(sqlReader.GetOrdinal("ufi_uhl1code"))
                ' Me.MainBuffer.ufiprocessingdate = sqlReader.GetDateTime(sqlReader.GetOrdinal("ufi_processingdate"))
                ' Me.MainBuffer.clearedfunds = sqlReader.GetDateTime(sqlReader.GetOrdinal("clearedfunds"))
                ' Me.MainBuffer.ufifilereference = sqlReader.GetString(sqlReader.GetOrdinal("ufi_filereference"))
                ' Me.MainBuffer.ufiusersponser = sqlReader.GetString(sqlReader.GetOrdinal("ufi_usersponser"))
                ' Me.MainBuffer.dserrormessage = sqlReader.GetString(sqlReader.GetOrdinal("ds_errormessage"))
                ' Me.MainBuffer.dsrdayref = sqlReader.GetString(sqlReader.GetOrdinal("dsr_dayref"))
                ' Me.MainBuffer.dsrrecorddate = sqlReader.GetDateTime(sqlReader.GetOrdinal("dsr_recorddate"))
                ' Me.MainBuffer.dsrprocessingdate = sqlReader.GetDateTime(sqlReader.GetOrdinal("dsr_processingdate"))
                'Me.MainBuffer.dsrcnumberof = sqlReader.GetInt32(sqlReader.GetOrdinal("dsrc_numberof"))
                'Me.MainBuffer.dsrcccurrency = sqlReader.GetString(sqlReader.GetOrdinal("dsrc_currency"))
                ''Me.MainBuffer.dsrcvalueof = sqlReader.GetDecimal(sqlReader.GetOrdinal("dsrc_valueof"))
                'Me.MainBuffer.dsrccnumberof = sqlReader.GetInt32(sqlReader.GetOrdinal("dsrcc_numberof"))
                'Me.MainBuffer.dsrcccurrency = sqlReader.GetString(sqlReader.GetOrdinal("dsrcc_currency"))
                'Me.MainBuffer.dsrccvalueof = sqlReader.GetDecimal(sqlReader.GetOrdinal("dsrcc_valueof"))
                'Me.MainBuffer.dsrcnumberof = sqlReader.GetInt32(sqlReader.GetOrdinal("dsrd_numberof"))
                'Me.MainBuffer.dsrdccurrency = sqlReader.GetString(sqlReader.GetOrdinal("dsrd_currency"))
                'Me.MainBuffer.dsrcvalueof = sqlReader.GetDecimal(sqlReader.GetOrdinal("dsrd_valueof"))
                'Me.MainBuffer.dsrdcnumberof = sqlReader.GetInt32(sqlReader.GetOrdinal("dsrdc_numberof"))
                'Me.MainBuffer.dsrdccurrency = sqlReader.GetString(sqlReader.GetOrdinal("dsrdc_currency"))
                'Me.MainBuffer.dsrdcvalueof = sqlReader.GetDecimal(sqlReader.GetOrdinal("dsrdc_valueof"))
                'Me.MainBuffer.dstcnumberof = sqlReader.GetInt32(sqlReader.GetOrdinal("dstc_numberof"))
                'Me.MainBuffer.dstcccurrency = sqlReader.GetString(sqlReader.GetOrdinal("dstc_currency"))
                'Me.MainBuffer.dstcvalueof = sqlReader.GetDecimal(sqlReader.GetOrdinal("dstc_valueof"))
                'Me.MainBuffer.dstccnumberof = sqlReader.GetInt32(sqlReader.GetOrdinal("dstcc_numberof"))
                'Me.MainBuffer.dstcccurrency = sqlReader.GetString(sqlReader.GetOrdinal("dstcc_currency"))
                'Me.MainBuffer.dstccvalueof = sqlReader.GetDecimal(sqlReader.GetOrdinal("dstcc_valueof"))
                'Me.MainBuffer.dstdnumberof = sqlReader.GetInt32(sqlReader.GetOrdinal("dstd_numberof"))
                'Me.MainBuffer.dstdccurrency = sqlReader.GetString(sqlReader.GetOrdinal("dstd_currency"))
                'Me.MainBuffer.dstdvalueof = sqlReader.GetDecimal(sqlReader.GetOrdinal("dstd_valueof"))
                'Me.MainBuffer.dstdcnumberof = sqlReader.GetInt32(sqlReader.GetOrdinal("dstdc_numberof"))
                'Me.MainBuffer.dstdccurrency = sqlReader.GetString(sqlReader.GetOrdinal("dstdc_currency"))
                'Me.MainBuffer.dstdcvalueof = sqlReader.GetDecimal(sqlReader.GetOrdinal("dstdc_valueof"))
                'Me.MainBuffer.irsatnumber = sqlReader.GetString(sqlReader.GetOrdinal("irsat_number"))
                'Me.MainBuffer.irsatsortcode = sqlReader.GetString(sqlReader.GetOrdinal("irsat_sortcode"))
                'Me.MainBuffer.irsattype = sqlReader.GetString(sqlReader.GetOrdinal("irsat_type"))
                'Me.MainBuffer.atcarnumberof = sqlReader.GetInt32(sqlReader.GetOrdinal("atcar_numberof"))
                'Me.MainBuffer.atcarcurrency = sqlReader.GetString(sqlReader.GetOrdinal("atcar_currency"))
                'Me.MainBuffer.atcarvalueof = sqlReader.GetDecimal(sqlReader.GetOrdinal("atcar_valueof"))
                'Me.MainBuffer.atcrrnumberof = sqlReader.GetInt32(sqlReader.GetOrdinal("atcrr_numberof"))
                'Me.MainBuffer.atcrrcurrency = sqlReader.GetString(sqlReader.GetOrdinal("atcrr_currency"))
                'Me.MainBuffer.atcrrvalueof = sqlReader.GetDecimal(sqlReader.GetOrdinal("atcrr_valueof"))
                'Me.MainBuffer.atctrnumberof = sqlReader.GetInt32(sqlReader.GetOrdinal("atctr_numberof"))
                'Me.MainBuffer.atctrcurrency = sqlReader.GetString(sqlReader.GetOrdinal("atctr_currency"))
                'Me.MainBuffer.atctrvalueof = sqlReader.GetDecimal(sqlReader.GetOrdinal("atctr_valueof"))
                'Me.MainBuffer.atdarnumberof = sqlReader.GetInt32(sqlReader.GetOrdinal("atdar_numberof"))
                'Me.MainBuffer.atdarcurrency = sqlReader.GetString(sqlReader.GetOrdinal("atdar_currency"))
                'Me.MainBuffer.atdarvalueof = sqlReader.GetDecimal(sqlReader.GetOrdinal("atdar_valueof"))
                'Me.MainBuffer.atdrrnumberof = sqlReader.GetInt32(sqlReader.GetOrdinal("atdrr_numberof"))
                'Me.MainBuffer.atdrrcurrency = sqlReader.GetString(sqlReader.GetOrdinal("atdrr_currency"))
                'Me.MainBuffer.atdrrvalueof = sqlReader.GetDecimal(sqlReader.GetOrdinal("atdrr_valueof"))
                'Me.MainBuffer.atdtrnumberof = sqlReader.GetInt32(sqlReader.GetOrdinal("atdtr_numberof"))
                'Me.MainBuffer.atdtrcurrency = sqlReader.GetString(sqlReader.GetOrdinal("atdtr_currency"))
                'Me.MainBuffer.atdtrvalueof = sqlReader.GetDecimal(sqlReader.GetOrdinal("atdtr_valueof"))
                'Me.MainBuffer.atccarnumberof = sqlReader.GetInt32(sqlReader.GetOrdinal("atccar_numberof"))
                'Me.MainBuffer.atccarcurrency = sqlReader.GetString(sqlReader.GetOrdinal("atccar_currency"))
                'Me.MainBuffer.atccarvalueof = sqlReader.GetDecimal(sqlReader.GetOrdinal("atccar_valueof"))
                'Me.MainBuffer.atccrrnumberof = sqlReader.GetInt32(sqlReader.GetOrdinal("atccrr_numberof"))
                'Me.MainBuffer.atccrrcurrency = sqlReader.GetString(sqlReader.GetOrdinal("atccrr_currency"))
                'Me.MainBuffer.atccrrvalueof = sqlReader.GetDecimal(sqlReader.GetOrdinal("atccrr_valueof"))
                'Me.MainBuffer.atcctrnumberof = sqlReader.GetInt32(sqlReader.GetOrdinal("atcctr_numberof"))
                'Me.MainBuffer.atcctrcurrency = sqlReader.GetString(sqlReader.GetOrdinal("atcctr_currency"))
                'Me.MainBuffer.atcctrvalueof = sqlReader.GetDecimal(sqlReader.GetOrdinal("atcctr_valueof"))
                'Me.MainBuffer.atdcarnumberof = sqlReader.GetInt32(sqlReader.GetOrdinal("atdcar_numberof"))
                'Me.MainBuffer.atdcarcurrency = sqlReader.GetString(sqlReader.GetOrdinal("atdcar_currency"))
                'Me.MainBuffer.atdcarvalueof = sqlReader.GetDecimal(sqlReader.GetOrdinal("atdcar_valueof"))
                'Me.MainBuffer.atdcrrnumberof = sqlReader.GetInt32(sqlReader.GetOrdinal("atdcrr_numberof"))
                'Me.MainBuffer.atdcrrcurrency = sqlReader.GetString(sqlReader.GetOrdinal("atdcrr_currency"))
                'Me.MainBuffer.atdcrrvalueof = sqlReader.GetDecimal(sqlReader.GetOrdinal("atdcrr_valueof"))
                'Me.MainBuffer.atdctrnumberof = sqlReader.GetInt32(sqlReader.GetOrdinal("atdctr_numberof"))
                'Me.MainBuffer.atdctrcurrency = sqlReader.GetString(sqlReader.GetOrdinal("atdctr_currency"))
                'Me.MainBuffer.atdctrvalueof = sqlReader.GetDecimal(sqlReader.GetOrdinal("atdctr_valueof"))
                'Me.MainBuffer.aoftcarnumberof = sqlReader.GetInt32(sqlReader.GetOrdinal("aoftcar_numberof"))
                'Me.MainBuffer.aoftcarcurrency = sqlReader.GetString(sqlReader.GetOrdinal("aoftcar_currency"))
                'Me.MainBuffer.aoftcarvalueof = sqlReader.GetDecimal(sqlReader.GetOrdinal("aoftcar_valueof"))
                'Me.MainBuffer.aoftcrrnumberof = sqlReader.GetInt32(sqlReader.GetOrdinal("aoftcrr_numberof"))
                'Me.MainBuffer.aoftcrrcurrency = sqlReader.GetString(sqlReader.GetOrdinal("aoftcrr_currency"))
                'Me.MainBuffer.aoftcrrvalueof = sqlReader.GetDecimal(sqlReader.GetOrdinal("aoftcrr_valueof"))
                'Me.MainBuffer.aoftcurnumberof = sqlReader.GetInt32(sqlReader.GetOrdinal("aoftcur_numberof"))
                'Me.MainBuffer.aoftcurcurrency = sqlReader.GetString(sqlReader.GetOrdinal("aoftcur_currency"))
                'Me.MainBuffer.aoftcurvalueof = sqlReader.GetDecimal(sqlReader.GetOrdinal("aoftcur_valueof"))
                'Me.MainBuffer.aoftcajrnumberof = sqlReader.GetInt32(sqlReader.GetOrdinal("aoftcajr_numberof"))
                'Me.MainBuffer.aoftcajrcurrency = sqlReader.GetString(sqlReader.GetOrdinal("aoftcajr_currency"))
                'Me.MainBuffer.aoftcajrvalueof = sqlReader.GetDecimal(sqlReader.GetOrdinal("aoftcajr_valueof"))
                'Me.MainBuffer.aoftdarnumberof = sqlReader.GetInt32(sqlReader.GetOrdinal("aoftdar_numberof"))
                'Me.MainBuffer.aoftdarcurrency = sqlReader.GetString(sqlReader.GetOrdinal("aoftdar_currency"))
                'Me.MainBuffer.aoftdarvalueof = sqlReader.GetDecimal(sqlReader.GetOrdinal("aoftdar_valueof"))
                'Me.MainBuffer.aoftdrrnumberof = sqlReader.GetInt32(sqlReader.GetOrdinal("aoftdrr_numberof"))
                'Me.MainBuffer.aoftdrrcurrency = sqlReader.GetString(sqlReader.GetOrdinal("aoftdrr_currency"))
                'Me.MainBuffer.aoftdrrvalueof = sqlReader.GetDecimal(sqlReader.GetOrdinal("aoftdrr_valueof"))
                'Me.MainBuffer.aoftdurnumberof = sqlReader.GetInt32(sqlReader.GetOrdinal("aoftdur_numberof"))
                'Me.MainBuffer.aoftdurcurrency = sqlReader.GetString(sqlReader.GetOrdinal("aoftdur_currency"))
                'Me.MainBuffer.aoftdurvalueof = sqlReader.GetDecimal(sqlReader.GetOrdinal("aoftdur_valueof"))
                'Me.MainBuffer.aoftdajrnumberof = sqlReader.GetInt32(sqlReader.GetOrdinal("aoftdajr_numberof"))
                'Me.MainBuffer.aoftdajrcurrency = sqlReader.GetString(sqlReader.GetOrdinal("aoftdajr_currency"))
                'Me.MainBuffer.aoftdajrvalueof = sqlReader.GetDecimal(sqlReader.GetOrdinal("aoftdajr_valueof"))
                'HasErrors = sqlReader.GetBoolean(sqlReader.GetOrdinal("haserrors"))
                'End If

                ' If HasErrors = True Then

                '                ErrorBuffer.AddRow()
                '                Me.ErrorBuffer.transsetid = sqlReader.GetString(sqlReader.GetOrdinal("transsetid"))
                '                Me.ErrorBuffer.errortype = sqlReader.GetString(sqlReader.GetOrdinal("errortype"))
                '                Me.ErrorBuffer.messageline = sqlReader.GetString(sqlReader.GetOrdinal("messageline"))
                '                Me.ErrorBuffer.erroritemtype = sqlReader.GetString(sqlReader.GetOrdinal("erroritemtype"))
                '                Me.ErrorBuffer.reference = sqlReader.GetString(sqlReader.GetOrdinal("reference"))
                '                Me.ErrorBuffer.transactioncode = sqlReader.GetString(sqlReader.GetOrdinal("transactioncode"))
                '                Me.ErrorBuffer.currency = sqlReader.GetString(sqlReader.GetOrdinal("currency"))
                '                Me.ErrorBuffer.valueof = sqlReader.GetDecimal(sqlReader.GetOrdinal("valueof"))
                '                Me.ErrorBuffer.errorcode = sqlReader.GetString(sqlReader.GetOrdinal("errorcode"))
                '                Me.ErrorBuffer.origaccno = sqlReader.GetString(sqlReader.GetOrdinal("origaccno"))
                '                Me.ErrorBuffer.destaccno = sqlReader.GetString(sqlReader.GetOrdinal("destaccno"))
                '                Me.ErrorBuffer.origref = sqlReader.GetString(sqlReader.GetOrdinal("origref"))
                '                Me.ErrorBuffer.origaccname = sqlReader.GetString(sqlReader.GetOrdinal("origaccname"))
                '                Me.ErrorBuffer.destaccname = sqlReader.GetString(sqlReader.GetOrdinal("destaccname"))
                '                Me.ErrorBuffer.orgsortcode = sqlReader.GetString(sqlReader.GetOrdinal("orgsortcode"))
                '                Me.ErrorBuffer.destsortcode = sqlReader.GetString(sqlReader.GetOrdinal("destsortcode"))
                '                Me.ErrorBuffer.desttype = sqlReader.GetString(sqlReader.GetOrdinal("desttype"))
                '                End If


                '                i += 1


            End While
            '  MainBuffer.SetEndOfRowset()
            '  If HasErrors = True Then
            ' ErrorBuffer.SetEndOfRowset()
            ' End If
        Catch ex As Exception

            '            Me.ComponentMetaData.FireError(0, "Script Component", "A Transformation error occurred. Check the corresponding Text File ", "", 0, True)

        End Try
    End Sub

Dont ask me .. i dont know

Thursday, March 07, 2013 10:55 PM

0
What is the exact error? In the CATCH routine report: ex.ToString()
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
- Marked As Answer by Pete Newman Tuesday, March 12, 2013 9:52 AM
- Reply
- Quote
Thursday, March 07, 2013 11:08 PM

0

ok, this is weird.

if i put a breakpoint at the line 'If Not sqlReader.IsDBNull(r_licence) Then' and step through, I get the msgbox up and no errors.

If i remove the breakpoint i get the origional error - Data is Null. This method or property cannot be called on Null values.

Dont ask me .. i dont know
- Reply
- Quote

SSIS script and null values

All Replies

Products

Resources

Solutions

Updates

Trials

Related Sites

Training

Certifications

Other resources

Support by product

Other support links

Not an IT pro?