Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to load Date columns in consistent manner Expand / Collapse
Author
Message
Posted Wednesday, January 16, 2013 10:15 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 3:57 AM
Points: 93, Visits: 237
Hi,

I am loading the table from Excel source using SSIS 2005.

Excel contains one of the columns as Exp_Date and its examples values are

10/09/2010 17:00:51
11/09/2010 11:45:16
11/09/2010 11:45:16
13/09/2010 10:07:59
13/09/2010 10:07:59
13/09/2010 10:07:59
14/09/2010 10:44:59
15/09/2010 10:44:59
16/09/2010 14:19:25


In table, for some columns it is loading fine. But for some columns it is loading in an inconsistent manner, i.e. a date that should be loading as ‘10/09/2010’ is actually loading as ‘09/10/2010’.

Please advise how to load the date column values into table as it is in Excel file.

Regards
SqlStud
Post #1408142
Posted Thursday, January 17, 2013 8:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:06 PM
Points: 7,125, Visits: 12,720
sqlstud (1/16/2013)
Hi,

I am loading the table from Excel source using SSIS 2005.

Excel contains one of the columns as Exp_Date and its examples values are

10/09/2010 17:00:51
11/09/2010 11:45:16
11/09/2010 11:45:16
13/09/2010 10:07:59
13/09/2010 10:07:59
13/09/2010 10:07:59
14/09/2010 10:44:59
15/09/2010 10:44:59
16/09/2010 14:19:25


In table, for some columns it is loading fine. But for some columns it is loading in an inconsistent manner, i.e. a date that should be loading as ‘10/09/2010’ is actually loading as ‘09/10/2010’.

Please advise how to load the date column values into table as it is in Excel file.

Regards
SqlStud

Which driver are you using? The built-in driver? Please post the connection string so we can see the properties? Post the Excel file as an attachment to this thread as well.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1408469
Posted Wednesday, March 20, 2013 11:27 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 17, 2014 12:20 PM
Points: 373, Visits: 909
You can import the data as a string and in the dataflow manipulate the string in a derived column transformation to put it into yyyymmdd format, and append that to the time portion.

This is the universal standard for date string and will import nicely into your datetime column, of your destination.
Post #1433363
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse