August 19, 2009 at 10:52 am
SSIS Gurus,
I am trying to import an XML file to SQL Server 2008 using XML Source component. Everything is good except for the fact that the datetime field values from XML are populated incorrectly. The datetime pattern in the XML file is "YYYY-MM-DD-HH:MM".
For example:-
gets populated as "1997-10-15 21:00" in SQL Server which is a is a difference of 7 hours.
So that means 16th Oct 1997 4 AM gets converted as 15th Oct 1997 9PM.
I tried using Conversion transformation etc but doesn't work. What is strange though is the time portion always gets populated as 9PM in the SQL Server table.
So i tried viewing the data using the data viewer immediately after the XML Source adapter and seems like the weird datetime conversion is occuring right when it fetches the data from the XML file. I am stuck with the problem since over a day now and my task deadline is approaching.
What could be the issue here? Any help will be greatly appreciated.
Please help me guys.
Thanks in advance,
Amol
Amol Naik
August 19, 2009 at 12:12 pm
The value "1997-10-16-04:00" does not mean "October 16, 1997 4:00 am".
The "-04:00" part of the date means offset the date (in UTC time) by 4 hours, which is why you see "October 15, 1997 9:00 pm"
Check out http://www.w3schools.com/Schema/schema_dtypes_date.asp for more information.
August 19, 2009 at 2:24 pm
Ah i see so thats the offset, thanks Eric for the quick reply, however i do not understand how it does it convert it to 9PM always. Could you please explain me how do i retain the dates while importing to SQL Server?
Thanks,
Amol
Amol Naik
August 19, 2009 at 3:16 pm
What time zone is the computer running this package set to? I'm guessing that it's set to GMT since the UTC offset is changing your dates to 9 pm the previous day. (My computer is set to Eastern Time, so a UTC offset of -04:00 gives me a different date/time.)
If you want to store the date without the UTC offset, there are a couple of things you could do.
Since the dates change to 9 pm the previous day, you could add a Derived Column task that either adds a new column or replaces your date column. The expression for this derived column would be: "DATEADD("HH",3,[Name of field])". The problem with this is that it wouldn't work if executed from a computer set to a different time zone.
You could alter the XSD to treat this field as a string instead of a date, then add a Derived Column task that converts only the 1st 10 characters of the string to a date. The expression for this derived column would be: "(DT_DATE)SUBSTRING([Name of field],1,10)". The problem with this is that you're modifying the xsd.
You could ask to have the xml contain the date only without the offset, but depending on where the file comes from, that could be like asking for a million dollars.
August 19, 2009 at 5:34 pm
Thanks Erik. Our computers are running at the GMT Time zone (GMT-08:00) Pacific Time (US & Canada). I still don't understand how does it default to previous day 9 PM. So does it always 3 hours from midnight always to arrive at 9M previous night? I still need to know that?
Changing the xml to have the datetime as string is not possible in the near future atleast, i'll go with your second option of using a Derived column with expression "DATEADD("HH",3,[Name of field])". But is it a safe way to assume that it will always default to 9 PM? That's why i was stressing how does it arrive at 9 PM.
Thanks again for your help.
Amol
Amol Naik
August 20, 2009 at 8:48 am
If the UTC offset in your date remains -04:00, the date will default to 9:00 pm the previous day in the Pacific time zone only while you're in Daylight Savings Time. On November 1st, when the clocks switch back to standard time, your date will default to 8:00 pm the previous day.
Here's how I can best describe the UTC offset:
During Standard Time (November - March)
Your date & offset of 1997-10-16-04:00 means that 12:00 am on 10/16/1997 is 4 time zones to the left of GMT (putting 12:00 in the Atlantic time zone.) This means that it's 4:00 am on 10/16/1997 in Greenwich England. You are located in the Pacific time zone, which is 8 hours behind Greenwich. This means that it's 8:00 pm on 10/15/1997 where you are.
During Daylight Savings Time (March - November)
You have to add 1 hour to the time if Daylight Savings Time is observed. In this case, your date & offset of 1997-10-16-04:00 means it's now 5:00 am on 10/16/1997 in Greenwich England because the clocks went forward 1 hour in March. You are located in the Pacific time zone, which is 8 hours behind Greenwich. This means that it's 9:00 pm on 10/15/1997 where you are, explaining why you see the date you do.
August 20, 2009 at 9:02 am
Thanks Erik, that was a very good explanation. Really appreciate your help so far. So depending on the Day light savings i'll need to add 3 or 4 hours to the xml imported date. Is there a way in SQL i can check for the Day light savings and then add 3 or 4 hours accordingly?
Best regards,
Amol
Amol Naik
August 20, 2009 at 10:29 am
This should work, just keep in mind that it will store dates in GMT. In your example, 1997-10-16-04:00 will be stored as 10/16/1997 5:00 am during Daylight Savings Time, and 10/16/1997 4:00 am during Standard Time.
This example assumes the name of the date field in the XML is HireDate.
1) Add a Script Component as a transformation after your XML Source.
2) In the Script Component's Inputs and Outputs tab, add an output column to Output 0 named HireDate_Modified and change the type to DT_DATE.
3) Add the following code to the Script:
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
' Store the number of hours difference between your time zone and UTC time
Dim lZoneDiff As Long = DateDiff(DateInterval.Hour, Date.Now, Date.UtcNow)
Dim tz As TimeZone = TimeZone.CurrentTimeZone
' Determine if the date occurred during Daylight Savings time.
' If yes, add 1 hour to lZoneDiff to compensate for Daylight Savings time.
If tz.IsDaylightSavingTime(Row.HireDate) Then
lZoneDiff += 1
End If
Row.HireDateModified = DateAdd(DateInterval.Hour, CDbl(lZoneDiff), Row.HireDate)
End Sub
If you don't want to store the time, you can modify the code to ignore it.
August 20, 2009 at 5:34 pm
Thanks a million Erik!
Amol Naik
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply