Migrating date from MySQL to MSSQL run time error issue

Hi,
I am trying to migrate data from a table in MySQL to MSSQL.
I have been using SSIS Packages in Microsoft Visual Studio to help me do this but i have come across a problem. When pulling the informaion from the MySQL database i get a run time error.
This is the code i have been using:

TRUNCATE TABLE [DataView].[dbo].[CUSTOMER_HISTORY_TEMP];

GO

BEGIN

DECLARE @max_time VARCHAR(255), @remote_query VARCHAR(255)

SELECT @max_time = CONVERT(varchar,ISNULL(MAX(dw_timestamp),CAST('1990-01-01' AS DATETIME)),120)
FROM [DataWarehouse].[dbo].[CUSTOMER_HISTORY];

SET @remote_query = 'SELECT * FROM CUSTOMER_HISTORY WHERE dw_timestamp>="' + @max_time + '" ORDER BY history_id limit '

DECLARE @record_count INT, @while_counter INT, @limit_start INT, @limit_amount INT

SET @limit_start=0
SET @limit_amount=1000
SET @while_counter=-1

SELECT @record_count=count(*) FROM [DataView].[dbo].[CUSTOMER_HISTORY_TEMP]

WHILE @while_counter<@record_count
            BEGIN
                        DECLARE @final_query VARCHAR(255)
                        SET @final_query = @remote_query + CAST(@limit_start AS VARCHAR) + ',' + CAST(@limit_amount AS VARCHAR)
                        INSERT INTO [DataView].[dbo].[CUSTOMER_HISTORY_TEMP]
                        EXEC('SELECT * FROM OPENQUERY(IBBINTRAISP_LOCAL,''' + @final_query + ''') AS subq');
                        SET @while_counter=@record_count
                        SELECT @record_count=count(*) FROM [DataView].[dbo].[CUSTOMER_HISTORY_TEMP]
                        SET @limit_start= @limit_start + @limit_amount
            END
END

--This is the Error i am recieveing when i run this:
OLE DB provider "MSDASQL" for linked server "IBBINTRAISP_LOCAL" returned message "Requested conversion is not supported.".
Msg 7341, Level 16, State 2, Line 1
Cannot get the current row value of column "[MSDASQL].history_text" from OLE DB provider "MSDASQL" for linked server "IBBINTRAISP_LOCAL".

Any thought would be appriciated,
Thank you.



MickyNinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lee SavidgeCommented:
Hi,

This looks like you have a data casting problem. Certain field datatypes will need special handling. See this page for more info:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=304025&SiteID=1

Regards,

Lee
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MickyNinAuthor Commented:
Thank you Lee,
I will have a look and let you know how it goes.
Regards,
MickyNin
0
MickyNinAuthor Commented:
Hi, I have had a look at the forum as suggested by Isavidge.
I have tried to both using index's and checking the data types are the same between MySQL and MSSQL. I am still getting back the Error:

OLE DB provider "MSDASQL" for linked server "IBBINTRAISP_LOCAL" returned message "Requested conversion is not supported.".
Msg 7341, Level 16, State 2, Line 1
Cannot get the current row value of column "[MSDASQL].history_text" from OLE DB provider "MSDASQL" for linked server "IBBINTRAISP_LOCAL".

I have tried to break down the code to bring back the rows that seem to be throwing me back the problem but there seems to be nothing unusal about these rows and the stragest thing of all is that i could load this package previous to today and never had a proble, and these rows would have been loaded at that time?
Any ideas on what the problme would be greatly appreciated?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Lee SavidgeCommented:
Hi,

It isn't a row that is the problem. The problem is a particular column. In this case the column history_text column. What is the datatype in this column? Which database is it in? Is it in the MS SQL Server one or the other one? Have you tried using DTS on the SQL Server installation to pull the data from the other database rather than try and do it using a stored procedure?

Regards,

Lee
0
MickyNinAuthor Commented:
Hi,

I have checked the data types.In both MySQL  and MSSQL it is  a Text datatype with not null values.
I seen that this had posed a problem if the text datatype allowed nulls so i have changed this?
The reason for using the t-sql procedure was to use a while loop when importing the data into the temp table in MSSQL from MySQL, this avoided the issue of run time errors. I put this SQL into the Control Flow 'Execute SQL Task' Item and then in the data flow I use the Temp table as a source, i do a look up to split updates and inserts and this information flows into the destination table.

I have used this on other bigger tables and it seems to work?
I think your right that it has something to do with the text datatype on history_id, would and option be to change the data type to something esle on both MySQL and MSSQL?

Regards,
MickyNin
0
MickyNinAuthor Commented:
Hi
Do you know is there a difference in the size of the datatype 'TEXT' beetween MySQL and MSSQL could this be a problem?

Thank you,
MickyNin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.