CODEWITHSUNDEEP
sql-serverssis
Steve
Steve

Reputation: 79

How to make SSIS truncate numeric field in Flat File Destination

I'd like some advice about handling truncation issues in SSIS. I have a column Col1 which is MONEY in a table. I'd like to output that to a text file (fixed width, ragged right). In the output file, the column which holds Col1 must only be 8 characters wide.

In the OLEDB Data Source, Col1 is specified as:
currency [DT_CY] in both the External Columns and Output Columns tab.

In the Flat File Connection Manager's Advanced tab, Col1 is specified as:
currency [DT_CY], with InputColumnWidth set to 8.

If I populate Col1 with 123456789.00 and execute the task, the OLEDB source succeeds and passes rows to the destination, but the task fails with :

Error: 0xC02020A1 at DFT_Test, FFDEST_Test [3955]: Data conversion failed. The data conversion for column "Col1" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

Error: 0xC02020A0 at DFT_Test, FFDEST_Test [3955]: Cannot copy or convert flat file data for column "Col1".

I want to avoid these truncation errors. In the Error Output of the source, I change the Truncation property for Col1 from Fail Component to Ignore Failure. I would have expected that would resolve the issue, but executing the task still gives the same error.

Can someone give some guidance about how to make SSIS simply truncate the column to 8 charactes?

Upvotes: 1

Views: 1219

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31785

Use a Derived Column task to create a column that is an 8-character string and populate it from the money column. Then in the Destination component, map the Derived Column to the Col1 Destination instead of the original column.

Or, even better, in your source component, use a SQL query that converts your money column to a varchar(8) or char(8) column.

Upvotes: 1

Related Questions