question on deploy package to production server

Question

I have etl task that will extract data from Mysql database and load into sqlserver. I created ssis package to perform this job on my local machine. In the source, i used ADO.net with mysql driver. everything works perfectly. but when i move the package file,the dtsx file, to the server which is a 64bit windows 2008 box and execute it, I got sqlserver.dts.runtime.dtscouldnotcreatemanagedconnectionexception. But if i move the whole project over, and open the project with BIDS on the server, I saw that in the connection manager for mysql i lost mysql driver setting, it default to sqlserver driver. i have to reset it to the correct driver on the server in order to run. I have tried to use configuration file as well, it doesnt work either. thanks

Answer 1

Does your package have to be mysql?

Answer 2

The BIDS environment is 32-bit, and will use the 32-bit drivers when you are setting up the package. When you run it on a 64-bit machine, it defaults to the 64-bit drivers. It sounds like you don't have the 64-bit driver installed on the server. You could get the 64-bit drivers installed, or run the package use the 32-bit version of DTEXEC, located in "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn".