SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


UK Dates in Tab delimited file not importing correctly.


UK Dates in Tab delimited file not importing correctly.

Author
Message
chubnut
chubnut
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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
mister.magoo
mister.magoo
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11043 Visits: 7891
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


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • chubnut
    chubnut
    Forum Newbie
    Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

    Group: General Forum Members
    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
    Attachments
    Doc1.docx (6 views, 105.00 KB)
    Richard Fryar
    Richard Fryar
    SSC Eights!
    SSC Eights! (975 reputation)SSC Eights! (975 reputation)SSC Eights! (975 reputation)SSC Eights! (975 reputation)SSC Eights! (975 reputation)SSC Eights! (975 reputation)SSC Eights! (975 reputation)SSC Eights! (975 reputation)

    Group: General Forum Members
    Points: 975 Visits: 1172
    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
    chubnut
    chubnut
    Forum Newbie
    Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

    Group: General Forum Members
    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.
    Attachments
    LocaleMe.jpg (11 views, 49.00 KB)
    LocaleThem.jpg (7 views, 49.00 KB)
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search