02 Migrate data from SQL Server to MySQL using SSIS

[@swapnilvmestry]

I'm facing issue columns cannot convert between unicode to non unicode strings Please help me on this

[@learnssis]

Use a Data Conversion transformation and convert to appropriate type according to the destination data type you are trying to insert to.

[@pradnyakasar614]

Test connection failed because of an error in initializing provider. ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified....how to resolve that?

[@pradnyakasar614]

when I am going in OLE db source and in New option there will not any option means in data connection ,there is not showing any option ,how to resolve that?

[@learnssis]

Please watch this video carefully and repeat the same steps as shown in the video. The only change will be that your source and destination database and tables will be different.

[@sameershaikh-fn3uc]

How can i migrate a whole database which contains tables, stored procedure and views i tried with mysql work bench but sp didn't migrate and i founded mis match rows count in between 2 server

[@learnssis]

Take a look at this video kzbin.info/www/bejne/jKXLiKioo69mfs0

[@TestVM-fw2uo]

Please guide me on know how to execute the MySQL statement from MySQL DB (not the SQL server) using Execute SQL task? I have tried using ODBC connection but I am getting an error "[Execute SQL Task] Error: Failed to acquire connection "MySQLDB-32". Connection may not be configured correctly or you may not have the right permissions on this connection.

[@PawanKumarSingh-gi2yj]

@learn ssis, can we populate scd data from sql server to mysql if yes, pleaes let me know. actually I am facing issues and unable to do so. Pleaes help if possible.

[@faizanmajid2922]

Great video sir. Thank you for teaching us these things via these videos.

[@learnssis]

You are most welcome Faizan.

[@ah_77_88]

thank you so match. whats change if i want to make migration from oracle to mysql ?

[@learnssis]

In that scenario your destination will be same but source will change and for fetching the data from Oracle you would need to install ODBC drivers for oracle and need to create the User DSN on your machine and then try to fetch data from it.

[@ah_77_88]

​@@learnssis​ ok i did that so then in ssis, in the oracle source what i should write instead of this : DRIVER={MySQL ODBC 8.0 ANSI Driver};SERVER=localhost;DATABASE=test;UID=root;Password=mysql123

[@learnssis]

@@ah_77_88You would need to create the User DSN in ODBC connection managers for that and then from the ODBC source, you would need to select the user DSN that you created. You would need to probably find any video or tutorial on setting up the User DSN for Oracle. You can check below video for reference, I have myself not done it before and don't have a video on this topic kzbin.info/www/bejne/Z2TNXq2cmrhso6M

[@quanweizhang4516]

When I tried to set up connection to mysql, it failed with error below. Any suggestions? Thanks Test connection failed because of an error in initializing provider. ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

[@learnssis]

Did you follow the steps shown in the video ? Have you downloaded and installed the ODBC drivers ? Are you using the connection string shown in this video which you copy from description of the video ?

[@mirzajafarov]

hI, but mysql database on linux server and i cant handle 4200 error because i cant find my.inf file. Can u help me with another video or something?

[@learnssis]

Sorry I never worked on linux and don't have idea how we can fix it there.

[@hoangminhninh9133]

I encounter performance issue when inserting into MySQL, 30 records/sec, the table is new, I tried other DB destinations like SQL Server, Postgress, it's fine. Did you have the same issue

[@learnssis]

Are you using the Bulk insert option ? I normally don't work much with MySQL. Majority of time I use OLD DB destination to insert the data to SQL Server and it works pretty good.

[@pradnyakasar614]

My 1 table containing data upto 10Lakh ,how to migrate from sql server to mysql?

[@learnssis]

Take a look at this video, you can transfer any number of records. kzbin.info/www/bejne/n4SwZaeKpcmMnLs

[@pradnyakasar614]

@@learnssis In my sql server ,I am not able to found where is the SSIS ,I was installed it

[@learnssis]

@@pradnyakasar614 Which SQL Server version currently are you using ?

[@learnssis]

@@pradnyakasar614 You can see here how to install SSIS inside Visual Studio 2019 kzbin.info/www/bejne/Zn_Sq3yMpLOsj5Y

