SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using SSIS to export data from a 64 bit server to Excel 2007


Using SSIS to export data from a 64 bit server to Excel 2007

Author
Message
gkolson
gkolson
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 120
Comments posted to this topic are about the item Using SSIS to export data from a 64 bit server to Excel 2007
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)

Group: General Forum Members
Points: 109526 Visits: 22280
As far as I know, the format of files generated by either the 32-bit version or the 64-bit version of Excel is exactly the same - so referring to '32-bit files' is a little misleading. It's the method of connecting to the file that needs to be 32-bit.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
x_t_r
x_t_r
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 160
I had same experience as Krista Olson. You need 32-bit Microsoft Access Database engine 2010 to get it work. Not from the reason that excel files are 32-bit or 64-bit, the problem is in OS which is not 64-bit completely. For example if you schedule the job in Agent - Agent is running as a 32-bit process and cannot run any 64-bit dtsx package.
Daniel Fountain
Daniel Fountain
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2307 Visits: 890
This is misleading


When done creating and testing the SSIS package on your workstation, be sure to change in Solution Explorer the Debugging properties back to Run64BitRuntime to true prior to running or scheduling it to run from a 64 bit server.


As:

"The Run64BitRuntime project property applies only at design time."

http://msdn.microsoft.com/en-us/library/ms141766(v=sql.105).aspx
inmallinath
inmallinath
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 42
I had tried this a while ago. This messes up the use of office applications.

When you launch any of the office applications, it starts to install the software.

Am I the only one? Has anyone experienced this?
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)

Group: General Forum Members
Points: 109526 Visits: 22280
inmallinath (12/4/2012)
I had tried this a while ago. This messes up the use of office applications.

When you launch any of the office applications, it starts to install the software.

Am I the only one? Has anyone experienced this?


I don't remember ever having this problem.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
rburko
rburko
Say Hey Kid
Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)

Group: General Forum Members
Points: 669 Visits: 365
What timing! I just went through the agony of getting the Excel connection to work on a 64 bit machine when running a SSIS package via SQL Server agent. My solution was probably the same as previously recommended by others: Run the package as Operating System (CmdExec), point to the 32 bit DTExec.exe in the command line.

Does the method outlined by Kirsta also work for Excel 2003?
Mile Higher Than Sea Level
Mile Higher Than Sea Level
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2176 Visits: 465
Really appreciate the article, hope SQL Server Central will consider many more articles like this one.
Could SQL Server Central please create a major category for SQL / Excel with the sub folders?
Excellent article, that only scratches the surface.

As a consultant to an international organization, I provide Excel 2010 reports that include Excel Object programming distributed with Citrix.
The use of SQL Server, Excel Objects, and Access 2010 run-time is extremely powerful for custom real-time analysis.
When groups of engineers, accounting, and compliance meet, they can perform "what-if" decision processes dynamically with Excel, a tool they know.
Rich User Interfaces such as Excel instead of HTML post increases worker efficiency. Data-Mining SQL Server with Excel is very powerful.
Users provided with selected views of Excel can work with the results more efficiently and cost effectively.
The speed of a seasoned Excel Object Model Programmer to deliver complex Business-Rule-Based decision tools over SharePoint is another consideration.

SharePoint and SSRS are very efficient for high-volume standard Internet-wide uses.
There is still a need for small-business or business units to utilize rich interface tools such as Excel for critical and timely business rule based decision process.
ahperez
ahperez
SSChasing Mays
SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)

Group: General Forum Members
Points: 636 Visits: 635
Great job Krista, I appreciate you taking the time to write this up as an article. Excel with and without PowerPivot connections is a familiar and flexible tool for my end users. I find that we are using the Report Manager (SSRS) less these days and relying more on direct connections between our rendering db and Excel. I had also cobbled together a similar solution by trial and error since upgrading to SQL 2008 R2 a couple of months ago.
Thanks sharing what you discovered with others, you have probably saved serveral readers out there a great deal of frustration.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)

Group: General Forum Members
Points: 124327 Visits: 13344
There are some flaws in this article:

* Run64BitRuntime is a design-time debugging property, which has nothing to do with packages running on a server.
* Why would you want to install the x86 ACE OLE DB provider on your server? As Phil said, there's no difference between an Excel file created by 32-bit Excel or 64-bit Excel.

The only thing this article describes is how you can create the package, because BIDS is a 32-bit application.

For example if you schedule the job in Agent - Agent is running as a 32-bit process and cannot run any 64-bit dtsx package.


Agent is not a 32-bit process if SQL Server is 64-bit. If you do have a 64-bit SQL Server, SSIS packages scheduled with Agent run in 64-bit, unless you explicitly specify that 32-bit has to be used.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search