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?

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.