November 7, 2006 at 11:45 pm
Dear All
I have an SSIS package which is loading a table from a text file. I am in a dd/mm/yy enviroment and the text file contains a field in dd/mm/yy format. Using VS2005 to execute the package everything works fine loading the database in yy/mm/dd format, however when I run the package from the SQL agent some of the dates are converted incorrectly for example
7/11/06 results in 2006/07/11 instead of 2006/11/07.
The control panal of the server is set to d/m/y and as I said when running the package manually all is well.
Carl
Also posted in SQL Agent Forum
November 9, 2006 at 3:54 am
I don't know the answer for this but I had a thought.
Are the Regional settings under the control panel based on the User profile?
If so try logging in as the user account the SQL Agent and SSIS services are running under and checking the Regional settings.
Just a idea of something to look into.
regards
Daniel
November 9, 2006 at 4:54 am
Daniel,
Thanks for you reply.
This problem was really getting to me so I had made sure all the regional settings for all the users for every computer with 100m where set to dd/mm/yy but it did not help.
The problem was actually worse than I first reported, in that dates like 30/01/06 remained as 30/01/06 whilst 3/11/06 would be converted to 11/3/06 - all in the same text file load. I confirmed this behaviour with three load tests and each time it was reproduced. Clearly this is a bug because it should never do that.
However, I have now found a good work around. I created a "credential" entering the administrator logon and password. I then created a Proxy using the credential and finally used the Proxy in the step of the SQL Agent job. This fixed the issue.
Even better, in the past I have seen some FTP problems using the agent. These appear to go away using the Proxy.
Regards
Carl
November 9, 2006 at 6:12 am
Hi Carl,
This is not a bug.
Microsoft is making a stand on the whole date thing...
SSIS uses ISO standard date format: yyyy/mm/dd
The only way I got around this was by importing my dates as text with the commitment that the format will always be the same: dd/mm/yyyy and then run a script transformation to parse the dates:
'Assuming dd/mm/yyyy
Private Function getDate(ByVal aDateString As String) As DateTime
Dim myDay As Integer
Dim myMonth As Integer
Dim myYear As Integer
Dim myFirstSlash As Integer = aDateString.IndexOf("/") 'default 2
Dim mySecondSlash As Integer = aDateString.LastIndexOf("/") 'default 5
myDay =
CInt(Left(aDateString, myFirstSlash))
myMonth =
CInt(Right(Left(aDateString, mySecondSlash), myFirstSlash))
myYear =
CInt(Right(aDateString, Len(aDateString) - mySecondSlash - 1))
Return New Date(myYear, myMonth, myDay)
End Function
Public
Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Row.cvtDateOfBirth = getDate(Row.dtDateOfBirth)
End Sub
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply