asked
SSIS Excel Destination
I have an issue where I can build an SSIS create my OLE DB Source, do a Data Conversion, and then chose Excel Destination. I am not using any variables or anything fancy, just a simple dump of everything in a table to an Excel file. (Will ultimately use a query instead of the staging table) Here is where the issues come in. If I execute the package within BIDS I get error "CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. If I save the SSIS Package to a file and then open with "Execute Package Utility" and execute the package then it runs fine. Google and BING searches have brought up issues with Oracle/SQL stuff but those are not applicable to me. I did see a blurb about x64 jet driver issues with Excel. That might apply as I am running on a Windows 2008 x64 OS with SQL Server 2005 x64) Has anyone else encountered this before. SSIS is not my strong suite but I am gaining more knowledge of it everyday. It is a very powerful tool and I want to get over my fears about it and really start using it more.
It probably depends on the 64 bit platform. There are no 64 bit driver for jet and you need to use the 32 bit version of ssis. you can use 32-bit SSIS even on 64-bit server. It is already installed when you installed 64-bit version, and all you need to do is run the 32-bit DTEXEC.EXE - the one installed `Program Files (x86)\Microsoft Sql Server\90\Dts\Binn` (replace 90 with 10 if you are using SSIS 2008). I also heard some people copying the 32 bit file to the 64 bit folder, but the correct solution would be to add the 32 bit folder to the path in environment setting in windows. If you want to execute it with SQL agent there is a setting for 32 bit
Only to mention, the switch to run 32 bit version in the agent job is only available on SQL 2008. On SQL 2005 you have to use a an Operatin System (cmdExec) step type and execute the dtexec from the path Håkan provided. On SQL 2008 and above you simply check use 32bit runtime in the Execution option of the step.
Thanks @hakan Winther. My issue wasn't running the package in a schedule job it was while in BIDS. In researching 32bit DTEXEC I found an article stating to click on project, chose my package properties, click debugging and change the "Run64BitRuntime" to False. This only applies during the designing of the package. When scheduling the job I will need to follow your advise above as well. Why hasn't MS created a driver for Excel in 64bit.
As a matter of fact, they have, but only for office 2010 (64bit)
Right-click the Project name in Solution Explorer and select Properties. Click the Debugging page and set the Run64BitRuntime property to False. The package should now run in BIDS and allow you to debug. Hope this helps, Andy
