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(ByV
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
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(ByV
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
Last Comment
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
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
<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)
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)
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
this one things has held my whole day up
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
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
ASKER
yea, error says 'value of System.DBNull cannot be converted to Date'
the NULL returned an error saying NULL no longer supported.
the NULL returned an error saying NULL no longer supported.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
thanks. need to import the namespace.
thanks for the help!
thanks for the help!
Microsoft SQL Server 2005
Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.
72K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
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 ....