Maybe somebody can provide assistance for the following question:
I have a SSIS package with Target SQL Server Version 2014. I am not 100% sure which version my target server is running, but it is at least 2014, but i assume its 2016. I have developed the package with SQL Server Data Tools 2015. It contains of:
- Flat File source + Connection Manager
- OLEDB Destination
- Conversion Step
The source file is encoded in UTF-8, the target database uses ANSI 1252. The file is located on a network drive (essentially the same windows server the SQL Server with the target database is running on).
Contained in the file is a decimal field with precision, scale 18,2. The actual data in the field is always 0.00. I have specified the input field in the connection manager as decimal.
Now my question: when i execute the package directly in Visual Studio (Data Tools), it works flawlessly, all rows are imported into the target table. When I call the dtsx package from an SQL Server Agent Job (it is the only step) it fails with a conversion error regarding the decimal field:
Source: Data Flow Task Flat File Source [88] Description: Data conversion failed. The data conversion for column "xxx" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
The error also arises when i switch between target server versions 2014 / 2016.
A list of things i tried without being successful:
- remove column headers from the input file
- test it with only one data row... no success.
- created a new solution with a new dtsx package and a slightly different input path
- deleted the SQL Server agent job and created a new one
- changed the encoding of the input file to UTF-8
I didnt find a solution for this exact error phenomenon on the net. Can anyone provide help?
Many thanks in advance!
Got it. The reason lies in the different decimal formats being awaited when calling the package from the SQL Server Agent Job versus the execution directly in Visual Studio / SQL Server Data Tools.
The Locale Id of the SSIS / dtsx package was by default set to "German" (which is the default language of my OS) whereas the language in effect on the target SQL Server is "English (US)". The input file contains the decimal values in the US format (dotted).
As soon as i changed the locale ID of the package to "English (United States)" it worked. (it would be also possible to change the decimal separator from a dot to a comma in the input file and leave the SSIS locale ID on German).
Obviously the execution via Visual Studio ignores the language setting on the server (otherwise it would crash in that mode too...)
this seems to be a rather similar question (it lead me to the correct solution): https://dba.stackexchange.com/questions/88895/difference-in-number-format-comma-and-dot-on-ssms-and-ssis