SSIS how can i insert a blank date to database with visual basic

Question

Hi all, how can i insert a blank date to sql database in ssis working with a visual basic script component? I have to transfer date from excel to database and i would like to insert a blank or null value in the database if in the excel is empty

thanks

Answer 1

By blank do you mean default date, or keeping it NULL??

Instead of inserting a blank date into SQl Server, could you not just set up a default date in the SQL Table itself, so that it automatically does it if nothing is passed to that field?

Answer 2

In the script component, create a new output column - of type DATE. This will expose it in the object model in the script as a writable object - There will be a Row.COLUMNNAME for the value and Row.COLUMNNAME_IsNull. Presumably your input column is coming in as a string column.

Then in the script, set Row.COLUMNNAME_IsNull = True when appropriate conditions are met, e.g (this is production code, with a preCOMPDATE string column coming from the source, and a COMPDATE date column in the output):

If Row.preCOMPDATE_IsNull Then
    ' In this example we only set it to NULL is the string is null - you might also do this for blanks or whatever else the string might contain
    Row.COMPDATE_IsNull = True
Else
    ' This is a date cleansing routine defined elsewhere in the script - out of range dates are defaulted to 1/1/1900, among other things, logging errors and setting severities
    Row.COMPDATE = ValidateDate("COMPDATE", _
                                Row.preCOMPDATE, _
                                startDate, _
                                endDate, _
                                New Date(1900, 1, 1), _
                                ErrorDesc, _
                                IsRegDtError)
End If