Error-Data Conversion Component

Hi

I am getting error in an SSIS,

Source-Excel File

Destination-SQL Server

Source has fields of DataType-Unicode strig[DT_WSTR], I am using "Data Conversion" Component to convert these into Integer.

Source(Unicode strig[DT_WSTR])Destination(numeric[DT_NUMERIC] Precision:18 ; Scale:0)

Error:

[Data Conversion [14]] Error: Data conversion failed while converting column "Column1" (99) to column "Column1_Converted" (204). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

[Data Conversion [14]] Error: The "output column "Column1_Converted" (204)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "Column1_Converted" (204)" specifies failure on error. An error occurred on the specified object of the specified component.

Can anyone tell me which Data Type should I use to convert string into integer.

Thanks

If you have control over the database table, you should get rid of using a numeric data type to hold an integer. Instead, you should use an integer data type.

Then inside SSIS, I'd personally use a derived column instead of a data conversion component and use the following expression:

(DT_I4)TRIM([yourField])

You can replace DT_I4 with any of the appropriate integer data types. (unsigned integers, 8 byte integers, etc...)|||

I can't change the DB table as an application is running on that and we are using this SSIS for the initial load of the data. But I am trying what u have suggested. Thanks a lot for replying, I'll get back to u after trying it.

|||In that case, this is a better expression:

(DT_NUMERIC,18,0)TRIM([yourColumn])|||

I tried it, but still no luck

This is what I am doing:

Step 1:Excel Source->

Step 2: columns-->

Derived Column Name Derived Column Expression Data Type Length

Column1 Replace 'Column1' (DT_I4)TRIM([Column1]) Unicode string [DT+WSTR] 255

Column2 Replace 'Column2 (DT_I4)TRIM([Column2) Unicode string [DT+WSTR] 255

Step 3ata Conversion-->

Input Column Output Alias Data Type Length Code Page

Column1 Column1_STR eight-byte unsigned integer[DT_UI8] 1252 (ANSI -Latin I)

Column2 Column2_STR eight-byte unsigned integer[DT_UI8] 1252 (ANSI -Latin I)

Step 4: OLE DB DEstination

With Columns mapped to Column1_STR and Column2_STR

Error:

[Derived Column [20]] Error: An error occurred while attempting to perform a type cast.
[Derived Column [20]] Error: The "component "Derived Column" (20)"
failed because error code 0xC0049064 occurred, and the error row
disposition on "input column "Column1" (1260)" specifies failure on error.
An error occurred on the specified object of the specified component.

|||

I tried (DT_NUMERIC,18,0)TRIM([Column1]) also,

Error

[Derived Column [20]] Error: The "component "Derived Column" (20)" failed because error code 0xC0049064 occurred, and the error row disposition on "input column "Column2" (1260)" specifies failure on error. An error occurred on the specified object of the specified component.

[DTS.Pipeline] Error: The ProcessInput method on component "Derived Column" (20) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component,
but the error is fatal and will cause the Data Flow task to stop running.

I can change the Excel file if it can solve this problem.

|||Excel Source -> Derived Column -> OLE DB Destination

Derived column component:
Derived Column Name Derived Column Expression Data Type
NumColumn1 <add as new column> (DT_NUMERIC,18,0)TRIM(Column1) numeric [DT_NUMERIC]

Then just use NumColumn1,2,3, etc... in your destination. Don't use the original columns from the Excel source. You can't cast a column to a different data type in place -- you need to create a new output column.|||

Still no luck,

Now I am adding a new column,

Derived Column Name Derived Column Expression Data Type Precision Scale
NumColumn1 <add as new column> (DT_NUMERIC,18,0)TRIM([Column1]) numeric [DT_NUMERIC] 18 0

NumColumn2 <add as new column> (DT_NUMERIC,18,0)TRIM([Column2]) numeric [DT_NUMERIC] 18 0

Error:

Derived Column [20]] Error: The "component "Derived Column" (20)" failed because error code 0xC0049064 occurred, and the error row disposition on
"output column "NumColumn1" (143)" specifies failure on error. An error occurred on the specified object of the specified component.

[DTS.Pipeline] Error: The ProcessInput method on component "Derived Column" (20) failed with error code 0xC0209029. The identified component returned an error
from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

|||Are you sure you have numeric (and nothing else) in those columns? I'm expecting to see some alpha characters or something in your columns that can't convert. Do you have decimal points in the raw data? Better yet, can you post some sample data from column1 and column2?|||

Column1 Column2
Row1- 21 34
Row2-
Row3- 28 246
Row4-
Row5- 21 223
Row6-
Row7-
Row8- 30 206
Row9- 21 145
Row10-
Row11- 25 236

|||New expression:

ISNULL(TRIM([yourColumn])) || TRIM([yourColumn] == "" ? NULL(DT_NUMERIC,18,0) : (DT_NUMERIC,18,0)TRIM([yourColumn])
|||

Added new Expression

error:

[Derived Column [20]] Error: The "component "Derived Column" (20)" failed because
error code 0xC0049063 occurred, and the error row disposition on
"output column "Derived Column 1" (1236)" specifies failure on error.
An error occurred on the specified object of the specified component.

|||

I think you will need some extra parens to get what you want (both the isnull test and the empty string test as the condition):

( ISNULL([yourColumn]) || TRIM([yourColumn]) == "" ) ? NULL(DT_NUMERIC,18,0) : (DT_NUMERIC,18,0)TRIM([yourColumn])

The error you saw, though, was that the conditional operation failed, so this might not fix that still.

|||

Yah u're right, I've added the extra parenth but still no luck.