MySQL Dump into SQL Server

I have a MySQL database and I would like to understand the best mechanism to do the following:

1) Export data from MySQL (already a database dump is being generated)
2) Schedule an import of this MySQL Database (import)
3) Do a delta load (so that I am not every night loading the whole database)
4) Run standardized scripts to modify and then output the calculated results (for consumption of a business intelligence tool that I am setting up).

I guess one consideration here is whether I import the database into MySQL or just the data dumps.

I will most likely give this job to a contractor as I know it's very easy - I just don't want to spend a long time troubleshooting.  I want to know what is required to be done with some idea technically how this will be done.  Would appreciate some details - e.g. if you advise where in the SQL Server Studio tool this is done.

Thanks,
Raheel
hennanra3Asked:
Who is Participating?
 
QuinnDexConnect With a Mentor Commented:
this will cover steps 1 2 and 3 more information is needed for 4

first download the correct connector from the link below from (suggest MySQL ODBC Connector 5.1)

MySQL ODBC Connector


Open your ODBC Data Source Administrator from the Control Panel -> Administrative Tools. Under the tab labelled as "System DSN", press the "Add" button.

On the "Create New Data Source" dialog that appeared, choose MySQL ODBC 5.1 Driver and then press the "Finish" button.

After that, a MySQL connection configuration dialog will appear. Add your MySQL database account information in it, preferably the "root" account which has full access to your databases in MySQL. Do not change the port to anything other than 3306, unless during your MySQL server installation, you have defined something else.

Press the "Test" button to ensure your connection settings are set properly and then the "OK" button when you're done.

In this state, you are ready to establish a link towards MySQL database from your Microsoft SQL Server Management Studio. Open a query window and run the following SQL statement:


EXEC master.dbo.sp_addlinkedserver 
@server = N'MYSQL', 
@srvproduct=N'MySQL', 
@provider=N'MSDASQL', 
@provstr=N'DRIVER={MySQL ODBC 5.1 Driver}; SERVER=localhost; _
	DATABASE=yourdb; USER=root; PASSWORD=yourpass; OPTION=3'

Open in new window


This script will produce a link to your MySQL database through the ODBC connection you just created in the previous stage of this article. The link will appear in the Microsoft SQL Server Management Studio under linked servers


Create a new database in Microsoft SQL Server  called  "testMySQL". In the query window, run the following SQL statement to import table shoutbox from the MySQL database yourdb, into the newly created database in Microsoft SQL called testMySQL.

SELECT * INTO testMySQL.dbo.shoutbox
FROM openquery(MYSQL, 'SELECT * FROM yourdb.shoutbox')

Open in new window


full detail and step by step images can be seen here
0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Couple of ways to pull this off, but the way I'd do this (disclaimer:  I have a hammer, and everything looks like a nail) is...

1)  SSIS package with a data flow task with defined source (MySQL) and target (SQL).
2)  SQL Agent job that fires the SSIS package on a predetermined basis (nightly, whatever), unless you guys run a third-party scheduling tool such as ActiveBatch.
3)  Either the SSIS package will handle the MERGE using JOINs, or create a SQL Stored Procedure with a MERGE statement that pulls this off.   Better would be SSIS using the Task Factory Upsert control, but that's a $1k license for the whole suite of tools.
4)  Various SQL Tasks within the SSIS package. If 'output the calculated results' means more than one target, then the data flow would have a multicast and multiple destinations.

Hope this helps.
Jim
0
 
hennanra3Author Commented:
Thanks both!
0
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.

All Courses

From novice to tech pro — start learning today.