Hi, I have an SSIS package which gets its data from a query executed on the database. I need to load this data into a flat file. I am using ragged right export, so my destination has fixed length fields. Now I have a float field named "Amount" in the source which has to be restricted to the format 000000000.00. I get Amount in this format by using CAST(Amount AS DECIMAL(11,2)) in the query. But when this data is loaded into the flat file it is loading a number of digits after the decimal like for example instead of 3383626.63 it is displaying 3383626.6299999999. Since my destination file has fixed length field this is resulting in an error. The error description is this - "[Flat File Destination [1178]] Error: Data conversion failed. The data conversion for column "Amount" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". Please help me with this issue.

What is the Flat File Destination Data Type for this field? Numeric or String? If String, then consider running the column through a Derived Column that a)converts it to String then b) removes anthing to the right of the decimal plus two.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Its actually a float value. Tried changing it to Decimal. Didn't work too.

It should work. Floats wouldn't. But if you set the data type in SSIS to a DT_DECIMAL or DT_NUMERIC, you shouldn't get "extra" decimal places shown. Are you sure that after you converted that column, you mapped it in the destination, and not the original float column? Talk to me now on

Please let me know where I exactly should set this datatype. Is it in the Flat File Connection Manager or in the Advanced Editor of Flat File Destination? I have been doing it in the Flat File Connection Manager so far. The ways I tried and didn't work are Decimal with a scale of 2 and also Numeric with precision 11 and scale 2. The input column width for this field is 12.

You need to set this data type on the Flat File Connection Manager's Advanced tab. When you used a Decimal type and scale of 2, how did it "not work"? Did it generate an error when you ran the package, or just give undesired results? Please post the error and/or results. Talk to me now on

Yes, I have been setting the datatype in the Advanced tab of Flat File Connection Manager. I got an error after running the package. It says [Flat File Destination [1178]] Error: Data conversion failed. The data conversion for column "Amount" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". Just to check, I increased the field width from 12 to 20 without changing the datatype/precision/scale and I got the output but undesired results like 3383626.6299999999 instead of 3383626.63

OK. Keep the Flat File Connection Manager column set to a decimal with a scale of 2. Now - what is the data type in the data flow of the column that you're mapping to the Flat File column? String or float? Talk to me now on

It is actually a float value. But I do a CAST on it before loading it into the flat file. CAST(SUM(Amount) AS DECIMAL(11,2)) AS 'PaymentsToDate' The Amount field is a float. PaymentsToDate is the one I am loading into the flat file and it is Decimal datatype. This query when executed on the Management Studio gives the desired result of 3383626.63. I get this long value after decimal(3383626.6299999999) after loading it into the flat file.

No - that's not what I'm talking about. The connector that hooks up to the Flat File Destination - double-click it. Go to the Metadata tab and find out what SSIS datatype "PaymentsToDate" is. Talk to me now on

Ohh ok. Sorry about that. The datatype on the Metadata tab for PaymentsToDate is DT_R8

OK. Found the solution to my problem. I used a Data Conversion task which converted my PaymentsToDate field to Numeric(11,2) before sending my query output to the flat file. Got the idea from Todd C's suggestion. Thank you.