Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Data conversion in SSIS package Expand / Collapse
Author
Message
Posted Friday, September 28, 2007 8:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 10, 2008 9:51 AM
Points: 4, 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
Post #404052
Posted Monday, November 19, 2007 3:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 28, 2013 5:11 AM
Points: 24, Visits: 66
Try increasing the size of the strings you are using just to ensure it is not a truncation error.
Post #423823
Posted Tuesday, November 20, 2007 4:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 14, 2014 5:04 AM
Points: 6, 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 easy:)
Post #423981
Posted Friday, May 16, 2008 7:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 31, 2009 4:28 AM
Points: 42, 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
Post #502062
Posted Thursday, May 22, 2008 11:46 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 3, 2010 2:32 AM
Points: 1,249, 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
Post #505619
Posted Wednesday, September 22, 2010 1:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, November 14, 2010 3:35 PM
Points: 2, 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
Post #991491
Posted Tuesday, December 21, 2010 5:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:52 PM
Points: 1, 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.
Post #1038022
Posted Wednesday, February 9, 2011 5:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 27, 2014 11:20 AM
Points: 4, Visits: 205
dakotad,

Thanks. Your tip worked for me.

newbie
Post #1061646
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse