none
SSIS Script Component Fails because of occasional null value(s) in a certain date field

    Question

  • Greeings,

    I am using Visual Studio 2008 and currently have a package that works fine, until I add a certain date column to my script component (vb).  There are 2 records in the source dataset that are null for this date (though all of the other columns have data that they still want to capture).

    I noticed that if I simply delete these 2 rows (with the null date) in my source Excel file, that the package then runs just fine. The customer does not want me to discard records like this however, since they want to capture the other columns of data and ignore this date when it is missing.

    Is there a way I can make my package work in spite of the occasional missing date(s)?

    If it helps..... the name of the column (with the occasional nulls) I am adding is:  EDDEPARTUREDATE

    (If I remove this column or if I delete the rows in my source file with the null dates it works fine)

    ith oFileStream
                    .Write("STAGEDATA~" & Me.Variables.sFacility & "~" & Row.BILLINGID & "~" & sIndicator & "~" & "Core Measures" & _
                    "~" & sIndicator & "~ED-TPT" & "~" & Row.ENCOUNTERDATE & "~" & Row.ARRIVALTIME & "~" & Row.EDDEPARTUREDATE & "~" & Row.EDDEPARTURETIME & _
                    "~" & Row.EDARRIVALTODEPART & "~" & Row.MEDIANTIMETOEDDEPARTUREOP18A & "~" & Row.TIMETOEDDEPARTUREREPORTINGOP18B & "~" & _
                    "~" & Row.TIMETOEDDEPARTUREOBSOP18C & "~" & Row.TIMETOEDDEPARTUREPSYCHOP18D & "~" & Row.TIMETOEDDEPARTURETRANSOP18E & "~" & _
                    "~" & Row.DOORTODIAGNOSTICEVALOP20 & "~" & sNumerator & "~" & sDenominator & "~" & sOutlier & "~" & sValue & "~1~" & _
                    "~" & "" & "~" & "" & "~" & "" & "~" & "" & "~" & "" & "~" & "" & "~" & "" & "~" & "" & "~")
                    .WriteLine()
                End With
    Thanks!

    • Edited by DaveDVF Thursday, April 17, 2014 3:25 PM Typ-O
    Thursday, April 17, 2014 3:24 PM

Answers

  • Thanks... I wonderd what the 'EDDEPARTUREDATE_IsNull' was for. If I tried to substitiue the '..._IsNull' name in my code above, it returns the value 'false' (which makes sense), but it doesn't seem to accept the 'IF Not ... ' logic, at least not in that section of the code (if only I knew a little bit more about vb and where to use this logic.... seems like a great solution).

    if the NOT isn't working then you could also try with an equals to false. Something like (I'm not a VB guy, more C#):

    If EDDEPARTUREDATE_IsNull = false Then


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Thursday, April 17, 2014 7:54 PM
    Moderator
  • Besides a Row.EDDEPARTUREDATE there is also a Row.EDDEPARTUREDATE_IsNull property available in the Script Component. You can use it to check for null values. Or you can use the standard .Net functionality like Koen mentioned.

    If Not EDDEPARTUREDATE_IsNull Then
        ' do something
    End If

    The code you use in the Derived Column isn't working because "" isn't a date. It should be something like:
    REPLACENULL(EDDEPARTUREDATE, "1900-01-01"), but not sure you want that...

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Thursday, April 17, 2014 6:09 PM
    Moderator
  • Got it!!! Thanks

    I inserted this code prior to "with oFilestream..." section of the vbcode

    Dim sFix As String If Row.EDDEPARTUREDATE_IsNull = False Then sFix = Row.EDDEPARTUREDATE Else sFix = "" End If

     

    I then simply pulled in  that variable to the "with oFilestream..." section of my code above (where the EDDEPARTUREDATE used to be, and it works. The output (an fdr file) now has the dates. The rows where the date was null is empty (as it should be) and the package now runs without error.  I couldn't have done this without you guys!  Thanks again!!!!!

    Thursday, April 17, 2014 8:16 PM

All replies

  • .NET has plenty of function to deal with NULL values.
    I'm not sure which language you are using, but you can for example use IsNullOrEmpty. If it is null, simply replace the value with an empty string.

    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Thursday, April 17, 2014 3:44 PM
  • Thank You. I am new to all of this (mostly self-taught) so although I understand what you are saying, I am not sure how to implement it in vb code. It gave me an idea though... one that I might be able to manage better. Maybe I could convert the nulls to empty strings prior to the data making it to my script component, by using a Derived column (change nulls to empty string that way). The problem now is that I am struggling with the expression to do that.

    ...wondering if you think that would work and what the expression should say (see my attempt here):

    The name of the source column has spaces (ED DEPARTURE DATE)

    Thanks again!

    Dave

    Thursday, April 17, 2014 4:54 PM
  • EDIT: apparently the REPLACENULL does exist :) Somehow complete forgot it was introduced in SQL Server 2012...

    The expression is as follows:

    ISNULL([ED DEPARTURED DATE]) ? "" : [ED DEPARTURED DATE]


    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.


    Thursday, April 17, 2014 6:03 PM
  • Ow yeah, to answer your other question: yes, replacing NULL values before the script task can certainly help :)

    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Thursday, April 17, 2014 6:04 PM
  • Besides a Row.EDDEPARTUREDATE there is also a Row.EDDEPARTUREDATE_IsNull property available in the Script Component. You can use it to check for null values. Or you can use the standard .Net functionality like Koen mentioned.

    If Not EDDEPARTUREDATE_IsNull Then
        ' do something
    End If

    The code you use in the Derived Column isn't working because "" isn't a date. It should be something like:
    REPLACENULL(EDDEPARTUREDATE, "1900-01-01"), but not sure you want that...

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Thursday, April 17, 2014 6:09 PM
    Moderator
  • The code you use in the Derived Column isn't working because "" isn't a date. It should be something like:

    REPLACENULL(EDDEPARTUREDATE, "1900-01-01"), but not sure you want that...
    Riiight, we were working with dates :)

    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Thursday, April 17, 2014 6:16 PM
  • Thanks... I wonderd what the 'EDDEPARTUREDATE_IsNull' was for. If I tried to substitiue the '..._IsNull' name in my code above, it returns the value 'false' (which makes sense), but it doesn't seem to accept the 'IF Not ... ' logic, at least not in that section of the code (if only I knew a little bit more about vb and where to use this logic.... seems like a great solution).
    • Edited by DaveDVF Thursday, April 17, 2014 7:51 PM type-O
    Thursday, April 17, 2014 7:47 PM
  • I see.... so it sounds like the Derived Column technique won't work in  this case (cannot convert null date field to an empty string). Too bad.... would have been great to have this fixed before the script component. They wouldn't be happy if I put in a bogus date instead of leaving it empty. Thanks again for your response.
    • Edited by DaveDVF Thursday, April 17, 2014 7:52 PM Type-O
    Thursday, April 17, 2014 7:50 PM
  • Thanks... I wonderd what the 'EDDEPARTUREDATE_IsNull' was for. If I tried to substitiue the '..._IsNull' name in my code above, it returns the value 'false' (which makes sense), but it doesn't seem to accept the 'IF Not ... ' logic, at least not in that section of the code (if only I knew a little bit more about vb and where to use this logic.... seems like a great solution).

    if the NOT isn't working then you could also try with an equals to false. Something like (I'm not a VB guy, more C#):

    If EDDEPARTUREDATE_IsNull = false Then


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Thursday, April 17, 2014 7:54 PM
    Moderator
  • Got it!!! Thanks

    I inserted this code prior to "with oFilestream..." section of the vbcode

    Dim sFix As String If Row.EDDEPARTUREDATE_IsNull = False Then sFix = Row.EDDEPARTUREDATE Else sFix = "" End If

     

    I then simply pulled in  that variable to the "with oFilestream..." section of my code above (where the EDDEPARTUREDATE used to be, and it works. The output (an fdr file) now has the dates. The rows where the date was null is empty (as it should be) and the package now runs without error.  I couldn't have done this without you guys!  Thanks again!!!!!

    Thursday, April 17, 2014 8:16 PM