outputting null values when converting vb transformation from dts to ssis

Question

I am struggling with converting some old dts packages that use ActiveX VBScript transformations to SSIS packages. The input is an 8 character string in YYYYMMDD format. The transformation is converting it to a date if possible. If not, the value should be NULL.

Here is the dts code

TheYear = Mid(DTSSource("SHIP_DATE"),1,4)
TheMonth = Mid(DTSSource("SHIP_DATE"),5,2)
TheDay = Mid(DTSSource("SHIP_DATE"),7,2)
TheDate = TheMonth &"/"&TheDay&"/"&TheYear

If IsDate(TheDate) Then
    DTSDestination("SHIP_DATE") = TheDate
End If

Here is what I have for the SSIS transformation

Dim TheYear As String
Dim TheMonth As String
Dim TheDay As String
Dim TheDate As String

If Row.SHIPDATE.Length >= 8 Then
    TheYear = Row.SHIPDATE.Substring(0, 4)
    TheMonth = Row.SHIPDATE.Substring(4, 2)
    TheDay = Row.SHIPDATE.Substring(6, 2)
    TheDate = TheMonth & "/" & TheDay & "/" & TheYear
    If IsDate(TheDate) Then
        Row.OutShipDate = TheDate
    Else
        Row.OutShipDate = Nothing
    End If
Else
    Row.OutShipDate = Nothing
End If

I have tried formatting OutShipDate as both date and database date. For an invalid date input string such as "00000000", I get either 1899-12-30 or 0001-01-01 in my database column depending on the dataype of OutShipDate. If I don't set Row.OutShipDate to anything in the VBScript, the SSIS execution fails completely.

Can I output a null value from the VBScript transformation?

Mych · Answer 1 · 2014-04-09T18:53:55.730

0

Should you not convert to date....

Row.OutShipDate = CDate(TheDate)

Is the DB column set to allow NULL?

UPDATE

If IsDate(TheDate) Then
    Row.OutShipDate = CDate(TheDate)
Else
    Row.OutShipDate = DBNull.value
End If

Another Update...

If IsDate(TheDate) Then
    Row.OutShipDate = DateTime.Parse(TheDate("MM/dd/yyyy"))
Else
    Row.OutShipDate = DirectCast(Nothing, System.Nullable(Of DateTime))
End If

This is what worked for me....

Dim TheDate As String = "36/29/2014"
'Dim TheDate As String = "4/9/2014"    

    Dim DDB As Date

    If IsDate(TheDate) Then
        DDB = CDate(TheDate)
    Else
        DDB = Nothing
    End If

edited Apr 09 '14 at 18:53

answered Apr 09 '14 at 15:49

Mych

2,527
4
36
65

Thanks for suggestion. Yes, DB destination column allows NULL. CDate(TheDate) caused package execution to fail. – jessieloo Apr 09 '14 at 16:16
Ok can you give a few example of what TheDate as a string is. If possible good and bad ones. – Mych Apr 09 '14 at 17:48
a vaild date is 20140409 for 4/9/14, most of the invalid dates are 00000000 and I want to return Null for those – jessieloo Apr 09 '14 at 17:52
Ugh... That returns Nullable object must have a value. at System.Nullable`1.get_Value(). I think I'm going to give up and set it to Nothing and then do a separate sql update statement to set everything that is '0001-01-01' to NULL. I appreciate your help though. – jessieloo Apr 09 '14 at 18:44
I got the basis of the answer from http://stackoverflow.com/questions/5685355/how-to-set-sql-datetime-to-null-from-linq but this was in c# which I got converted but must have lost something in translation... – Mych Apr 09 '14 at 18:49
1

@jessieloo I just came across http://stackoverflow.com/questions/102084/hidden-features-of-vb-net and there is a section about nullable dates sent in by David T Macket... It's way down the list so keep scrolling. – Mych Apr 10 '14 at 13:29
That was a helpful link but I don't think it solves the issue with the SSIS. I found the answer which is to set Row.OutShipDate_IsNull to true. Not sure if that's actually part of VB.net or something that just applies to vb transformation in ssis. I appreciate your help, though! – jessieloo Apr 11 '14 at 20:59

score 0 · Accepted Answer · answered Apr 11 '14 at 20:56

0

I found the answer and now that I know what it is, it seems stupidly easy.

Basically, if I want to return a NULL date, the code should be

Row.OutShipDate_IsNull = True

This link helped ultimately

answered Apr 11 '14 at 20:56

jessieloo

1,759
17
24

outputting null values when converting vb transformation from dts to ssis

2 Answers2