SQLTeam.com | Weblogs | Forums

SSIS - Connect to MySQL

Hi everybody,

what is the best way to connect a MySQL-Database.

I know two ways

  1. With Ado.Net-Source and ODBC-Datasource
  2. With MySQLDataProvider

Are there some differences between this types? Or does it not matter which type i use?

We just create a Linked Server, something like this:

-- Replace these strings globally:
--	RemoteName
--	MySQL_RemoteName
--	MyLocalLogin	-- (Optional)
--	MyRemotePassword
--	MyRemotePassword

-- Create Linked Server
EXEC sp_addlinkedserver 
	@server = 'MySQL_RemoteName'	-- local name for the linked server being created
	, @srvproduct = 'MySQL'		-- product name of the OLE DB data source to add as a linked server
-- Following parameters ONLY required if NOT connecting two SQL Servers
	, @provider = 'MSDASQL'
	, @datasrc = 'RemoteName'	-- name of the data source as interpreted by the OLE DB provider (DBPROP_INIT_DATASOURCE property)

-- Alternative parameters are required for NON-Standard Port usage
-- Remove any existing Linked Server Login (optional)
EXEC sp_droplinkedsrvlogin
	@rmtsrvname = 'MySQL_RemoteName'
	, @locallogin = 'MyLocalLogin'

-- Create Linked Server Login
EXEC sp_addlinkedsrvlogin
	@rmtsrvname = 'MySQL_RemoteName' 
	, @useself = 'false'	-- true=Connect using current UserID/Password, false=use rmtuser/rmtpassword below
	, @locallogin = NULL	-- NULL=All local logins use this remote login account
				--	otherwise local login UserName being set up (repeat for each user, as required)
--	, @locallogin = 'MyLocalLogin'
-- Following parameters ONLY required IF @UseSelf='FALSE' (above)
	, @rmtuser = 'MyRemoteLogin'		-- UserName on Remote corresponding to this @LocalLogin.   
	, @rmtpassword = 'MyRemotePassword'	-- Ditto: password