September 27, 2010 at 9:22 am
Hi all,
we have a package in ssis in production scheduled to run every night,and i recently found that it is handling few dates in wrong way I will tell with an example:
There is a file daily that comes from oracle with date column ex:30-DEC-99 and 12-nov-11 and our package is converting the dates into datetime using this code in script task:
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If Trim(Row.requestdate) = "" Then
Row.requestDateFIX = Nothing
Else
Row.requestDateFIX = FixDate(Row.requestdate)
End If
If Trim(Row.shipdate) = "" Then
Row.shipDateFIX = Nothing
Else
Row.shipDateFIX = FixDate(Row.shipdate)
End If
If Trim(Row.promisedate) = "" Then
Row.promiseDateFIX = Nothing
Else
Row.promiseDateFIX = FixDate(Row.promisedate)
End If
If Trim(Row.entereddate) = "" Then
Row.enteredDateFIX = Nothing
Else
Row.enteredDateFIX = FixDate(Row.entereddate)
End If
If Trim(Row.lineentereddate) = "" Then
Row.lineEnteredDateFIX = Nothing
Else
Row.lineEnteredDateFIX = FixDate(Row.lineentereddate)
End If
If Trim(Row.newcrd) = "" Then
Row.newCrdFIX = Nothing
Else
Row.newCrdFIX = FixDate(Row.newcrd)
End If
End Sub
Function FixDate(ByVal sBadDate As String) As Date
Dim sYear, sMonth, sDay As String
sYear = Right(sBadDate, 2)
sMonth = Mid(sBadDate, 4, 3)
sDay = Left(sBadDate, 2)
If sYear > "80" Then
sYear = "19" & sYear
Else
sYear = "20" & sYear
End If
Select Case sMonth
Case "JAN"
sMonth = "01"
Case "FEB"
sMonth = "02"
Case "MAR"
sMonth = "03"
Case "APR"
sMonth = "04"
Case "MAY"
sMonth = "05"
Case "JUN"
sMonth = "06"
Case "JUL"
sMonth = "07"
Case "AUG"
sMonth = "08"
Case "SEP"
sMonth = "09"
Case "OCT"
sMonth = "10"
Case "NOV"
sMonth = "11"
Case "DEC"
sMonth = "12"
End Select
Return CDate(sYear & "-" & sMonth & "-" & sDay)
End Function
End Class
The date columns in destination table looks like this:
Column DataType
RequestDate Datetime null,
ShipDate Datetime null,
PromiseDate Datetime null,
EnteredDate Datetime null,
LineEnteredDate Datetime null,
Newcrd Datetime null
The SSIS package is converting the dates like ex: 12-nov-11 to 2011-11-12 which is correct according to oracle database..
but it is converting the dates like ex:30-DEC-99 to 1999-12-30 which is wrong according to Oracle .the date should be 2099-12-30 .
Now this table has the Past due data and future data for the sales and if the date shows something less than today i.e 2010-09-27 is a past due according to this data and there is no way we have a past due for the year 1999 or less than 2008 year .
How should i deal with kind of dates and how should i tell it to take the date ex:30-DEC-99 as 2099-12-30..
if i want to change the package is it possible?because the package is configured and scheduled already and we are using vault source to store the packages.If yes, do i need to configure again after i change the package ..
Appreciate any help
September 27, 2010 at 9:58 am
Hi all,
I kind of changed the script to for all the dates to fall in 2000 year not in 1900.
i have changed the function part (the one in bold)
Function FixDate(ByVal sBadDate As String) As Date
Dim sYear, sMonth, sDay As String
sYear = Right(sBadDate, 2)
sMonth = Mid(sBadDate, 4, 3)
sDay = Left(sBadDate, 2)
If sYear > "01" Then
sYear = "20" & sYear ---this is where i have changed ,this will suggest all the dates to fall in 2000 i think
End If
Select Case sMonth
Case "JAN"
sMonth = "01"
Case "FEB"
sMonth = "02"
Case "MAR"
sMonth = "03"
Case "APR"
sMonth = "04"
Case "MAY"
sMonth = "05"
Case "JUN"
sMonth = "06"
Case "JUL"
sMonth = "07"
Case "AUG"
sMonth = "08"
Case "SEP"
sMonth = "09"
Case "OCT"
sMonth = "10"
Case "NOV"
sMonth = "11"
Case "DEC"
sMonth = "12"
End Select
Return CDate(sYear & "-" & sMonth & "-" & sDay)
End Function
it worked ( i have only checked for couple of ID's)..but Please correct me if my approach is wrong..
Now how i need to change the package (developed by other guy) is it possible to do that after the package is configured and scheduled ..
will vault source help me..
do i need to configure after i change the package ..
September 27, 2010 at 4:32 pm
srilu_bannu (9/27/2010)
The SSIS package is converting the dates like ex: 12-nov-11 to 2011-11-12 which is correct according to oracle database..but it is converting the dates like ex:30-DEC-99 to 1999-12-30 which is wrong according to Oracle .the date should be 2099-12-30 .
This is a simple Y2K windowing issue. You need to make sure that the windowing in your function is the same as what Oracle is configrued to use. Of course it is possible that there is no way to correct the problem short of getting the full 4 digit year from Oracle. (Would your data ever have 1999 dates in it? If so, how will you be able to identify them from the 2099 dates?)
September 28, 2010 at 8:50 am
No, we don't have any due quantity from the year 1999 or 19th century,that is the reason i want to prefix everything with "20"
September 28, 2010 at 8:55 am
19th Century?:w00t:
Your very first post used 30-DEC-99 as an example, so it's not surprising that some confusion was caused.
September 28, 2010 at 1:30 pm
Yes i agree with you ..
Anyway i have changed the code by going into BIDS and edit the package but while saving the package into MSDB Database it is giving me error saying "Access denied for the server" ..
could anyone tell me how some one who has the access should add me (i mean the procedure)...
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply