November 24, 2008 at 1:35 am
Scenario:
SQL 2005
SSIS package
Flat file (CSV)
FlatFile --> OLE DB Destination
Problem:
Within the flat file a column number 4 looks like "3.144591"
I have set the DataTpe for this column to Numeric (I have also tried most the other types) but I keep getting this error:
Error: 0xC02020A1 at ImportFiles, Profit [179]: Data conversion failed. The data conversion for column "Column 4" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
Error: 0xC0209029 at ImportFiles, Profit [179]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "Column 4" (247)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "Column 4" (247)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
Error: 0xC0202092 at ImportFiles, Bargains [179]: An error occurred while processing file "E:\NewFiles\Profit.CSV" on data row 1.
Any ideas?
should I be using Derived Column Or Data Conversion?
Thanks all!
November 24, 2008 at 10:24 am
How do you have the numeric value defined?
November 25, 2008 at 3:55 am
Withing SQL DB the column is defined as VARCHAR(16),Null
Within SSIS the column is defined as:
OutputColumnWidth - 16
DataType - String[DT_STR]
TextQualified - True
November 25, 2008 at 3:59 am
If this is pure numeric data in a CSV file, it should not be surrounded by text qualifiers ("). Try stripping these text qualifiers out from this field.
Phil
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 25, 2008 at 4:53 am
I cannot edit the CSV file.
How can I use Derived Column exspress to change the column value from: 24 Nov 2008
to: 24-NOV-08
Need help with the Expression.
thanks
(slowly going mad)
December 3, 2008 at 10:54 am
pri.amin (11/25/2008)
I cannot edit the CSV file.How can I use Derived Column exspress to change the column value from: 24 Nov 2008
to: 24-NOV-08
Need help with the Expression.
thanks
(slowly going mad)
Take a look at the REPLACE function
REPLACE(haystack, needle, replace value)
December 3, 2008 at 1:01 pm
You might go to the Advanced Editor of your flat file source and look at the data types and "Length" of the column in question in the Input and Output Properties. Those are in the Source Output -> Output Columns of the Input and Output tab of the Advanced Editor.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply