Help – SSIS Job Cannot Access Excel File

  • Tolive

    SSC Enthusiast

    Points: 195

    This is driving me crazy now, any help is great appreciated:

    I have a SSIS package for importing data from an Excel file to a SQL 2005 database:

    1. Check to see if the Excel file exists (fixed name Excel file on another internal network folder)

    2. If #1 returns true, import data; if not, exit.

    If I run the package directly, it works fine, but if I schedule a job to run the package, the job can still run successfully, BUT step 1 in SSIS always return false, i.e. step 2 in SSIS will never been executed.

    Looking at the log, I found that the step 1 in the SSIS package always retuened false because accessing the Excel file was denied, I’ve tried to give the network folder FULL permission to even EVERYONE, but it still doesn’t work.

    What I missed?

    Error log:

    ——————————————————————-

    Code: 0xC0202009 Source: TrackingDataImport Connection manager “SourceConnectionExcel” 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: “The Microsoft Jet database engine cannot open the file ‘\\MyNetworkFolder\ExcelData.xls’. It is already opened exclusively by another user or you need permission to view its data.”. End Error

    ——————————————————————-

  • Tommy Bollhofer

    SSChampion

    Points: 14940

    Looks like there is a lock on the file (on the server, right-click my computer, manager, system tools, shared folders, open files). Also, have you granted permissions to the share in addition to the NTFS permissions?

  • Tolive

    SSC Enthusiast

    Points: 195

    Thanks a lot Tommy! it’s working now.

    I figured it out, it’s the share permission, but I still don’t understand why it’s like this:

    In my company, for all shared folders, we give EVERYONE full share permission, and we control effective permissions by controlling the NT permission only. But this seems not working for the SQL Service account, after I added a dedicated share permission for the SQL Service account, it works now.

    Tommy Bollhofer (12/21/2007)


    Looks like there is a lock on the file (on the server, right-click my computer, manager, system tools, shared folders, open files). Also, have you granted permissions to the share in addition to the NTFS permissions?

  • kelvinthong83

    Valued Member

    Points: 63

    I have the same problem when importing data from an Excel file to a SQL 2005 database as well but my excel file stored in the SharePoint document library.

    I’ve tried to give full permission to the document library but it still doesn’t work.

    Error:

    The Microsoft Jet database engine cannot open the file ‘\\serversdev\Documents\QC Files\Prod.xls’. It is already opened exclusively by another user, or you need permission to view its data.

  • AIRWALKER-375999

    Hall of Fame

    Points: 3040

    kelvinthong83 (11/18/2010)


    I have the same problem when importing data from an Excel file to a SQL 2005 database as well but my excel file stored in the SharePoint document library.

    I've tried to give full permission to the document library but it still doesn't work.

    Error:

    The Microsoft Jet database engine cannot open the file '\\serversdev\Documents\QC Files\Prod.xls'. It is already opened exclusively by another user, or you need permission to view its data.

    We’ve also recently started to get this error, did you find the caause?

  • Elliott Whitlow

    SSC Guru

    Points: 102296

    If any user anywhere has it open you will get this message. Do you have it open in Excel on your desktop? Had it happen many times..

    CEWII

  • AIRWALKER-375999

    Hall of Fame

    Points: 3040

    Hi Elliott thanks for your response,

    the excel file our ssis packages uses goes on a short journey before it end up on the database server,

    it is uploaded to an ftp server from a client machine, and then is downloaded from the ftp server by another ssis package to our database server, then it is moved to a directory where the ssis package that is throwing this error is located.

  • Jason Norsworthy

    SSChasing Mays

    Points: 606

    AIRWALKER-375999 (12/21/2010)


    Hi Elliott thanks for your response,

    the excel file our ssis packages uses goes on a short journey before it end up on the database server,

    it is uploaded to an ftp server from a client machine, and then is downloaded from the ftp server by another ssis package to our database server, then it is moved to a directory where the ssis package that is throwing this error is located.

    Are you still having issues with this? I just solved my (very frustrating) problem.

    My package has a hard coded “Excel File Path”, which as far as I know, you can’t avoid. I then have an expression that sets the Excel File Path to a variable that is set by a script task. All this works fine on my development machine, because my original excel file is still at that hard coded path. Upon deploying to my prod server, this package starts throw the error detailed in this thread. Copying the excel file to the server machine so it can be found by the package (even though it is not used) fixed the problem. I do have “Delay Validation” = true, so I don’t know a way around this other than to make sure that the package can find a file at the hard coded Excel File Path. Very frustrating indeed >:|

  • assefa.samrawit

    Newbie

    Points: 9

    Data type change SSIS.

    I am using for each loop container to read excel files and move them to an archive folder, but I forgot to create an archive folder and SSIS automatically move the excel files to .file format, and I couldn’t open the file type file. what should I do? Thanks in advance.

Viewing 9 posts - 1 through 9 (of 9 total)

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