Developed Packages in 32Bit and tryin to run on 64 Bit - Error on aquiring Excel connection manager
Hi Friends I have developed couple of SSIS packages on my system (32 Bit) and moved them to Test which is 64 Bit. I have created a job for these packages to run. When I run it gives me following error
no OLE DB provider is available. End Error Error: 2011-01-12 13:13:01.04 Code: 0xC020801C Source: Unapid vouchers Excel Destination [42] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.
The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC00F9304. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End
Error Error: 2011-01-12 13:13:01.04 Code: 0xC0047017 Source: Unapid vouchers SSIS.Pipeline Description: component "Excel Destination" (42) failed validation and returned error code
0xC020801C. End Error Error: 2011-01-12 13:13:01.04 Code: 0xC004700C Source: Unapid vouchers SSIS.Pipeline Description: One or more component failed validation. End Error
Error: 2011-01-12 13:13:01.04 Code: 0xC0024107 Source: Unapid vouchers Description: There were errors during task validation. End Error DTExec: The package execution
returned DTSER_FAILURE (1). Started: 1:13:00 PM Finished: 1:13:01 PM Elapsed: 0.468 seconds. The package execution failed. The step failed.
Any help is appreciated!!
Thanks a Bunch!!
Gopal
Gopal
January 12th, 2011 3:19pm
Hello Gopal:
Do you run it using DTExec? Of so you may want to specify the x86 switch on the target machine.
Like this: /x86Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
January 12th, 2011 3:30pm
It is setup as a Job on target. I am not sure how I can do the switch...!Gopal
January 12th, 2011 3:38pm
The SQL Server Agent job you meant? In this case it still uses the DTExec, so since the Excel driver has a limitation the package needs to run in 32 bit mode on a 64 bit machine you do need this setting applied.
For a SQL Agent job setting you need to revise the settings.
Please refer to this MSDN article:
http://msdn.microsoft.com/en-us/library/ms138023.aspx
down there p.6 describes the necessary step.
Found a picture for you:
http://www.sql-server-performance.com/admin/article_images_new/2008_images/cannot_execute_ssis_package_files/image001.jpg
Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
January 12th, 2011 3:43pm
Hi I have already made changes to the job to run in 32 bit mode... but I dont know how to use the X86 switch from a SQL SERVER JOB.Gopal
January 12th, 2011 3:56pm
What you have done is enough. Did you try running it now?Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
January 12th, 2011 4:01pm
Gopal,
Have you tried this?
1. open your package from BIDS
2. Go to Project
3. Go to you project properties
4. Under Configuration properties, click on Debugging, and set Run64bitRuntime to 'True'
5. Save your package and run.
hth,
Rok
January 12th, 2011 4:06pm
I tired to edit the project properties as well the sql job properties... neither of them worked yeilding same error.
I even tried to create a new job just in case!!!!!
Gopal
Free Windows Admin Tool Kit Click here and download it now
January 12th, 2011 5:09pm
ArthurZ,
I have tried running it.. still gives the same error.Gopal
January 12th, 2011 6:35pm
The Run64bitRuntime is just that, onlythe run time in BIDS so that setting does not go with the package. Do you have the Excel objects on the server? If running the job in 32bit mode (which is correct and needs to be set either in the job
or executed from the DTExec located in the program files (x86) SQL Server directory (not the 64 bit binary), then I'm guessing the provider simply isn't there.
Todd McDermid has an excellent resource on this here
http://toddmcdermid.blogspot.com/2009/10/quick-reference-ssis-in-32-and-64-bits.html
Take some time to run through it and also note the links at the bottom of the article.Ted Krueger
Blog on lessthandot.com @onpnt on twitter
Please click the Mark as Answer button if a post solves your problem!
Free Windows Admin Tool Kit Click here and download it now
January 12th, 2011 6:55pm
"C:\Program Files (X86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe"/D["CAP's Paid Date Update in Solar.dtsx"]/De[envsys]
Will this command line argument right?
when ever I run this it keeps giving me an error saying
At least one of the DTS, SQL, or File options must be specified. Process Exit Code 6. The step failed.Gopal
January 13th, 2011 9:58am
"C:\Program Files (X86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe"/D["CAP's Paid Date Update in Solar.dtsx"]/De[envsys]
okay I fixed this command to
"C:\Program Files (X86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /D"CAP's Paid Date Update in Solar.dtsx" /De envsys
Now it start giving me the same old error that it cannot aquire the excel connnection
Executed as user: xxxxxx\yyyyyy. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 8:03:08 AM Progress: 2011-01-13
08:03:10.10 Source: Unapid vouchers Validating: 0% complete End Progress Progress: 2011-01-13 08:03:10.15 Source: Unapid vouchers Validating:
33% complete End Progress Progress: 2011-01-13 08:03:10.15 Source: Unapid vouchers Validating: 66% complete End Progress Error: 2011-01-13 08:03:10.42 Code:
0xC0209302 Source: CAP S Unpaid Vouchers Connection manager "Excel Connection Manager" Description: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR. The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is
not registered. Error code: 0x00000000. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error Error: 2011-01-13 08:03:10.42
Code: 0xC020801C Source: Unapid vouchers Excel Destination [42] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager
"Excel Connection Manager" failed with error code 0xC0209302. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2011-01-13 08:03:10.42
Code: 0xC0047017 Source: Unapid vouchers SSIS.Pipeline Description: component "Excel Destination" (42) failed validation and returned error code 0xC020801C. End Error Progress: 2011-01-13 08:03:10.42
Source: Unapid vouchers Validating: 100% complete End Progress Error: 2011-01-13 08:03:10.42 Code: 0xC004700C Source: Unapid vouchers SSIS.Pipeline
Description: One or more component failed validation. End Error Error: 2011-01-13 08:03:10.42 Code: 0xC0024107 Source: Unapid vouchers Description: There were errors
during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:03:08 AM Finished: 8:03:10 AM Elapsed: 1.5 seconds. Process Exit Code 1. The step failed
ANY HELP IS REALLY APPRECIATED!!!!!
Gopal
Free Windows Admin Tool Kit Click here and download it now
January 13th, 2011 10:11am
No.
It should be like:
"C:\Program Files (X86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /D "CAP""'s Paid Date Update in Solar.dtsx" /De nvsys
/X86
I added the x86 switch to instruct the package to run in 32 bit mode.Arthur My Blog
January 13th, 2011 10:14am
Yupp I tried that toooo!! it did not work... Giving following error
Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 8:56:23 AM Progress: 2011-01-13 08:56:25.13
Source: Unapid vouchers Validating: 0% complete End Progress Progress: 2011-01-13 08:56:25.20 Source: Unapid vouchers Validating: 33% complete End Progress
Progress: 2011-01-13 08:56:25.20 Source: Unapid vouchers Validating: 66% complete End Progress Error: 2011-01-13 08:56:25.47 Code: 0xC0209302
Source: CAP S Unpaid Vouchers Connection manager "Excel Connection Manager" Description: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR. The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. Error code: 0x00000000.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error Error: 2011-01-13 08:56:25.47 Code: 0xC020801C
Source: Unapid vouchers Excel Destination [42] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with
error code 0xC0209302. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2011-01-13 08:56:25.47 Code: 0xC0047017
Source: Unapid vouchers SSIS.Pipeline Description: component "Excel Destination" (42) failed validation and returned error code 0xC020801C. End Error Progress: 2011-01-13 08:56:25.47 Source: Unapid
vouchers Validating: 100% complete End Progress Error: 2011-01-13 08:56:25.47 Code: 0xC004700C Source: Unapid vouchers SSIS.Pipeline Description:
One or more component failed validation. End Error Error: 2011-01-13 08:56:25.47 Code: 0xC0024107 Source: Unapid vouchers Description: There were errors during task validation.
End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:56:23 AM Finished: 8:56:25 AM Elapsed: 1.547 seconds. Process Exit Code 1. The step failed.
Gopal
Free Windows Admin Tool Kit Click here and download it now
January 13th, 2011 10:59am
Seems this is because the driver for Microsoft.ACE.OLEDB.12.0 is not installed.Arthur My Blog
January 13th, 2011 11:05am
How can I install tht?Gopal
Free Windows Admin Tool Kit Click here and download it now
January 13th, 2011 11:25am
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en
gets you too the Download file...!!! requested permission to install it on the test server...!! willl let you guys know what had happened!!!Gopal
January 13th, 2011 11:39am
Sorry for the delay, good, you are on the right track!Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
January 13th, 2011 11:49am
Hi Friends, I thank you all for the support.. And Yes it worked as I had to install Microsoft.ACE.OLEDB.12.0 driver as referred by Arthur.!!
Thanks A TON!!!!!!!!Gopal
January 13th, 2011 12:04pm