"requested conversion is not supported" error in DTS data copy.

I have a DTS that pulls data in from a table in MysQL and inserts it into a tablle in SQL Server. it's failing when I run the step with the "requestd conversion is not supported" error, but when testing each single column copy in the Data Transformation step, it passes.

I just want an exact copy of the data.

the source MySQL table is...

CREATE TABLE `entry` (
`web_id` int(11) NOT NULL auto_increment,
`web_name` varchar(40) NOT NULL default '',
`web_email` varchar(60) NOT NULL default '',
`web_cli` varchar(50) NOT NULL default '',
`web_pu_id` varchar(60) default NULL,
`web_answer` varchar(60) NOT NULL default '',
`web_ddi` varchar(30) default NULL,
`web_time_entered` datetime NOT NULL default '0000-00-00 00:00:00',
`web_ip_address` varchar(20) default NULL,
`web_entry_status` set('failed','success') NOT NULL default '',
`web_polled` enum('true','false') default 'false',
PRIMARY KEY (`web_id`)
) ENGINE=MyISAM;

and the SQl Server destination is...

CREATE TABLE [entry] (
[web_id] [int] NOT NULL ,
[web_name] [varchar] (23) NOT NULL ,
[web_email] [varchar] (37) NOT NULL ,
[web_cli] [varchar] (15) NOT NULL ,
[web_pu_id] [varchar] (15) NULL ,
[web_answer] [text] NOT NULL ,
[web_ddi] [varchar] (11) NULL ,
[web_time_entered] [datetime] NOT NULL ,
[web_ip_address] [varchar] (15) NULL ,
[web_entry_status] [varchar] (15) ,
[web_polled] [varchar] (15) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [temp_webentry] WITH NOCHECK ADD
CONSTRAINT [PK_tmpwebentry] PRIMARY KEY CLUSTERED
(
[web_id]
) ON [PRIMARY]
GO

sample line of data ...

352711, 'some name', 'email@.addre.ss', '0123456789', 'AA_XX-Y-012345-2', '', '098765432', '2005-10-19 23:17:42', '192.168.28.165', 'failed', 'false'scratch that.. in compairing the DDLs it was obvious..

varchar -> text doesn't work well :)

a

"Conversion failed when converting from a character string to uniqueidentifier."

This is probably a simple wuestion but i would appreciate some help

I am trying to get and insert theuserId into a table called "Orders"

I got the user id as

Dim CustomerId As Object = Membership.GetUser(User.Identity.Name).ProviderUserKey.ToString

The button on click command is

worldshop.ShoppingCart.CreateOrder(CustomerId,...................

The Function Has the parameter for Customer Id as follows

Dim dbParam_CustomerId As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_CustomerId.ParameterName = "@.CustomerId"
dbParam_CustomerId.Value = CustomerId
dbParam_CustomerId.DbType = System.Data.DbType.String
command.Parameters.Add(dbParam_CustomerId)

When i run this I am getting the error message

"Conversion failed when converting from a character string to uniqueidentifier."

Can anyone tell me where I am going wrong

many thanks

Martin

What type is CustomerID defined as in the database? It sounds like it is NOT a string, but rather a uniqueidentifier (a GUID).|||

thanks for your reply. i got it sorted out

martin

|||Do you mind sharing with us what your solution was? as I am having the same problem...|||

I'll check and get back to you

martin

|||

sorry about the delay in getting back to you

I was having the same problem. I solved it by instantiating the userid as an object

Dim userId As Object = Membership.GetUser(User.Identity.Name).ProviderUserKey

i passed the object to the function as a guid

ByVal userId As Guid
Dim dbParam_userId As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_userId.ParameterName = "@.UserId"
dbParam_userId.Value = userId
dbParam_userId.DbType = System.Data.DbType.Guid
command.Parameters.Add(dbParam_userId)

last i put the guid into the database as char 36

@.UserId char (36)

HTH