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
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