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


ssis error in excel connection manager


ssis error in excel connection manager

Author
Message
susmitha117
susmitha117
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 145
Hi,
i have a ssis package where i import excel to sql table destination. . i created a excel connection manager and selected the sheet of excel in excel connection manager. When i am trying to click the 'columns' it gives me the error saying that "The Microsoft Jet database engine cannot open the file . It is already opened exclusively by another user, or you need permission to view its data."

I am sure that the file is not opened by anyone. What might be the error?Please help
I am using visual studio 2005. The connection string i used is Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

Thanks
prabhu.st
prabhu.st
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 310
1) Ensure the file is not in open state. (in some cases, even though you close that particular file and not the excel, due to cache, you could get this kind of error, of so close all the excel files and (excel as well) try opening) .
2) Ensure you are able to access the folder where the file is available. (there may be permission issues, because the direct permission is differ from the sql server instance permission)

Regards,
Prabhu
twin.devil
twin.devil
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2354 Visits: 2670
prabhu.st (7/25/2014)
1) Ensure the file is not in open state. (in some cases, even though you close that particular file and not the excel, due to cache, you could get this kind of error, of so close all the excel files and (excel as well) try opening) .
2) Ensure you are able to access the folder where the file is available. (there may be permission issues, because the direct permission is differ from the sql server instance permission)

Regards,
Prabhu

+1, additionally, sometimes excel tends to hold the document open even if you close the excel it can happen that excel is closed in the explorer but keeps on running in the background. you can also end that process.
susmitha117
susmitha117
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 145
Thank you! But unfortunately i work on remote desktop. It is recommended that i should not end any processes on the remote desktop's
prabhu.st
prabhu.st
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 310
I just had a guess, you were working on the remote machine, better you try with executing sqlcmd for that particular folder and see whether you are able to access it. if "yes", then the file is in open status, if "no" then for sure it is access/permission you had for that particular folder..

(ensure you have the admin privilege on the DB where you work, it is to enable the configuration (sp_configure) to run the sqlcmd)

regards,
Prabhu
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