I have an SSIS package that connects to a mysql server and attempts to pulls data from different tables and inserts the rows into a SQL Server 2005 database.
One issue i notice is that at any given time it runs, regardless of what step it is on, it almost always fails to bring in the total records from mysql into sql server.
there are no errors thrown.
One morning it will have all 11M records and on the next run anywhere between 3K and 17K records.
Anyone notice anything like this?
I import data from two separate MySQL databases -- one over the Internet and one in-house. I've never had this type of issue. Can you describe how you setup your connection to the MySQL database? I used the ODBC driver available on the MySQL website and connect using an ADO.NET data source in my data flow that references the ODBC connection.
One possible way you could at least prevent yourself from loading incomplete data is only load new records. If the source table has an ID and the records never change once they are inserted, then you could feed in the maximum ID by checking your database first.
Another possible way to prevent loading incomplete data is loading the MySQL database into a staging table on your destination server and then only load records you haven't already loaded.
Yet another way to do it is load the data into a staging table, verify the records are greater than some minimum threshold such as the row count of the target table or the expected minimum number of transactions per day and then only commit the changes after this validation. If the rows are insufficent, then raise an error on the package and send a notification email. The advantage of raising an error is you can set your SQL Server Agent job to retry the step for a defined number of attempts to see if this resolves the issue.
Hope these tips help even if they don't directly address the root cause of your problem.
I've only tried MySQL -> SQL Server via SSIS once, but the error I found related to MySQL datetimes not converting to SQL Server datetimes. I would have thought this would break the whole dataflow, but depending on your configuration you could have set it purely to ignore bad rows?