I'm having an issue that apparenlty the entire internet can't solve. I wanted to create a new thread so I can summarize the problem with all the current findings. This is a known issue but for some reason I'm special and the suggested solution doesn't seem to work. Here is the issue: I need money values from a database to print properly on a ragged right flat file: 000.00. The intial problem was any values that had zero cents were getting printed without the cents value. 123.00 became 123. Values that were 0 weren't printed at all. I solved this by doing the following: On the OLE DB Source I right click and go to the advanced editor. In the Input and Output properties I go to OLE DB Source Output and expand output columns. I click on the column I want and change the data type to DT_NUMERIC with precision 12 and scale 2. That makes NO sense but it works. So now I'm getting ALMOST what I need. The problem NOW is values that are 0 get printed as .00 with no leading 0. That just looks freaking weird. At this point the suggestion from the community is to create a derived column. Makes sense right? So I do that with the following expression: (DT_STR,50,1252)([BALANCE] == 0 ? "0.00" : (DT_STR,50,1252)[BALANCE]) Now that's pretty straight forward. Check the column value and change the output to 0.00 if the amount is 0. If it's not 0 use the exising value and cast it to string. Then cast the entire thing to string. This doesn't work. Zero values are still coming out on the flat file as .00. The final piece of this is the flat file connector. I've experimented with different data types on the flat file; both string and numeric. Neither one makes a bit a difference. Does anybody have any idea what on earth is going on here?

* Hey falcon00 ease off, you are very close to breaching the EULA * I remember the thread very well. I think this post would be more helpful if you could provide more insight into where the data is coming from and how it looks just before it hits the Flat File Connector. I suggest you use Data Viewers to show us and you what is in the pipe.Arthur My Blog

Well this is handy. And it's coming from a SQL Server database of data type money. BALANCE 2160.00 3885.00 4785.00 4298.34 4055.32 4485.00 3885.00 3885.00 3885.00 4736.34 8370.00 4193.97 3885.00 4485.00 4123.66 3885.00 3885.00 6666.35 3972.74 4485.00 6870.00 3885.00 3885.00 3885.00 5985.00 3885.00 4785.00 4485.00 3885.00 3768.03 3885.00 5985.00 3885.00 3885.00 7891.80 3066.23 2615.90 2172.26 2180.61 2665.19 3521.86 6606.39 1286.64 1528.10 0.00 228.00 315.00 0.00 315.00 630.00 3696.00 0.00

So the issue is preserving the data format.Coming out as what datatype? I guess it is coming out off an OLEDB Data Source with SQL. And then what hits the Flat File Source?Arthur My Blog

It comes out of the OLEDB Source as DT_NUMERIC. The expression in the derived column cast it to DT_STR. That's what you're looking at above. That's what it is before it hits the flat file destination. Here is where it gets wacky. The data type setting in the FF connector doesn't seem to matter. I can set it as DT_STR or DT_NUMERIC. Either way 0 values still get printed as .00.

Printed where? Can we see a screenshot of what is in the Data Viewer before the Flat File Destination and what is in the file? Also its configuration (in the advanced editor) please Arthur My Blog

Printed on the outputted flat file. I can't do a screen shot because I can't rub out the sensative information. Same thing in the advanced editor. What I pasted above is what shows up in the data viewer. I'm assuming you mean the editor for the FF destination? It shows actual column names but if you let me know specifically what you need to see I'll share the settings.

What so sensitive can be in the advanced editor? The other sensitive info you can mask. Yes, I need the column specs (metadata) settings in the FF please.Arthur My Blog

Printed on the outputted flat file. I can't do a screen shot because I can't rub out the sensative information. Same thing in the advanced editor. What I pasted above is what shows up in the data viewer. I'm assuming you mean the editor for the FF destination? It shows actual column names but if you let me know specifically what you need to see I'll share the settings. You could also only select/show one column in the dataviewer.... to prevent showing sensative information.Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

SSISJoost, That's what I did. That's what the copy and paste above is. Arthur, Is this what you're looking for?

Ok I FINALLY stumbled on the solution! In the derived column I had it replacing an existing column. When I changed it to add as new column I FINALLY got it to work right!