November 14, 2008 at 9:09 am
Thanks in Advance,
We are bringing in a flat file that is formatted fixed width. We are
using SQL 2005 and SSIS. Some columns are formatted DT_Numeric (14,2)
and the destination table in a SQL database is formatted that way. In
some cases the flat file returns a value of 14 empty spaces in the
fixed width space that corresponds to a DT_Numeric (14,2) field in a
SQL table in the OLEDB destination connection. When I attempt an
integration I get the following message:
Error: 0xC02020A1 at Load Promotions Export, Promotions Export FF [1]:
Data conversion failed. The data conversion for column "Rebate"
returned status value 2 and status text "The value could not be
converted because of a potential loss of data.".
I would like to perform this data load in SSIS without building an
intermediate table. I have tried the data type conversion and derived
column transformations without success, but I may being doing
something wrong in those transforms. I have also set the Retain Null
flag to both on and off without change. I verified that the flat file
connection manager is selecting the correct 14 empty spaces which I
need to ultimately be a DT_Numeric (14,2). I imagine that an
intermediate table which is formatted for a char (14) would solve the
problem, but shouldn’t SSIS be able to handle this out of the box with
a transform?
Mark Simmerman
Napa, CA
November 16, 2008 at 5:53 pm
Mark, if I read you correctly you are running into an issue where the absense of a value in the source returns a fixed width 14 character field, which equates to 14 space characters. You should be able to use the derived column transform to substitute an appropriate value. Try something like the following - I'm doing this blind without my BIDS studio open, so be sure to check the syntax.
LEN(TRIM(my_numeric_value)) == 0 ? "0.00" : TRIM(my_numeric_value)
Using the above, if a blank value is returned by your data source you will end up with a zero in the destination, otherwise the numeric value will be passed over. Note that you may still have to change the data type on your data source from a numeric to a CHAR so that the package does not fail before reaching the derived column.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
November 17, 2008 at 2:37 pm
I think Tim has the solution. If you do not want the 0's you could use the Null transform for numeric.
NULL(DT_NUMERIC, 14,2)
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 23, 2009 at 10:16 am
I am having a similar problem using SSIS. I have a flat file with a string that has 12 spaces in a field. I need to convert the column from string to currency. If I use a Derived Column, what would that look I need to code? I am new to this.
I have worked on this for a couple days now and any help would be appreciated.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply