May 11, 2009 at 7:44 pm
I have this SSIS package which executes an SP and output the results to a flat file. My problem is that 0.00 is outputted to the flat file as 0 (all numbers > 0 are exported correctly). However, when I ran the SP in SQL Analyzer, it's displayed as 0.00. When I clicked Preview in the OLE DB Source Editor, I can see that it's displayed as 0.00. But why is it that decimal number .00 is being dropped in the export.
I've tried to change the datatype of the field from Currency to String in the Flat File Connection Manager but it didn't work. I also tried to change it to Numeric (9,2) and it still didn't work. Does anyone know of the most efficient way to do this because my SP returns a lot of currency fields.
May 13, 2009 at 1:33 pm
Data Flow Source -> Right Click -> Advanced Editor -> Input and Output Properties -> Source Output -> Output Columns -> Select Column -> Change Data Type from Currency (likely default) to Numeric -> Change Scale to 2.
The above solution will only format the data as .00. It still drops the 0.00 on the output for some reason. However, it at least gets your closer to what is probably the full solution. The only way we've found to get the full 0.00 format is to use a string output. Annoying, but it was the only complete solution we've found so far.
May 13, 2009 at 1:55 pm
Yeah I know it's annoying! What I did though is convert all money fields to varchar in my SP. And since my target flat file has been created prior to this change all fields are showing as Currency. I don't think I had to do modify the datatype in the target file but just for consistency sake I did it anyway. Luckily, SSIS allows you to open the xml code and do find and replace. So this particular task is a piece of cake compared to the conversion in the SP.
Thanks for looking into this, I appreciate it!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply