I am experiencing an issue with an SSIS job that connects to a Firebird DB using their ODBC driver (version 2.0.4.155).
- The SSIS package runs flawlessly in Visual Studio 2008, which is installed on Windows 7 32-bit (don't ask!)
- The package fails to execute when deployed to SSIS in SQL Server 2008 64-bit. The error:
Data source name not found and no default driver specified.
This immediately made me think that a ODBC driver mismatch might be the issue, where SSIS is looking for the 32-bit drivers. So here is what I did:
- Uninstalled the Firebird ODBC 64-bit drivers
- Installed the Firebird ODBC 32-bit drivers
- Modified the Job schedule in SQL Server so it executes in 32-vbit
- Package runs successfully after that -- however there is a twist.
I will be able to execute the package again as long as I am still logged in to the SQL server (I logged in to install the Firebird drivers). I can execute it even from a remove SQL Server Management Studio connection -- but as long as I do not log off from the server.
If I log off from the server, then the SSIs job will no longer work (same error as before). This led me to think that this is not in fact a 32/64 bit mismatch, but perhaps some registry or environment variable is not being committed after I log off from thee server due to insufficient permissions (even though I was meant to be admin on that server).
So for my next test:
- I uninstalled the 32-bit driver
- Reinstalled the 64-bit driver
- Remained logged into the SQL server
- Executed the job time and again from a remote SQL Server Management Studio: It worked every time
As soon as I logged off from that SQL server, the behavior occurred again. This was hard to troubleshoot at first because the drive installs without throwing any errors and it even works at first.
But I am confident now that this has to do with permissions. Any ideas what I might be missing?