Data Transformation Services (DTS) - SQL 2000

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Data Transformation Services (DTS) - SQL 2000

Author

Topic

kakikupart
Starting Member

11 Posts

Posted - 2009-09-24 : 07:31:10

Pls anyone help me on this error,how to convert the data & datatype
using DTS?Now i got this error ....

Step 'Copy Data from Results to [POSDVP].[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

Your help appreciated ...
Dwen ..

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-24 : 09:14:18

Conversion invalid for datatypes on column pair 30
source column 'GROUPID' (DBTYPE_STR), destination column 'TermGroupID' (DBTYPE_I2)

That means ~ incoming string cannot go into integer destination.

No, you're never too old to Yak'n'Roll if you're too young to die.

kakikupart
Starting Member

11 Posts

Posted - 2009-09-24 : 20:49:25

So,wht I need to do in DTS to allow the convertion

chorofonfilo
Starting Member

40 Posts

Posted - 2009-09-25 : 01:57:41

As a way to test your problem and try to help you, just performed a DTS to migrate the table Categories from the Northwind database to another called Categories1 in which i converted the first column Categoryid from an int to a char, and after that i switched back from table Categories1 to another called Categories2 converting the column again to an int, and it worked well.
I created the DTS package using the DTS assistant of the Enterprise Manager.
If you think this can help you, i can let you know how i made it so you can try it too, maybe the string you are trying to convert is resulting into a big number that cant be parsed accordingly for an int so maybe you can try parsing it to different numeric type.

Perseverance worths it...:)

kakikupart
Starting Member

11 Posts

Posted - 2009-09-25 : 23:38:26

Before this i use Transform Data task to copy the data.By using SQL query i was add CAST but 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.
------------------------------------------------------------------------------------------------
Current- 'GROUPID'(DBTYPE_STR) New -'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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-26 : 04:01:08

select
account,
case
when Paygroup = 'B000' then 0
when Paygroup = 'BROC1' then 1
when Paygroup = 'CARD' then 2
else Paygroup
end as GROUPID,
CREATEBY
from Customer

No, you're never too old to Yak'n'Roll if you're too young to die.

kakikupart
Starting Member

11 Posts

Posted - 2009-10-05 : 00:02:33

Thx a lots,it work fine .....

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-05 : 08:59:08

welcome