September 24, 2009 at 5:19 am
Pls anyone help me on this error.how to convert the datatype?
Step 'Copy Data from Results to [DVP].[dbo].[CUSTOMER] Step' failed
Step Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Step Error Description:The number of failing rows exceeds the maximum specified. (Microsoft Data Transformation Services (DTS) Data Pump (8004202b): TransformCopy 'DirectCopyXform' conversion error: Conversion invalid for datatypes on column pair 30 (source column 'GROUPID' (DBTYPE_STR), destination column 'TermGroupID' (DBTYPE_I2)).)
Step Error code: 8004206A
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:0
Step Execution Started: 9/24/2009 6:31:54 PM
Step Execution Completed: 9/24/2009 6:32:13 PM
Total Step Execution Time: 18.625 seconds
Progress count in Step: 92000
September 24, 2009 at 6:38 am
Can you let us know what you have tried already? If you haven't checked I would make sure the datatypes in your source (GroupID) and destination (TermGroupID) are compatible.
September 24, 2009 at 3:00 pm
You're trying to import a char column (DBTYPE_STR) into a smallint column (DBTYPE_I2). Try using CAST or CONVERT when selecting the source data rather than just doing a column copy.
Greg
September 24, 2009 at 7:03 pm
I would like to implement the conversion as part of a DTS package.I just want to know wht i need to add into the package to allow the datatype convertion?pls help me on this problem ...
Your help appreciated,
Dwen
September 25, 2009 at 10:07 am
I assume you're using a Transform Data task to copy the data. In the Source tab of the task, use a SQL query rather than just selecting a table to copy from. Use a CAST or CONVERT function in the SQL query to convert the data type of GroupID column.
Greg
September 25, 2009 at 9:30 pm
Ya before this i use Transform Data task to copy the data.By using SQL query i was add the cast but the DTS still not working.The error msg is "Syntax error converting the varchar value BROC1 to a column of data type smallint".
This is the value from current data that required to map into new table.
------------------------------------------------------------------------------------------------
(source column 'GROUPID' (DBTYPE_STR) Vs destination column 'TermGroupID' (DBTYPE_I2)).)
B000 = 0
BROC1 = 1
CARD = 2
This is my query that was add to DTS for data selection.Can i use case to map the value?
SELECT ACCOUNT,
CAST (PAYGROUPID AS SMALLINT) GROUPID,
CASE GROUPID WHEN 'B000' THEN 0 WHEN 'BROC1' THEN 1 WHEN 'CARD' THEN 0 END GROUPID,
CREATEBY FROM CUSTOMER
Pls anyone help or advise me on this problem.
Thank for help ----Dwen
September 26, 2009 at 6:29 pm
Ya before this i use Transform Data task to copy the data.By using SQL query i was add the cast but the DTS still not working.The error msg is "Syntax error converting the varchar value BROC1 to a column of data type smallint".
This is the value from current data that required to map into new table.
------------------------------------------------------------------------------------------------
(source column 'GROUPID' (DBTYPE_STR) Vs destination column 'TermGroupID' (DBTYPE_I2)).)
B000 map to 0
BROC1 map to 1
CARD map to 2
This is my query that was add to DTS for data selection.Can i use case to map the value?
SELECT ACCOUNT,
CAST (GROUPID AS SMALLINT) GROUPID,
CASE GROUPID WHEN 'B000' THEN 0 WHEN 'BROC1' THEN 1 WHEN 'CARD' THEN 0 END GROUPID,
CREATEBY FROM CUSTOMER
Pls anyone help or advise me on this problem........
Thank for help ----Dwen
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply