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

UK Dates in Tab delimited file not importing correctly. Expand / Collapse
Author
Message
Posted Friday, January 25, 2013 9:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 1:52 PM
Points: 7, Visits: 123
I'm sorry if this has been raised before. I have searched but have not found what I'm looking for.

I have a range of some 40 tab delimited txt files to load into a SQL Server 2008R2 database. These range in size from 2k to 14GB. Dates in these files are in the DDMMYY, DDMMYYYY, DD/MM/YY, DD/MM/YYYY (or YYYYMMDD being imported as varchar) format. Some columns can have a mixture of 2 and 4 digit years.

The box on which SQL Server has been installed is for some unknown reason set to US (I'm in the UK, the box is in the UK, the data is all UK data and we are a UK organisation). I cannot change this on this pre production environment. I believe that the live environment is correct. SQL Server, the users etc, I have changed to the correct UK settings.

All the receiving tables have been created with the correct data types set. Dates have been set to datetime.

When using SSIS to import the data (I can only use SSIS for operational reasons), the dates are incorrectly imported. Originally I thought converting to a US date format but further investigation shows many inconsistencies.

If I bring the date data in as say varchar and then validate using the isdate function SQL Server returns 1 as a valid date. If I alter the table and add a date (not datetime) column and update the new column the dates are converted correctly. If it's a datetime column the data is incorrect although not as inconsistent as via SSIS.

So (finally) my questions are as follows:-

Why can't SSIS correctly convert the dates to the UK date format? Why does it think it needs to convert it to US or derrivative thereof?

How can I get SSIS to import the data correctly or am I going to have to use the subsequent update method described above.

Sorry for the length of the post. This has been giving much grief for some time now.

Many thanks

John

Post #1411785
Posted Friday, January 25, 2013 10:37 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:49 PM
Points: 1,787, Visits: 5,722
Perhaps if you supplied some tables definitions and sample data that exhibits the problem, it would be easier to help.

Also, it would be helpful to document how you configured your import - column definitions for the text files, any transforms etc...


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1411839
    Posted Monday, January 28, 2013 4:00 AM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Tuesday, May 27, 2014 1:52 PM
    Points: 7, Visits: 123
    Sorry for the delay - have been away this weekend. I've generated some sample data and created an example table as below. The data is a decent representation of the quality of the data in the files I have. Please remember that this is tab delimited txt files.

    MEMID SAMPLE1 SAMPLE2 SAMPLE3 SAMPLE4
    1 01/09/07 20070901 01092007
    2 01/09/2007 02092007
    3 17/10/1999
    4 17/10/1999 20300612
    5 12/01/2020
    6 15/08/2030 12/01/20 19970508
    7 15/08/1997 15/08/30 03092007
    8 28/03/2002 15/08/97 04092007
    9 05092007
    10 28/03/2002 06092007



    CREATE TABLE [dbo].[ExampleDates](
    [MEMID] [int] NOT NULL,
    [SAMPLE1] [datetime] NULL,
    [SAMPLE2] [datetime] NULL,
    [SAMPLE3] [varchar](10) NULL,
    [SAMPLE4] [varchar] (10)NULL
    ) ON [PRIMARY]

    In terms of screen shots please see attached word doc.

    The odd thing is that I can't produce this data on the system I have to work on as it is locked down with no internet access. So I produced this on my own machine and the data goes in correctly. So this leads me to think that it isn't so much the data as the set up of the Server itself.

    Would be grateful for any suggestions.

    Many thanks





      Post Attachments 
    Doc1.docx (0 views, 105.04 KB)
    Post #1412319
    Posted Monday, January 28, 2013 5:30 AM
    SSC Veteran

    SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

    Group: General Forum Members
    Last Login: Yesterday @ 8:17 AM
    Points: 283, Visits: 1,116
    What is the default language of the login you are using for the import?

    If English, try changing it to British English.

    Without knowing exactly how SSIS is handling the data I can't say if that is the cause, but worth a try.




    Check Your SQL Servers Quickly and Easily
    www.sqlcopilot.com
    Post #1412365
    Posted Monday, January 28, 2013 7:05 AM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Tuesday, May 27, 2014 1:52 PM
    Points: 7, Visits: 123
    All of the logins are set to British English and the server instance has been configured to British English too. Interestingly, when using the SSIS wizard on my machines the Locale defaults to English (United Kingdom) - see pics. On the their machine it defualts to English (United States) and I change the locale manually to be UK

    Originally when they set the instance up they defaulted it all to US and I had to change it to British English

    As an adendum to this, We have also checked the default user in the registry and this is set correctly too.


      Post Attachments 
    LocaleMe.jpg (5 views, 49.09 KB)
    LocaleThem.jpg (2 views, 49.01 KB)
    Post #1412413
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse