in sql server it's possible link mysql server mssql , query using ssms example. want try , use ssis transformations , store data on mysql database.
\i read there several ways link mysql mssql server. ole db, mysql odbc etc etc.
2 questions:
are there limitations might run when use combination of ssis , mysql instead of mssql?
when link mysql database mssql , write query in ssms, write queries in mysql language or mssql language. example difference in top , limit
i have worked linked mysql server sql server in past , ran issues.
querying mysql ssms (sql server)
once have created linked server imagine should able use four-part name , query tables in mysql doesnt allow you. example cannot like...
select * mysqlserver.dbname.schema.tablename for reason throws error. question whether can use t-sql in ssms query linked mysql server? nope, unfortunately not.
but alternatively microsoft recommends using openquery execute queries linked server.
when using openquery, sql server not try parse query, sends linked server is. means can/should able write mysql in ssms using openquery , work.
using ssis mysql
even though ssis microsoft's tool comes sql server proper etl tool can read data multiple sources , send data many types of destination.
once have used appropriate driver connect mysql , ported data in ssis package , not relevant anymore, data came from? have access ssis tools , should able use them if data coming flat file, sql server or excel sheet etc.
