SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Data conversion in SSIS package


Data conversion in SSIS package

Author
Message
Ashutosh Jha
Ashutosh Jha
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 47
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
Dr. John Tunnicliffe
Dr. John Tunnicliffe
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 71
Try increasing the size of the strings you are using just to ensure it is not a truncation error.
vivekanandaraj
vivekanandaraj
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 14
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 easySmile
swatianand83
swatianand83
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 140
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
pduplessis-723389
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3233 Visits: 400
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
dpatelia
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 17
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
dakotad
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 35

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
khau tinh
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 205
dakotad,

Thanks. Your tip worked for me.

newbie
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search