How to import a flat file without changing regional settings on the deployment server?

Question

I am trying to read a text file in SSIS (2005/2008). I created a connection to the file and placed flat file source, and it works fine in preview but when I try to run I get error saying:

[Derived Column [91848]] 
Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR

So I have identified that there are couple of columns with decimal numbers which use period as delimiters. But since I work in Scandinavian environment, the servers expect comma as delimiters.

What works is manually changing delimiter from comma to period in regional settings on the windows server but this is not an adequate solution.

How do I import the flat file using SSIS without having to change the regional settings on the server where the package is deployed?

Sandy

Answer 1

You should be able to set the LocaleID on the Flat File connection object. That will apply the English locale settings when reading that file without affecting the other things on your computer. (Look in the Properties window, not the Editor dialog.)

Right-click on the flat file connection under Connection Managers section and select Properties. You can also select the flat file connection manager and press F4 to view the properties.

Set the LocaleID according to your preference.

Syntactics

I did the opposite, filled a text file with commas for the decimal separator and set the LocaleID to Norway. Imported fields as decimal just fine. My guess is that you didn't refresh the column mappings after changing the LocaleID. – Syntactics

Worked for me here in SSIS 2012: text file with comma for the decimal separator, United States as regional format in control panel, United States as locale in the connection object->fails. Changing the locale in the connection object to Norwegian, Nynorsk (Norway) solves the problem, no need to refresh column mappings. – Cockaleekie

This very much helped me. Have to refresh the column mappings afterwards in the source object. – Maxentia