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,

Creating index - Error 229 (Permission denied)

I have been working with my DBA and have not yet found the issue.
I have my development database on a SQL Server 2005 server. I enabled
it for full-text catalogs and created a catalog with no issues
whatsoever. When I go to create the index, however, I run through the
wizard (using SMS) until the last step. Upon finishing the wizard, I
get the following error:
Failed to retrieve data for this request (Microsoft.SqlServer.SmoEnum)
Additional Information:
A exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)
EXECUTE permission denied on object 'sp_help_category', database
'msdb', schema 'dbo'.
SELECT permission denied on object 'sysjobs_view', database 'msdb',
schema 'dbo'. (Microsoft SQL Server, Error: 229).
I am the dbowner, and the DBA granted me temporary DBA privileges with
no difference (still received the same error). The DBA created the
index for me with no problems. I can see the index (I have not yet
tried using it). I can pull up the property pages and see "General"
and "Columns" views fine, but when I click on "Schedules", I receive
the same error as above.
Any thoughts?
Thanks.
You will need to be in the dbo_role on the msdb database as well.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<tlchurch@.gmail.com> wrote in message
news:1170424716.096179.254400@.l53g2000cwa.googlegr oups.com...
>I have been working with my DBA and have not yet found the issue.
> I have my development database on a SQL Server 2005 server. I enabled
> it for full-text catalogs and created a catalog with no issues
> whatsoever. When I go to create the index, however, I run through the
> wizard (using SMS) until the last step. Upon finishing the wizard, I
> get the following error:
> Failed to retrieve data for this request (Microsoft.SqlServer.SmoEnum)
> Additional Information:
> A exception occurred while executing a Transact-SQL statement or
> batch. (Microsoft.SqlServer.ConnectionInfo)
> EXECUTE permission denied on object 'sp_help_category', database
> 'msdb', schema 'dbo'.
> SELECT permission denied on object 'sysjobs_view', database 'msdb',
> schema 'dbo'. (Microsoft SQL Server, Error: 229).
> I am the dbowner, and the DBA granted me temporary DBA privileges with
> no difference (still received the same error). The DBA created the
> index for me with no problems. I can see the index (I have not yet
> tried using it). I can pull up the property pages and see "General"
> and "Columns" views fine, but when I click on "Schedules", I receive
> the same error as above.
> Any thoughts?
> Thanks.
>
|||Hello tlchurch,
I am surprised your DBA hasn't ben able to help, as the error is fairly self
explanatory.
You do not have permissions to access sysjobs_view in database msdb and also
execute sp_help_category in msdb.
Your DBA needs to give you the correct permissions to see the schedules,
which are store in msdb.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> I have been working with my DBA and have not yet found the issue.
> I have my development database on a SQL Server 2005 server. I enabled
> it for full-text catalogs and created a catalog with no issues
> whatsoever. When I go to create the index, however, I run through the
> wizard (using SMS) until the last step. Upon finishing the wizard, I
> get the following error:
> Failed to retrieve data for this request (Microsoft.SqlServer.SmoEnum)
> Additional Information:
> A exception occurred while executing a Transact-SQL statement or
> batch. (Microsoft.SqlServer.ConnectionInfo)
> EXECUTE permission denied on object 'sp_help_category', database
> 'msdb', schema 'dbo'.
> SELECT permission denied on object 'sysjobs_view', database 'msdb',
> schema 'dbo'. (Microsoft SQL Server, Error: 229).
> I am the dbowner, and the DBA granted me temporary DBA privileges with
> no difference (still received the same error). The DBA created the
> index for me with no problems. I can see the index (I have not yet
> tried using it). I can pull up the property pages and see "General"
> and "Columns" views fine, but when I click on "Schedules", I receive
> the same error as above.
> Any thoughts?
> Thanks.
>