SSIS: Loading Flat file and issue with locale settings

Working with Flat file source in SSIS is common task and we all have experience with that. However, sometime it get tricky when it comes to localization. Some countries uses comma (,) and some countries uses dot (.) as decimal point for numerical field in the table.

If your data include decimal places  with dot (.) and you use the locale as any of the Nordic country such as Denmark/Norway/Sweden, then you are screwed. Since these countries use their decimal places as comma (,). I am going to explain a scenario where faced similar issue.

Got a file from business where a column in the file was numeric, and e.g. data look like below:

Budget	Amount
Food	229.1200
Cloth	8.4100
Travel	12.1500
Apartment Rent	271.2800

 Created the SSIS package:

Fig 1: SSIS package for loading data from Flat file

When I made the package did not look carefully the numerical field, so put the locale as Danish. If you open the connection manager you will find window like below:

Fig 2: Setting the locale

As you can see from the above Figure (Fig 2) I put Danish(Denmark) as locale, so after loading the data from the flat file to the staging table I only can see wrong data is inserted. After spending some time found the locale I have set it will make mess with dot (.) decimal place.

It means either I have to change the file replacing dot (.) with comma (,) or I can change the locale setting with English (United States). Offcourse, you will find more easy to change the local setting.

Hurrah!! Now you got correct result at your staging table.