Locale issues when importing flat file data (UK/US date problem)

• chubnut

  SSC Journeyman

  Points: 89

  More actions

  June 3, 2013 at 7:32 am

  #394900

  I’ve raised this issue before but I’ve now been allowed to spend time on site to review the problem further. Unfortunately, the servers and the desktops are on different domains from mine (and each other I believe), quite locked down and administered by another organisation.

  We have a server set up with SQL Server 200R2 Installed on it.

  The location and all the regional settings on the server are all set to United Kingdom.

  I’m connecting to the server via a Win 7 desktops with SSMS installed (SQL 200R2) The location and the regional settings on the desktops are also set to United Kingdom.

  If importing flat file pipe delimited data via SSIS into a database on the server from the desktop it thinks the locale is United States and screws the dates up. Whilst I can create a package and view the properties, change and save it, I cannot run the package due to permissions, nor will the relevant permissions be given. Plus the nature of the data being provided means consistency is very irregular.

  If I run a file in on the server itself it correctly loads it up as United Kingdom and the dates are correct. It also correctly imports the data if I log in via another server.

  I’ve created a new instance on the desktops, ensured that everything is set for UK and tried to load the data there. This also gives me the US date problem.

  In all the tests we have carried out it does seem that the desktops are at fault. I’ve checked with the sys admins and they say nothing is being blocked (or shown in the logs as being blocked). Unfortunately, they are not open to the problem or there being a problem for them to fix.

  Whilst I can log into and load the data via the server direct, this is not really acceptable and because of the nature of the project, may not be allowed due to risks involved.

  Has anyone come across this issue before and if so what was the solution? Is this a problem with ports and if so which ones? Or is this a problem with GP?

  Many thanks