Data conversion in SSIS package

Question

Post reply

Data conversion in SSIS package

Ashutosh Jha

Old Hand

Points: 374
More actions
September 28, 2007 at 8:18 am

#374906

I am importing data from Excel file and destination OLEDB SQL 2005, in between i am converting data from nvarchar to varchar but it is showing folowwing error
1.[Data Conversion [3013]] Error: Data conversion failed while converting column "System Type" (2978) to column "Copy of System Type" (3097). The conversion returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
2.[Data Conversion [3013]] Error: The "output column "Copy of System Type" (3097)" failed because truncation occurred, and the truncation row disposition on "output column "Copy of System Type" (3097)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
3.[DTS.Pipeline] Error: The ProcessInput method on component "Data Conversion" (3013) failed with error code 0xC020902A. 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.
4.[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC020902A. [/color]
i also used data conversion component
Please give some sugestion to get out of this problem
Thanks
ashutosh

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Dr. John Tunnicliffe Right there with Babe Points: 782 More actions · Answer 1

Try increasing the size of the strings you are using just to ensure it is not a truncation error.

vivekanandaraj SSC Veteran Points: 264 More actions · Answer 2

hi,

the data which is coming from excel is not an unicode format!

by default size would be around 255 char;

so go with the same conversion component, for eg.,

integer--> four byte signed int, like that we have to mention

for strings--> str[4 byte], like that choose the best format which you can find as the suggesion in the error component!

otherwise do the reverse thing, change the data type in dbo.table;

made easy:)

swatianand83 SSC Eights! Points: 870 More actions · Answer 3

I am trying to import a csv file to a database table thru SSIS. In the file we have one date column and i have explicitly converted it to datetime in the package. But while execution, it extracts some records and then on one of them it throws an error of 'data conversion failed while converting....'

please help.

thanks

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 4

Are you importing this as date (DT_DBDATE), time (DT_DBTIME), or as datetime (DT_DBTIMESTAMP)?

Are you sure that the content of the file is truly date and time?

I suggest you import as a test as a simple varchar(100) to somewhere else, and then try and cast the problematic field as datetime on the database.

If SQL complains, then you know its content, if it doesnt complain, then you know you may need to work on setting your field

dpatelia SSC Veteran Points: 286 More actions · Answer 5

You need to convert data into actual data type. Let say your input column contains VARCHAR(10) AND output contain VARCHAR(50) then it will fail. If you are using derived column/variable in that case you have to convert data into actual data type. I have samililar issue and then found that at database side it was showing DT_STR(255) BUT DERIVED COLUMN WAS showing DT_wSTR(0) and in derived column no where I specify width but it was comiing default as 0 so then I converted it with

(DT_STR,255,1252)Derived Column variable/function

and then after it worked.

Regards

Daxesh

dakotad Valued Member Points: 51 More actions · Answer 6

Fortunately I had some sample files from previuos uploads and had to have the DT_DECIMAL, DT_STR and DT_DATE InputColumnWidths agree with the actual file column widths on the "Advanced" page under the the "Flat File Connection Manager Editor". Everything worked after that. I used UltraEdit to open up the text file and count the column widths of the file. I had to manage the columns in SSIS because the output required a <CR><LF> at the end of each row. I found it easier to define the column widths and then after the last row, send a ColumnDelimiter of {CR}{LF}. Someone may know of an easier way but this worked and after four days of hacking, I was just glad I got this to work.

That solved my problem and I hope this helps.

khau tinh SSC Journeyman Points: 80 More actions · Answer 7

khau tinh

SSC Journeyman

Points: 80

February 9, 2011 at 5:40 pm

#1284911

dakotad,

Thanks. Your tip worked for me.

newbie