How do I fix an error when transferring data between SQL Server and MySql?
I have an SSIS package that connects to a mysql server and tries to fetch data from different tables and insert rows into a SQL Server 2005 database.
One of the problems I notice is that at any given time it starts no matter what step it runs in, it almost always does not write to the shared record from mysql to sql server.
there were no mistakes.
One morning he will have all 11M records and on the next run somewhere between 3K and 17K records.
Anyone notices anything like this?
source to share
I am importing data from two separate MySQL databases - one over the internet and one of them. I've never had this type of problem. Can you describe how to set up your MySQL database connection? I used the ODBC driver available from the MySQL website and connected using an ADO.NET datasource in my data stream that references an ODBC connection.
One possible way to at least prevent the download of incomplete data is to download only new records. If the original table has an ID and the records never change after they are inserted, you can specify the maximum ID by checking your database first.
Another possible way to prevent incomplete data loading is to load the MySQL database into a staging table on the destination server and then only load the unloaded records.
Another way to do this is to load data into a staging table, check that the records are greater than some minimum threshold such as the number of rows of the target table or the expected minimum number of transactions per day, and then only commit changes after this check. If the line is not enough, then raise the error in the batch and send a notification email. The benefit of raising the error is that you can tell the SQL Server Agent job to repeat this step for a specified number of times to see if that fixes the problem.
We hope these tips help, even if they don't directly address the root cause of your problem.
source to share