Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS importing Excel Expand / Collapse
Author
Message
Posted Thursday, June 5, 2008 4:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 5, 2008 10:48 AM
Points: 7, Visits: 15
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
Post #511995
Posted Thursday, June 5, 2008 4:47 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, January 6, 2011 6:44 AM
Points: 206, Visits: 363
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...

Post #511998
Posted Thursday, June 5, 2008 4:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 5, 2008 10:48 AM
Points: 7, Visits: 15
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
Post #512001
Posted Thursday, June 5, 2008 4:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 5, 2008 10:48 AM
Points: 7, Visits: 15
oops sorry Mark......
Post #512002
Posted Thursday, June 5, 2008 5:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 5, 2008 10:48 AM
Points: 7, Visits: 15
I have changed the files to CSV and this works fine.....

Post #512009
Posted Thursday, June 5, 2008 5:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 5, 2008 10:48 AM
Points: 7, Visits: 15
(still does not work with Excel tho)
Post #512020
Posted Thursday, June 5, 2008 8:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 5, 2008 10:48 AM
Points: 7, Visits: 15
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
Post #512175
Posted Thursday, June 5, 2008 9:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, January 6, 2011 6:44 AM
Points: 206, Visits: 363
Great stuff!
Post #512225
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse