Creating SSIS Package to Extract Data from MySQL and Append to SQL 2005

Hi guys,

I'm a newbie DBA and i'm trying to create a package that would extract data from MySQL and inserts them to a SQL 2005 Server. I'm quite new to this SSIS and would like to ask help from you to help me go through with this.

I hope you guys can help me with this.

Hoping to hear from you soon.

Thank you so much.

Kind regards,
Neil
I'm sure everyone on the forum will be happy to help. I'd suggest starting by going through the SSIS Tutorials in SQL Server Books Online. Then, as you encounter specific problems or design questions, we'll be happy to answer them.|||

Yep, I agree with John.

Just a hint though, you'll be using a DataReader Source against your MySQL ODBC driver to retrieve the results.

|||

Actually, i have gone through the BOL tutorial and have started creating the SSIS package using DataReader Source. i then provided the connection manager which is in my case using ADO.Net connection (MySQL ODBC 3.51 Driver) but as soon as i enter the SQLCommand and click ok this error displays on the bottom part of the Advanced Editor for DataReader Source.

Error at Data Flow Task [DataReader Source[1]]: System.Data.Odbc.OdbcException: ERROR [HY010]

[MySQL][ODBC 3.51 Driver][mysqld-4.1.12-Debian_1ubuntu3.4-log]

What do u think is causing this?

Thanks,

Neil

|||

If you could provide the query, that would help troubleshoot the issue.

If you are trying to use parameters or a complex query, try building it in a variable and using an expression on the data flow to set the SQL source. Expressions for the data flow can be found by clicking the data flow, opeing the properties window (F4), and looking for the expressions item.

|||

Below is the SQLCommand i am using. What's troubling me is that the DTS for this in SQL 2000 is running smoothly that is why i was thinking maybe i am using the wrong steps in SSIS.

SELECT chatrequestlogs.`chat_session`,
chat_admin.`name`,
chatrequestlogs.`ip`,
FROM_UNIXTIME(chatrequestlogs.created) AS Time_Requested,
FROM_UNIXTIME(chattranscripts.created) AS Time_Created,
chatrequestlogs.status,
LTRIM(substring(chattranscripts.plain, 1, locate(':',chattranscripts.plain, 1) - 1)) AS Name_Registered,
LTRIM(substring(substring(chattranscripts.formatted, locate('<question>', chattranscripts.formatted,1), locate('</question>', chattranscripts.formatted, locate('<question>', chattranscripts.formatted, 1)) - locate('<question>', chattranscripts.formatted, 1)),11)) AS Question,
LTRIM(substring(chattranscripts.plain, locate(':', chattranscripts.plain, 1) + 1)) AS Comments,
chatrequestlogs.deptID,
substring(substring(formatted, locate('<tstamp (', formatted), locate(') tstamp>', formatted) - locate('<tstamp (', formatted)), 10) AS Time_Started,
reverse(substring(substring(reverse(formatted), locate('>pmatst )', reverse(formatted)), locate('( pmatst<', reverse(formatted)) - locate('>pmatst )', reverse(formatted))), 10)) AS Time_Ended,
chat_admin.`email` AS CSCEmail,
chattranscripts.`email` AS CustomerEmail
FROM chatrequestlogs
JOIN chat_admin ON chatrequestlogs.userID = chat_admin.`userID`
JOIN chattranscripts ON chattranscripts.chat_session = chatrequestlogs.chat_session
WHERE FROM_UNIXTIME(chatrequestlogs.created) >= DATE_ADD(NOW(), INTERVAL - '1440' MINUTE)
AND FROM_UNIXTIME(chatrequestlogs.created) < NOW()

|||

Hi guys,

The problem went away after I turned the ValidateExternalMetadata property of the datasource reader to false.

Thank you so much for the replies.

Forums like these are of great help to newbies like me.

Keep up the good work guys.

Kind regards,