error 30311: Value of type 'System.DBNull' cannot be converted to 'Date'

I'm creating an ISP for extractig data from a text file and put it in a database.

One of the fields in my textfile contains the value '0' or a date. If it's '0' it should be converted to the Null Value. The column in which it has to be saved is of type smalldatetime.

This is the code of my script where I want to check the value of the field in my textfile and convert it to a date or to Null.

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

If Row.cdDateIn = "" Or Row.cdDateIn= "0" Then

Row.cdDateInCorr = CDate(DBNull.Value)

ElseIf Row.cdDateIn_IsNull Then

Row.cdDateInCorr = CDate(DBNull.Value)

Else

Row.cdDateInCorr = CDate(Row.cdDateIn)

End If

End Sub

The error that I get is:

Validation error. Extract FinCD: Conversion of cdDateIn [753]: Eroor 30311: Value of type 'System.DBNull' cannot ben converted to 'Date'.

How do I resolve this problem ?

Why not use a derived column? Expression follows: (Typing from memory)

cdDateIn == "" || cdDateIn == "0" || ISNULL(cdDateIn) ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)cdDateIn

|||You can just set Row.cdDateInCorr_IsNull = true.
|||

JayH wrote:

You can just set Row.cdDateInCorr_IsNull = true.

Correct, though if this is all you're doing in the script component, I would stay away from that and use a derived column. There's no reason for the overhead of the script in this case.

|||

I've used the code JayH suggested and this works.

Phil, your code is a bit like chinese to me. This ISP I'm working on is my first project in .NET. But I will check this derived column thing.

Thanks four your help.

|||

Phil Brammer wrote:

JayH wrote:

You can just set Row.cdDateInCorr_IsNull = true.

Correct, though if this is all you're doing in the script component, I would stay away from that and use a derived column. There's no reason for the overhead of the script in this case.

Are you sure about this - the IsNull property is for evaluation and not for assigning true or false to it - I believe that it will reset itself when you leave the script task - or am I wrong?
|||

Simon Givoni wrote:

Phil Brammer wrote:

JayH wrote:

You can just set Row.cdDateInCorr_IsNull = true.

Correct, though if this is all you're doing in the script component, I would stay away from that and use a derived column. There's no reason for the overhead of the script in this case.

Are you sure about this - the IsNull property is for evaluation and not for assigning true or false to it - I believe that it will reset itself when you leave the script task - or am I wrong?

Yes, I'm sure. You can set the property to True and the null will stick. You cannot set it to False, but it will automatically become False if you set a value to the column. SP2 will raise an exception if you set it to False.

|||Ah yes you are right - here are the full explanation:

The IsNull property of columns in the Script Component now raises a warning when it is used incorrectly.
The code generated by the Script component contains a <columnname> and a <columnname>_IsNull property for each input and output column. The intention of the <columnname>_IsNull property is to allow the user to specify a Null value for the column by setting the value of the property to True. To specify a non-null value, the user should assign a value to the <columnname> property. In earlier versions, if you set <columnname>_IsNull to False, the property was silently reset to True. In SP2, the Script component raises a warning message about the incorrect usage.

Source: http://msdn2.microsoft.com/en-us/library/bb283536.aspx#BKMK_IntegrationServices