SQL Server Agent triggered SSIS package occasionally using a comma decimal separator

Question

SQL Server Agent triggered SSIS package occasionally using a comma decimal separator

Joe 1

I have an SSIS 2014 package that has been running for about 5 years that migrates data from a vendor database on a 2016 SQL Server (running 2012 compatibility) to a corporate database on a 2014 SQL Server. The package is triggered by an SQL Server Agent job that uses a proxy.

The source query calls a vendor function that converts Float values to VarChar in an application defined format (decimal or scientific notation) and occasionally (about 1.4%) it uses a comma decimal separator instead of a period.
I know the issue relates to the STR() function because the query includes Floats converted using using CAST which always used a period decimal separator.

I have checked that the default language for the proxy's login is set to english_us and that the SSIS LocaleID is set to English (United States).
The issue is not related to the database servers because we use an active-passive method for business continuity and have regularly have to switchover to ensure readiness and all of the involved servers have switched more than once.

I am trying to figure out where the localization setting for STR() and CAST is at in this case but am not sure if it's at the connection from SSIS to Source database or is it set on the source database.

ZoeHui-MSFT 41,551 Reputation points

2022-08-18T07:55:41.327+00:00

Hi anonymous usere，

Do you mean that if you use str() or cast function in SQL Server database, then it uses a comma decimal separator instead of a period?

Could you please share the code and sample data with us so that we could do more analysis.
Joe 1 Reputation point

2022-08-24T13:59:29.677+00:00

@ZoeHui-MSFT

Only the STR() function is using a comma for the decimal separator and it only occurs occasionally.

Since 2017 the job that is experiencing this issue has been executed 92,283 times and 1,144 of them use commas for the fields that used STR() to convert Floats to string while the fields that used CAST always use periods. It is worth noting that while the comma usage is intermittent at the job level it is consistent at the query level. Meaning that when a comma was used instead of a period it was used in all the fields that used STR(), never some of them.

Continued below
Joe 1 Reputation point

2022-08-24T14:02:58.747+00:00
Part 2

The user-defined scalar function that formats numbers as strings accepts two arguments, the FLOAT(53) value and the UNIQUEIDENTIFIER of the requested format from the Formats table, and returns a NVARCHAR(64). This function allows for numbers to be formatted as a decimal or as an exponent, but both use STR() to perform the conversion.

After validating the inputs, the function queries the Formats table for the format based on the UID.

If the format is precision it queries the Formats table for how many digits after decimal place and runs this query where @number is the input FLOAT, @nScale is an INT and @cNumber is the returned NVARCHAR(64):

SELECT @cNumber = LTRIM(STR(@number, 64, @nScale));

If the format is exponent it queries the Formats table for how many digits after the decimal place then it determines the value of the exponent using a while loop and then it runs this query where @mantissa is the input FLOAT that was set in a NUMERIC(38,18) variable, @nScale is an INT, @cExponent is a VARCHAR(3) that contains the zero-padded exponent, and @cNumber is the returned NVARCHAR(64) value:

SELECT @cNumber = LTRIM(STR(@mantissa, 64, @nScale)) + @cExponent;

Here is a screenshot of some of the data which shows that the issue affects only fields that used STR() and not CAST():
Joe 1 Reputation point

2022-09-04T11:54:53.52+00:00

@ZoeHui-MSFT Please review my response