June 5, 2008 at 4:33 am
Hi,
I have a SSIS package that runs against 2 data sources
1) SQL server
2) Excel files
I have set the protectionlevel to encryptallwithpassword..and set a password
have created a set of credentials and set that to run against an account that belongs to the local admin group on the server.
If I run in design mode all runs well
when i run as a SQL agent Job the excel fails
(The sql server import runs fine)
The error i am getting is
Message
Executed as user: domain\admin. ...0.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:46:01 Error: 2008-06-05 10:46:40.85 Code: 0xC0202009 Source: Sun_Staging Connection manager "Accounts_LP" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error". End Error Error: 2008-06-05 10:46:40.85 Code: 0xC020801C Source: Import_Excel_Files Account_Hierarchy [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Accounts_LP" 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: 2008-06-05 10:46:40.87 Cod... The package execution fa... The step failed.
the excel file is an excel 2003 file
any ideas PLEASE 🙂
Regards
Ray
June 5, 2008 at 4:47 am
Does your SQL Server Agent service account have administrator rights? Try adding your account in there and restart the service and see if it runs then...
June 5, 2008 at 4:50 am
HI SSC
The SQL server agent runs under local system account, this should have access to the machine..(the file is on local.
The directory that holds the excel files also currently has everyone setup as read access
Regards
June 5, 2008 at 4:51 am
oops sorry Mark......
June 5, 2008 at 5:12 am
I have changed the files to CSV and this works fine.....
June 5, 2008 at 5:41 am
(still does not work with Excel tho)
June 5, 2008 at 8:42 am
Ok got it working, seemed the virtual mem on the SQL server was limited to 2 Gig max (the server has 4 gig physical mem)...seemed as tho the buffer manager was maxing out changed the vm on the machine and it worked
June 5, 2008 at 9:30 am
Great stuff!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply