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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics