SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help - SSIS Job Cannot Access Excel File


Help - SSIS Job Cannot Access Excel File

Author
Message
Tolive
Tolive
SSC-Enthusiastic
SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)

Group: General Forum Members
Points: 157 Visits: 9
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
Tommy Bollhofer
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13886 Visits: 3363
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 @sqlscribe
Tolive
Tolive
SSC-Enthusiastic
SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)

Group: General Forum Members
Points: 157 Visits: 9
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
kelvinthong83
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 18
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
AIRWALKER-375999
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2752 Visits: 225
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
Elliott Whitlow
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94090 Visits: 5314
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
AIRWALKER-375999
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2752 Visits: 225
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
Jason Norsworthy
Mr or Mrs. 500
Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)

Group: General Forum Members
Points: 566 Visits: 255
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 >:|
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search