x

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.
more ▼

asked Feb 15, 2011 at 12:45 PM in Default

Tim gravatar image

Tim
36.4k 38 41 139

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

answered Feb 15, 2011 at 01:07 PM

Håkan Winther gravatar image

Håkan Winther
15.7k 35 37 48

Is that a separate install or a switch to type in when launching BIDS?
Feb 15, 2011 at 01:11 PM Tim
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.
Feb 15, 2011 at 01:29 PM Tim
As a matter of fact, they have, but only for office 2010 (64bit)
Feb 15, 2011 at 01:32 PM Håkan Winther
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!!!
Feb 15, 2011 at 01:43 PM WilliamD
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.
Feb 15, 2011 at 10:00 PM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left

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

answered Feb 25, 2011 at 02:51 AM

Andy Leonard gravatar image

Andy Leonard
332 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1951
x943
x117

asked: Feb 15, 2011 at 12:45 PM

Seen: 2664 times

Last Updated: Feb 15, 2011 at 12:45 PM