asked on

Trouble with Null Dates in Script Component

My SSIS package connects to an AS 400 where the month, day, year is stored as a decimal. I have a script component that combines the three fields into one and converts to date which is then copied over to my sql into a Pricing table and a destination field know as BeginDate.
It works, until it encounters a null value for the date fields and I receive this message
Conversion from string "0/0/0" to type 'Date' is not valid. And I understand that since the values in the fields are blank.
I thought at first to check the source Month field and grab only values > 0 but I'm told there are circumstances where the begin dates are blank for a reason. So I have to grab all the data regardless if the BeginDate has a valid date (this not user input) I'm just copying from db2 to sql.
Here is my script.

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim Pdate As Date
Pdate = CDate((Row.PRBEGINMO1 & "/" & Row.PRBEGINDA1 & "/" & Row.PRBEGINYR1))
If IsDate(Pdate) Then
Row.BeginDate = CDate(Pdate)
End If
End Sub

everything I try comes back wrong or all the dates as strange like 1889/01/19.
Thanks for the help

jogos

Always give a system the data in the way it wants it.

So if you query a NULL-value in code you mostly substitute it by something, date '0/0/0', int -20000000 or whatever) to have a value that programm can work with.
But by returning to the database (or other system) you must convert it back to something that system understands, so back to NULL ....

DFCRJ

ASKER

I cant get this to work. I changed the Dim Pdate to a string and the dates come back as 1899-12-30 on any values that should be blank.
If I add a variable and assign it "00/00/00" it returns the same error.
I think I've looked at this to long

jogos

<If I add a variable and assign it "00/00/00" it returns the same error.>
You assign it a value and that isn't a valid date, so you get something like the lowest possible value ...

Saying to database that there is no date is assinging it the value Null (NULL is not actualy a value but is the way to say in database, the content is not relavant)

DFCRJ

ASKER

i know, but I tried so many things I can remember what I have and havent done.
this one things has held my whole day up

jogos

tried = Null?

as in

If IsDate(Pdate) Then
Row.BeginDate = CDate(Pdate)
else
Row.BeginDate = null
End If

ex
http://www.developermania.com/newsgroups/category/6/86/microsoft_public_sqlserver_dts.aspx

DFCRJ

ASKER

yea, error says 'value of System.DBNull cannot be converted to Date'
the NULL returned an error saying NULL no longer supported.

ASKER CERTIFIED SOLUTION

jogos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DFCRJ

ASKER

thanks. need to import the namespace.
thanks for the help!