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.

               

Exporting data as flat file by SSIS: Step by step guideline

Exporting data from database and convert that into comma seperated file(.csv) is pretty straight forward and easy task by using SSIS,

As an example, I need to export ResellerInfo from AdventureWorksDW2012 and generate .csv file by using SSIS.

In SSIS Toolbox you have transformations called a) Data flow Task b) OLE DB source and c) Flat File Destination , to complete the excercise we need those two transformations and one Task.

Fig 1: SSIS Toolbox (Data flow Task)

Fig 2: SSIS Toolbox

In OLE DB Source you should write your SQL query to populate the result, in this case, I have below SQL:   SELECT

       [ResellerName]

      ,[NumberEmployees]

      ,[OrderFrequency]

      ,[OrderMonth]

      ,[FirstOrderYear]

      ,[LastOrderYear]

      ,[ProductLine]

      ,[AddressLine1]

      ,[BankName]

      ,[MinPaymentType]

      ,[YearOpened]

  FROM [AdventureWorksDW2012].[dbo].[DimReseller]

 

At your package you need drag a data flow task and then double click the data flow task, now under the data flow drag and drop the two transformations OLE DB Data source and Flat File Destination.

Fig 3: Under Data Flow task

In the above design, 'Populate Data' is a dataflow task and 'destination CSV' is Flat file destination transformation.

While we connected from 'Populate data' to the 'Destination CSV', we had to configure the 'Destination CSV' which is Destination Flat file Transformation. Destination Flat File configuration must connect with Flat file connection manager as like below:

Fig 4: Flat file destination is conneted with Flat file connection manager

Flat File connection manager should include, connection manager name, file destination and name at least. You can have options to choose from different delimeter like comma, semicolon etc.

Fig 5: Configure flat file connection

Now, from your package you can run the data flow task "Generate CSV for Reseller" by right click.

Fig 6: Execute Data flow task

and then you should have the .csv under the location that you have already put in the flat file connection manager.

Fig 7: CSV result set at the specified location