[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Upgrade to SQL Server 2008 having trouble with SSIS connections

Posted on 2010-09-14
23
Medium Priority
?
1,107 Views
Last Modified: 2013-11-10
I am getting this error in oneof my SSIS packages that was built in VS 2005 and I have upgraded to 2008 and now having trouble with some packages...

[Connection manager "Excel Connection Manager 2"] Error: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.

Any ideas?
0
Comment
Question by:johnnyaction
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 9
  • 2
  • +1
23 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33672677
Since 64 bit Excel drivers are not available, you need to execute your procedure in 32 bit mode to get it work.
Detailed info below

http://sqlserver.posterous.com/excel-connections-in-ssis-64-b
0
 
LVL 1

Author Comment

by:johnnyaction
ID: 33673066
ok I seemed to get by that but now Im getting an access denied on my C:\ for one of my file system tasks? Could that be related? FYI...I am only getting the error when I run the sql job that calls the packages via steps. I can run it in BIDS
0
 
LVL 1

Author Comment

by:johnnyaction
ID: 33673412
I was reading about a checkbox on the job step portion of a sql job where you can select to run in 32 bit mode? I cant seem to find it though? Any ideas?
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 30

Expert Comment

by:Reza Rad
ID: 33673544
Is this problem only exists when you run ssis package as job?
or you get it when you run it from BIDS either?
0
 
LVL 1

Author Comment

by:johnnyaction
ID: 33673560
I can run the packages individually but not from a sql job?
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 33673596
did you deployed packages on same machine which you tested package on it with BIDS? or this is different machine which runs job under it?
0
 
LVL 1

Author Comment

by:johnnyaction
ID: 33673627
I upgraded to a new sql server 2008 box. I took all the jobs that were running under the old sql 05 server and I am now trying to run them on the new 08 box. I copied all the dtsx packages from the old server to the new and I copied all the jobs that call these dtsx packages and I am trying to run them from a sql job. Any thoughts?
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 33673919
if your new sql server 2008 box is 64 bit, try executing packages with DTEXEC 32 bit version.
you can find 32 bit DTEXEC here:

<drive>:\Program Files(x86)\Microsoft SQL Server\100\DTS\Binn

0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 33673928
0
 
LVL 1

Author Comment

by:johnnyaction
ID: 33674748
I put this in the command line of my jobs step

/FILE "E:\Microsoft SQL Server\SSIS\Data Warehouse SSIS\Data Warehouse SSIS\Generate Net Position List.dtsx"  /X86  /CHECKPOINTING OFF /REPORTING E

Still will not work. I added the /x86 I saw in an article this was what I should do. Still doesnt work though
0
 
LVL 1

Author Comment

by:johnnyaction
ID: 33674753
And I have made everything 64bit=False
0
 
LVL 30

Accepted Solution

by:
Reza Rad earned 2000 total points
ID: 33675375
To run a package in 32-bit mode from a 64-bit version of SQL Server Agent, select Use 32 bit runtime on the Execution options tab of the New Job Step dialog box.
0
 
LVL 1

Author Comment

by:johnnyaction
ID: 33675441
I have tried to find where that option is in my job step but I cannot find it. See attached
EE.docx
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 33675525
amazing,
my configuration is different.
look at attachment!

1.jpg
0
 
LVL 1

Author Comment

by:johnnyaction
ID: 33675581
Do I have to install a 32 bit environment somehow? I have seen some articles where they tell me to check that checkbox but I dont know how to make it appear?
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 33675607
is your sql server agent under 2008?
0
 
LVL 1

Author Comment

by:johnnyaction
ID: 33675743
I am assuming so. Its a SQL Server 2008 R2 installation. I did not have any service packs installed so Im installing SP1 now.
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 33675765
a workaround for this :
use the Operating System type of job step, and refer to the 32-bit version of DTExec specifically in the command line that you use, and manually specify arguments to DTExec
32-bit DTExec is here:
<drive>:\Program Files(x86)\Microsoft SQL Server\100\DTS\Binn

reference:
http://toddmcdermid.blogspot.com/2009/10/quick-reference-ssis-in-32-and-64-bits.html



0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33679174
Try this from command prompt:

"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec.exe" /f "E:\Microsoft SQL Server\SSIS\Data Warehouse SSIS\Data Warehouse SSIS\Generate Net Position List.dtsx" /X86
0
 
LVL 1

Author Comment

by:johnnyaction
ID: 33695412
Ive tried all suggestions but I am still having issues. Anything else you could recommend?
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 33695605
what is your ssis package consists?
do you used execute process task?
do you used execute package task?
do you used script task?

in any of above tasks you need to do changes to run under 32 bit. you need to read this great article of Todd carefully line by line:

http://toddmcdermid.blogspot.com/2009/10/quick-reference-ssis-in-32-and-64-bits.html


0
 
LVL 3

Expert Comment

by:novaspoonman
ID: 33695809
Reza is correct. This is definitely a 32/64-bit issue. There is no 64-bit provider for Excel.

Did you happen to install a 64-bit version of BIDS with a 32-bit version of SQL Server and SSIS? That would explain why the 32-bit runtime checkbox is missing.

0
 
LVL 1

Author Closing Comment

by:johnnyaction
ID: 33824946
thanks
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question