I'm trying to create a very basic package that copies data from my SQL server into an Excel file.
The data that comes from the server comes with a decimal comma (e.g. 1234,56) - that is how I want it, that is how it's always worked, that is how I want the final data to be formatted in Excel. For some reason Visual Studio / SSIS is always transforming this data to a decimal point format (e.g. 1234.56) - that's not how I want it, I can't work with that.
I've tried every form of data conversion, doesn't matter if it's DT_NUMERIC, DT_DECIMAL, DT_CY, it's always giving me a decimal dot - even when going the Derived Column route, specifically turning the values into DT_WSTR, then replacing every "." with a "," and then converting to any numerical format still gives me a result with decimal points.
The problem is that Visual studio is previewing the SQL input in the correct formats, the numbers do have a decimal comma, the input is 100% correct - but at some point the comma is being transformed into a period without my doing.
The Excel Connection Manager's LocalID setting is set to German, so it should produce correctly formatted numbers. I tried pre-formatting the Excel-Destination file's columns to my desired format - not working. I tried putting in correctly formatted dummy data into the Excel-File and set it to Overwrite - not working.
Kinda losing my mind here, anyone got any clue on what's causing this behavior?