July 15, 2010 at 1:51 pm
Hi,
For one of our systems we're working on a conversion plan to move from 32-bit SQL 2000 to 64-bit SQL 2008 (new server), and in testing I've ran into several problems with SSIS packages not working when moved to a 64-bit SQL 2008 server. The biggest problem we're facing are SSIS packages that need to connect to MS Access and Excel, and after reading this from MS - http://support.microsoft.com/kb/957570 - it looks like there's no support for OLE DB connections to Access or Excel in the 64-bit environment. The SSIS package works great when ran from my system running Windows XP (32-bit), but then errors with "SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64bit_ERROR" when run from the server.
I copied the package to the server and after changing the Debug property Run64BitRuntime to False I can run it from BIDS, but I can't find any simple way to schedule a job to run the dtsx package through SQL. My thought was I could change the Command Line option in the Job Step to run "c:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" with all the options, but thus far no combination I've used has worked. Is this the correct way to get it going?
Thanks for any suggestions...
Sam
July 15, 2010 at 2:02 pm
If you have another server available that is running a 32-bit OS, you could schedule jobs that require 32-bit ODBC drivers to run on that server.
July 15, 2010 at 2:32 pm
Michael.. I found a 'solution' that's working, but it may be worth setting-up a processing server just for such jobs since the solution isn't ideal.
My work around was to use DTExecUI to create the parameters to execute my SSIS package, then within the SQL Job create a "Operating System (CmdExec)" step with "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec.exe" plus the parameters created by DTExecUI.
Thus far the one SSIS package I'm testing is working with this, so it may be my best bet for now.
Take care --
Sam
July 15, 2010 at 9:27 pm
Sam,
Thats best approach,
Alaternatively, you can download 64bit oledb drivers for excel and ms access from msoft site, available from recently.
July 16, 2010 at 2:33 am
samalex (7/15/2010)
Michael.. I found a 'solution' that's working, but it may be worth setting-up a processing server just for such jobs since the solution isn't ideal.My work around was to use DTExecUI to create the parameters to execute my SSIS package, then within the SQL Job create a "Operating System (CmdExec)" step with "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec.exe" plus the parameters created by DTExecUI.
Thus far the one SSIS package I'm testing is working with this, so it may be my best bet for now.
Take care --
Sam
That is the solution mostly used in SQL 2005 to execute SSIS-packages in 32-bit mode.
However, in SQL 2008, you can just select an option in the SSIS-job stept that tells the package to run in 32-bit mode. Very easy, and you don't need to set-up another server. Just make a distinction between what can run in 64-bit jobsteps and what can run in 32-bit jobsteps.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 16, 2010 at 3:58 pm
da-zero (7/16/2010)
That is the solution mostly used in SQL 2005 to execute SSIS-packages in 32-bit mode.
However, in SQL 2008, you can just select an option in the SSIS-job stept that tells the package to run in 32-bit mode. Very easy, and you don't need to set-up another server. Just make a distinction between what can run in 64-bit jobsteps and what can run in 32-bit jobsteps.
Awesome... I spent lots of time searching Microsoft's site for the solution, and the one I posted was all I could find. Though it seems obvious now, I wish MS would've documented this change in SQL 2008 alittle better because none of the MS support/KB sites I searched when plugging in the error gave this as a solution.
Thanks --
Sam
July 16, 2010 at 8:55 pm
It would be better if MS made the realization that folks will still need to I/O with MS Office products even on 64 bit machines. As a side bar, losing this capability or having to make any special settings in 64 bit at all is just stupid. Microsoft... please get your act and your software together. It shouldn't take a forum of people to figure this stuff out.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply