October 29, 2007 at 11:53 am
My SSIS package connects to an AS 400 where the month, day, & year are stored separately as a decimals. I have a script component that combines the three fields into one and converts to a date which is then mapped to my sql into a Pricing table with a destination field know as BeginDate (SMALLDATE).
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 it 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 in our business where the begin dates are blank for a reason. So I need to grab all the data regardless if the BeginDate has a valid date (this not user input) or is NULL. I'm just copying using db2 select statements 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 the dates are strange like 1889/01/19.
Thanks for the help!
October 29, 2007 at 11:58 am
Try this way
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim strDate as string
strDate = (Row.PRBEGINMO1 & "/" & Row.PRBEGINDA1 & "/" & Row.PRBEGINYR1)
If IsDate(strDate) Then
Row.BeginDate = CDate(strDate)
End If
End Sub
October 29, 2007 at 12:28 pm
That did work!
but, the NULL values on the prices are now listed as 1899-12-31
which is better than what I had working
October 29, 2007 at 1:53 pm
How are you displaying the data to see that value?
October 30, 2007 at 6:45 am
thru the query editor. Select *
I changed the destination field from SMALLDATE to varchar just to see and 0000-00-00 appears now.
Really in this table I just have to show that there is no ending date on a customers price list.
I have a 'Start Date' and 'End Date' the start date always has something but the End Date will very and no one will be updating the data on the SQL. Just for reporting.
October 30, 2007 at 8:05 am
Thinking about this it sounds like the date may be -1 (12/31/1899) is being inserted and not null which the date is valid so it passes the rudimentary check. Might consider a change in logic like so to see if you can get around it. If this doesn't catch then is your column nullable?
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim strDate as string
strDate = (Row.PRBEGINMO1 & "/" & Row.PRBEGINDA1 & "/" & Row.PRBEGINYR1)
If IsDate(strDate) Then
If CDate(strDate) > CDate("12/31/1899") THEN Row.BeginDate = CDate(strDate)
End If
End Sub
October 30, 2007 at 9:06 am
That is good thinking. After playing with it I stumbled upon this and now the DB has NULL in the End Date as it should.
(1) Changed the destination field from DATETIME to SMALLDATETIME, verified set to allow NULL
(2) Changed the script-com on the data type from Date to Database_TimeStamp
(3) Used this code:
Dim strDate, endDate As String
strDate = (Row.PRBEGINMO1 & "/" & Row.PRBEGINDA1 & "/" & Row.PRBEGINYR1)
endDate = (Row.PRENDMO1 & "/" & Row.PRENDDA1 & "/" & Row.PRENDYR1)
If IsDate(strDate) Then
Row.begindate = CDate(strDate)
Else
Row.begindate_IsNull = True
End If
If IsDate(endDate) Then
Row.EndDate = CDate(endDate)
Else
Row.EndDate_IsNull = True
End If
but if I you hadnt mentioned changing the Date to a String I would still be stumped. Thanks for all the help!
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply