February 25, 2009 at 11:41 am
Hi,
i am using Sql server 2005 with SP3 on Windows 2003 64-Bit as my production machine.. i have developed SSIS packages which needs to be scheduled using the SQL Server Agent. when i run the SSIS manually it works absolutely fine.. when scheduled on SQL Server Agent, it threw up error message as 'connection manager failure'.. i have tried almost all the possibilities some of them to list out are :
a)installed Office to get the Excel recognised by the machine. no go.
b)converted the excel file to csv. to run on the sql server agent.
d)tried bulk insert. ( when tried bulk insert with csv it works, i am looking to import data from excel file directly, instead)
e)tried running the Dtexec using the operating system command. no go.
when ran bulk insert, i get the error "microsoft jet oledb 4.0 has not been registered"
i have tried almost all the solutions provided on the net for no use..
i execute the package by double clicking on the package, it works fine...
I am wondering, if anyone is using sql server 2005, if they are using, aren't they importing the data using excel file or by any chance if anyone is using SQL Server Agent at all.....
i see so many blogs and forums on the net, where this question is unanswered or the thread is still open... what is microsoft taking to release the 64 bit driver compatible for 64bit os.. why is this adjustment of running the program file(x86)\..\..\..\dtexex.exe
please help..
Thanks
February 25, 2009 at 12:12 pm
This is generally simple task if Windows 2003 is SP2 because it adds the 32bits ODBC which you can use to connect to Excel/Access in WOW64 if you are moving the data to SQL Server. If you are not moving the data to SQL Server then you need a datasource more suited for your destination. If your destination is Sharepoint list then you need ADO.NET datasource. So check the thread below and read my side of the post because the OP left out very important facts when the thread was started.
http://www.sqlservercentral.com/Forums/Topic659426-149-2.aspx#bm659672
Kind regards,
Gift Peddie
February 26, 2009 at 4:11 am
Hi,
i tried the possible ways you suggested, very good information though, but, i was not able to solve the issue, i am still getting the same error message...
64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 4:42:16 AM Error: 2009-02-25 04:42:28.61 Code: 0xC0202009 Source: "Source Name" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error Error: 2009-02-25 04:42:28.61 Code: 0xC020801C Source: IMPORT PLAN Excel Source [2311] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Source Name" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2009... The package execution fa... The step failed.
Please advice
February 26, 2009 at 7:23 am
Your problems are not related to 64bits but rather source and destination of your package which you have not posted. There is no 64bits driver for Excel but where is the destination of your package? What are you doing in the package because your error is not related to platform.
Kind regards,
Gift Peddie
February 26, 2009 at 7:45 am
well, in that case, my Excel file is in a shared folder and i am the owner of the folder, if package is invoked by an account called ServiceWCP, i have added ServiceWCP with full access to that folder. i tried getting the excel file to the production machine, changed the source path to run the package, it works fine when ran manually, but when assinged a job on Sql Server Agent, it is acting weird...
i am trying the XP_Cmdshell, meanwhile, if you figure out any alternatives, kindly suggest...
Thanks
Deepak
February 26, 2009 at 7:55 am
Remove xp_cmdshell use use an Agent proxy account the Agent must be admin in SQL Server and on the network and in some cases the owner of the package must also be admin. Check the thread below for solution.
http://www.sqlservercentral.com/Forums/Topic661486-148-1.aspx
Kind regards,
Gift Peddie
March 3, 2009 at 12:40 am
Gift,
i tried all the solutions suggested by you, thank you so much for providing many ways of solving the issues, i am afraid, i am back to square 1 with the same issue... it's still not fixed, FYI.. i tried running on a 32bit environment (my Dev machine) it works without any hassles.. so now, we are stuck with the same problem...
i tried running the packages directly on the command prompt.. it gives the same error.. don't know what to do..
one last resort is to install the 32bit SQL Server on the production machine.. for which i do not have permission.
please advice..
Thanks
Deepak.
March 3, 2009 at 7:45 am
When something others are using is not working for you it means you are making mistakes, so here is a visual configuration to get it to work.
You have to make sure you reference the x86 which is the 32bits dll.
http://ssis.trigonblue.com/Excelin64bitenv/tabid/523/Default.aspx
Kind regards,
Gift Peddie
March 4, 2009 at 1:11 am
Gift,
Thank you sooo much for being there and replying on my every post to fix this issue.
i fixed it.. i fixed it.. thanks.. we need to keep the SSIS package running in the 64bit environment and here is the code used in the SQL SERVER AGENT under Operating System command(exe)
"c:\program files(x86)\microsoft sql server\90\DTS\Binn\Dtexec.exe" /File"C:\package.dtsx" /maxconcurrent "-1"/checkpointing off /Reporting E
my mistake was that, i was using single quote to invoke dtexec and closing the quotation after reporting e....
Thanks for all your help...
i am so grateful to you... 🙂 thanks again...
March 6, 2009 at 4:34 pm
Hi Deepak,
I am sorry to get back to you so late, I also very happy your problem is resolved. And thanks for the complements.
🙂
Kind regards,
Gift Peddie
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply