question

Tim avatar image
Tim 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.
sql-server-2005ssisexcel
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image
Håkan Winther answered
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
9 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image Pavel Pawlowski commented ·
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.
1 Like 1 ·
Tim avatar image Tim commented ·
Is that a separate install or a switch to type in when launching BIDS?
0 Likes 0 ·
Tim avatar image Tim commented ·
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.
0 Likes 0 ·
Håkan Winther avatar image Håkan Winther commented ·
As a matter of fact, they have, but only for office 2010 (64bit)
0 Likes 0 ·
WilliamD avatar image WilliamD commented ·
Only about 6 years too late. I hated SSIS development with SQL 2k5. Finally clean 64 bit SQL Server etc. but oh no, you can't run native exports to excel..... ARGGHHHH!!!
0 Likes 0 ·
Tim avatar image Tim commented ·
It is making it a hard sell at my shop saying we need to migrate to 2008/2005 when we can't do easy extracts to Excel. CSV, sure no problem. Oh well, I burned several hours struggling and now know how to do it so I can move forward. Again SSC saves the day for me.
0 Likes 0 ·
Håkan Winther avatar image Håkan Winther commented ·
Sec is an excellent source for answers! :)
0 Likes 0 ·
Noonies avatar image Noonies commented ·
I also had this problem and after researching for many hours I found the only work around that resolved my issue was extracting to CSV and then it worked! UGH!
0 Likes 0 ·
Tim avatar image Tim commented ·
In my early testing I also did the CSV thing to prove my data sets were ok, but I was bound and determined to get into in Excel.
0 Likes 0 ·
Andy Leonard avatar image
Andy Leonard answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.