December 21, 2007 at 11:44 am
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
-------------------------------------------------------------------
December 21, 2007 at 11:57 am
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?
Tommy
Follow @sqlscribeDecember 21, 2007 at 2:13 pm
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?
November 18, 2010 at 4:43 am
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.
December 20, 2010 at 10:25 am
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?
December 20, 2010 at 11:50 am
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
December 21, 2010 at 8:49 am
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.
April 5, 2012 at 2:23 pm
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 >:|
January 21, 2019 at 1:27 am
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.
April 19, 2022 at 10:56 am
This was removed by the editor as SPAM
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply