August 20, 2008 at 5:25 pm
I have an ETL which is getting the data from a MySQL table to a SQL Server db.
First I truncate the SQL server table, than I select the data from MySQL and put it into SQL Server destination table.
When I am looking at the table in SQL Server I cannot see the modifications made to a row in MySQL.When I am looking in MySQL, I can see the modified row.
I checked the ETL and the mappings, connections to the servers are OK. The datareader for MySQL source has only a SELECT statement.
Does anybody know what would be the problem?
Is it the SQL Server or the MySQL one?
August 21, 2008 at 5:35 am
Just a few thoughts, maybe some too obvious... but:
Have you scheduled the package to run at regular intervals?
Have you verified the truncation on the SQL SVR side is actually happening?
Are you allowing Identity inserts, if any Identity columns exist in the destination?
What version of SQL Server are you using, and what type of connection for the MySQL connector?
Are you getting data in the destination at all, or does it remain empty?
August 21, 2008 at 8:46 am
Thank you for your reply.
These are good questions!
I did check the truncation and it is happening.
The MYSQL is 5.0 and SQL Server is 2005 version.
There is no identity column.
The package is running every 10 minutes, but after 1 hour i could not see any change made in MySQL.
I use the MYSQL connector 5.1 (ODBC) Checked it, it works OK.
Any other ideas?
Thank you.
Dan
August 21, 2008 at 9:24 am
OK, a few more thoughts:
Have you checked the package execution history for any possible messages or package failures?
Packages that are tested by a user manually hiting the "go" button may be configured run under current user rights, whereas the SQL Server account that is executing the schedules may not have the same rights to paths and databases. We resolved this with creating a domain account that has the appropriate permissions to run our packages, and using the "Run As" property on the package scheduling setup.
What user are you scheduling the package as?
Does that user have rights to both systems, either via package vars or System DSNs on the sources?
There is a newer version of the MySQL .NET adapter (v5.2.x). During testing we found issues with the early 5.0.x versions, and went back to the ODBC route for v3.51... but now there is a newer one for that also. We have not tested 5.1 or 5.2 series yet, but I have read a bit that 5.2 is supposedly the version to look at (finally). I'll get 'round that that... sometime.
August 21, 2008 at 9:27 am
P.S. The fact that you see no changes tells me one thing for sure: The truncation and new inserts are NOT happening as you thought.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply