UK Dates in Tab delimited file not importing correctly.

• chubnut

  SSC Journeyman

  Points: 89

  More actions

  January 25, 2013 at 9:07 am

  #273413

  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

  SSC-Forever

  Points: 47068

  More actions

  January 25, 2013 at 10:37 am

  #1581139

  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
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]