September 2, 2004 at 3:17 pm
I do not understand why the following script is returning all values of 1853/01/01 in my destination column. When I run a test in the code window and do a view result. I realize that it just copies them to a temp text file but all of the dates are good, with a few expected exceptions.
I've used MsgBox to confirm my date value after the IsDate(Date) returns true, yet it always envokes TransFailureMain(). I assumed it would throw InsertFailureMain() but I guess what I dont' understand is why DTSDestination("J1_ContractDate") = CDate(Date) fails when IsDate(date) returns true. I've also tried using CStr(Date) and just plain Date.
I forgot the code to create an OLEDB_DATETIME object but think that might work.
God bless the person who helps me. I've tried 5 times to post this message but it kept blanking it out becuase I'd take to long to type it, typically running of to try another possibility, but none have worked so far. I'm fed up with it =(
'********************************************************************** ' Visual Basic Transformation Script ' ' Date Transformation Script for 4 part date conversion
'************************************************************************
Function Main()
Dim Date
Dim Day
Dim Month
Dim Year
Dim Century
Day = CInt( DTSSource("J1_CONTRACTDAY") )
Month = CInt( DTSSource("J1_CONTRACTMONTH") )
Year = CInt( DTSSource("J1_CONTRACTYEAR") )
Century = 100 * CInt( DTSSource("J1_CONTRACTCENTURY") )
Date = CStr( Century + Year) + "/" + CStr(Month) + "/" + CStr(Day)
If IsDate(Date) Then
DTSDestination("J1_ContractDate") = CDate(Date)
Main = DTSTransformStat_OK
Else
DTSDestination("J1_ContractDate") = "1753/01/01"
Main = DTSTransformStat_OK
End If
End Function
Function TransFailureMain()
DTSDestination("J1_ContractDate") = "1853/01/01"
TransFailureMain = DTSTransformstat_OK
End Function
Function InsertSuccessMain() InsertSuccessMain = DTSTransformstat_OK End Function
Function InsertFailureMain()
DTSDestination("J1_ContractDate") = "1953/01/01"
InsertFailureMain = DTSTransformstat_OK
End Function
September 2, 2004 at 11:27 pm
I think you're running into problems due to using reserved words and functions as your variable names. I know you need to use Year, Month, Day and Date, but maybe you should change them to
intYear, intMonth, intDay, strDate.
September 3, 2004 at 1:32 am
1. Make sure you are using a datetime data type and not a smalldatetime, because "1853/01/01" and "1753/01/01" are outside the range smalldatetime supports.
2. Depending on your Locale settings for windows, etc, depends on the order of y/m/d or d/m/y or m/d/y that scripting and sql server will expect. To save the heartache use DateSerial as in:
DTSDestination("J1_ContractDate") = DateSerial(Year,Month,Day)
But as bobsterboy mentioned, you will want to change your variable names to non reserved names.
Julian Kuiters
juliankuiters.id.au
September 3, 2004 at 8:57 am
Hey guys thanks for the help but still it will not work. My destination is of type datetime and I've modified the script to the following below, but still only get the value 1853/01/01 in my table. The test still gives me valid dates such as 6/9/1999, but that's only to a temp text file. Seeing as how I'm not even concatinating a "/" into the date, the DateSerial is working fine. It seems to be a problem upon insertion however the TransFailureMain() date of 1853/01/01 is what I end up with in my source. I'm about ready to just pull the data over as is and do some t-sql casting via stored proc. This is just more work than I originally bargained for.
'********************************************************************** ' Visual Basic Transformation Script ' ' Date Transformation Script for 4 part date conversion '************************************************************************
Function Main()
Dim intDay
Dim intMonth
Dim intYear
intDay = Cint(DTSSource("J1_CONTRACTDAY") )
intMonth = Cint( DTSSource("J1_CONTRACTMONTH") )
intYear = Cint( DTSSource("J1_CONTRACTYEAR") ) + (100 * Cint( DTSSource("J1_CONTRACTCENTURY") ))
DTSDestination("J1_ContractDate") = DateSerial(intYear,intMonth,intDay)
Main = DTSTransformStat_OK
End Function
Function TransFailureMain()
DTSDestination("J1_ContractDate") = "1853/01/01"
TransFailureMain = DTSTransformstat_OK
End Function
Function InsertSuccessMain() InsertSuccessMain = DTSTransformstat_OK End Function
Function InsertFailureMain()
DTSDestination("J1_ContractDate") = "1953/01/01"
InsertFailureMain = DTSTransformstat_OK
End Function
September 3, 2004 at 12:36 pm
Maybe it's not this field, but another field that's causing you all this trouble. Have you tested all of the other transformations to see if they're working correctly?
September 3, 2004 at 2:51 pm
Well I deleted all the other transformations which are not simply copy columns and guess what, it's working now. I didn't think a seperate transformation would affect it but it's working right now. Good note to end the week and begin the weekend on..
Thanks Bobster and have a good labor day weekend.
September 3, 2004 at 2:58 pm
Cool,
I learned something too. I've done literally hundreds of DTS packages and have never messed with the error handling. I'm assuming now that the trans error is global for that transformation and the ins error is only for inserting that field.
Too much stuff to learn, too little time. Have a good holiday, glad I was able to help.
September 3, 2004 at 3:17 pm
Yeah it's kind of hidden. You have to right click Data Transformation Services and go to properties from the enterprise manager window to enable Multi-Phase Data pump to even see the option to do so.
However, I wouldn't go making assumptions about the different phases based on this example, which seems pretty obscure. I still don't feel comfortable in the way it was fixed but I'll live with the fact that it is. I don't think this was behaving properly.
Here is the final code.. i don't understand what is so different about it. Also I need to try an out of range date because after running the script this way it never used the InsertFailure or Transfailure dates.
'**********************************************************************
' Visual Basic Transformation Script
'
' Date Transformation Script for 4 part date conversion
' Usage:
' Replace all <<< source/destination >>> labels with appropriate source/destination
'
' **Notes
' Multiphase data pump must be enabled and the post row transform options must be enabled
' Version 1.0
' Author: Joseph Hirn
'************************************************************************
' Copy each source column to the destination column
Function
Main()
Dim myDate
Dim intDay
Dim intMonth
Dim intYear
intDay = Cint(DTSSource("J1_CONTRACTDAY") )
intMonth = Cint( DTSSource("J1_CONTRACTMONTH") )
intYear = Cint( DTSSource("J1_CONTRACTYEAR") ) + (100 * Cint( DTSSource("J1_CONTRACTCENTURY")))
myDate = CStr(intMonth) + "/" + CStr(intDay) + "/" + CStr( intYear)
If IsDate(myDate) Then
DTSDestination("J1_ContractDate") = DateSerial(intYear, intMonth, intDay)
Else
DTSDestination("J1_ContractDate") = "1753/01/01"
End If
Main = DTSTransformStat_OK
End Function
Function TransFailureMain()
DTSDestination("J1_ContractDate") = "1853/01/01"
TransFailureMain = DTSTransformstat_OK
End Function
Function InsertSuccessMain()
InsertSuccessMain = DTSTransformstat_OK
End Function
Function InsertFailureMain()
DTSDestination("J1_ContractDate") = "1953/01/01"
InsertFailureMain = DTSTransformstat_OK
End Function
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply