SSMS and SSIS – Difference in Number Format (Comma vs Dot)

sql serversql-server-2012ssis

I have a numeric(5,2) field on my SQL Server database. When I run the query, the number on that field appears with . (dot) separator. However, when I preview the query on SSIS, it appears with comma(,) separator. I am trying to show the number format with dot(.) on SSIS as well. How can I do that?
Thank you.

Best Answer

The issue here is that the Locale settings of the file connection in SSIS are different to the language settings in SQL Server.

In SSIS, go to the Properties window for your File Connection (Right-click->Properties). There should be a setting for LocaleID that will need to be set to match SQL Server (or to English).

Further information can be found in the following Stack Overflow post

Related Solutions

Sql-server – SQL column goes from first to last position in output

In the Columns page on the OLE DB Source, you can use the check boxes beside the columns to change the order.

The columns are added to the output in the order in which they get checked, so what you need to do is uncheck all the columns, and then check them again in the correct order.

Downstream data flow tasks will want to update themselves because the column mappings "broke", but this shouldn't be a big issue to fix.

Sql-server – SSIS convert DT_WSTR to date

You have to use convert DT_WSTR to DT_DBTIMESTAMP. You can find BOL reference here.

for e.g.instead of "Getdate()" use your Date_Column

(DB_TIMESTAMP)((DT_WSTR,4)YEAR(GETDATE()) + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()),2) + RIGHT("0" + (DT_WSTR,2)DAY(GETDATE()),2) + (DT_WSTR,10) Time_Column)

Also, you can use the Data Conversion transformation, which is probably easier if you are only changing the data type.

EDIT: You can also look for an option of SSIS GoogleAnalyticsSource from codeplex.

Best Answer

Related Solutions

Sql-server – SQL column goes from first to last position in output

Sql-server – SSIS convert DT_WSTR to date

Related Question