[@pradnyakasar614]

@@learnssis It's sql server 2011

[@mundodaisa5866]

Very nice! I need to migrate around 20 tables from, which is the better way to do it?

[@learnssis]

If you have SSIS then I would recommend to use SSIS to migrate the 20 tables.

[@mundodaisa5866]

@@learnssis thank you very much. I have Siss and using your video I migrate one table. How I should to migrate all? One by one?

[@learnssis]

@@mundodaisa5866 If the structure of the table is different, then you would need to create 20 Data flow task to migrate 20 tables.

[@mundodaisa5866]

​@@learnssisThank you once again. The tables have differents structures, so I will create differents flow tasks. Sorry bothering you, but I am new at SISS. Can I create a flow between the data flow in order to execute only once and garanty the order of the migration?

[@learnssis]

@@mundodaisa5866 Sorry I did not get your question ?

[@ipelengmekgwe834]

The problem is the ANSI drivers. You don't need to update the my.ini file, you need to use the Unicode drivers, Driver={MySQL ODBC 8.4 Unicode Driver}

[@learnssis]

Thank you for providing the valuable information.

[@swapnilvmestry]

Good one 👍

[@learnssis]

Thank you Swapnil.

[@mahbuburrahmanopu5634]

Thanks bro! ❤❤

[@learnssis]

You are most welcome.

[@ChokoDemon]

thanks a lot

[@learnssis]

Thank you so much Choko.

[@diegocastro7433]

ty my friend!

[@learnssis]

Thank you Diego.

[@balamuruganpjb9085]

Guys, if you are facing the driver error while selecting the tables - use select queries to fetch data.

[@learnssis]

Thanks for sharing this solution.

[@algenius._]

Thanks!

[@learnssis]

You are welcome.

[@KrishnaYadav-go2wy]

great job

[@learnssis]

Thank you Krishna Yadav.

[@KrishnaYadav-go2wy]

@@learnssis mention not!! can u provide a video on how to migrate data from oracle to SQL server?

[@learnssis]

@@KrishnaYadav-go2wy I will need to install Oracle on the machine to test it. Maybe will try to make a video in future on this one.

[@ISMAILELGHARNOUGUI]

Thank youu

[@learnssis]

You are most welcome Sir.

[@ISMAILELGHARNOUGUI]

@@learnssis in ssis i used database Mysql workbench i use ado net source and desctination but how i can do the query of update also in the fact table i have the component lookup for mutch id but he does'nt work with mysql , he work only with ole db .if you have a solution help me please

[@ericsos101]

This is very informative video. Great job.I have always get confused with the implementation between SQL SERVER and MySQL. You have cleared me some doubts. Awesome project.. So i would like to know how can i populate different ODS table from one single table of another database? ok like i have created a ODS Layer(a database i named Prospect_ODS) and created 3 different tables so i would love now to populate these 3 different tables from another single table that belong in another database i named Prospect_DW. How can i do it using SSIS and loading it without duplicate data? thank you Aqil and i will be much appreciate for a little help .. Thanks in advance!!

[@learnssis]

Glad to know that it helped you.

[@learnssis]

If you want to load 3 tables from a single table, then you can simply create a source connection where you can pull the data from source and then use a destination connection to insert the data into 3 tables. I don't see any problem with it. Either you can use a single data flow task to insert the data into 3 destination table or you can create 3 dft to load the data into 3 destination tables. While loading the data into destination table, may be you can use Lookup transformation to check if data is already exists in destination table.

[@ericsos101]

@@learnssis ok so lookup transformation will allow me to prevent duplicate data while loading from table db to another table dB?

[@ericsos101]

@@learnssis actually I want to load one single table from a database to 3 different tables of another database .. then while loading it I need to make sure there’s no duplicate data during loading

[@learnssis]

@@ericsos101 Yes, using looking we can identify if a record already exists in destination or not, so we will only insert Lookup non matched records into destination table.

[@pradnyakasar614]

test connection failed because of an error in initializing provider connection string property has been not initialized,I am getting this error