SSIS importing Excel

  • 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

  • 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...

  • 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

  • oops sorry Mark......

  • I have changed the files to CSV and this works fine.....

  • (still does not work with Excel tho)

  • 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

  • Great stuff!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply