So, for the past few months a DBA and I were trying to figure out why all of our SSIS packages, connection strings and data sources all work fine on Server 2003, SQL 2005 and of course in dev, but not on our production SQL 2005 server. After extensive research (which was probably right in front of our faces the whole time) we stumbled over the following KB article:

KB957570

Which very briefly explains how the OLE DB and ODBC providers are only available on 32 bit systems, and our production SQL 2005 server is 64 bit. Since 64 bit performs so much better, and SQL is a resource hog to begin with, this seemed like the best solution. Then when we installed ILM 2007 (64 bit) and wanted to connect to the database (also 64 bit), and things kept failing, we’d beat our heads into the wall trying to figure out why. According to the KB article, there is no plan to support these providers in a 64 bit environment, and the best solution is to just emulate a 32 bit environment, such as run the application in 32 bit mode. Not that easy when you’re custom-writing interfaces, applications and queries. However, thanks to my co-worker’s friend he pointed out a way to run an SSIS package on a 64 bit server and make it think it’s 32 bit!

While creating the package in VS2005, go to Project->Project Properties. Look for the element called “RUN64BITRUNTIME” and change it to False. Then save and deploy the package as usual. Finally, you cannot execute the package from the UI or even from a SQL job. You’ll need to execute it from the command line using DTEXEC utility. To automate this, use a SQL job to call the DTEXEC utility command, either through xp_cmdshell or a batch job. More details can be found by reading this article from SQL Server 2008 Books Online.

So, there is a workaround to this apparent roadblock. The best part of all of this? There is allegedly support for these two providers in SQL 2008. We haven’t had a chance to test this yet, but that explains why they’re not supporting 64 bit versions in SQL 2005….just get SQL 2008!