Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ssis error in excel connection manager Expand / Collapse
Author
Message
Posted Monday, July 21, 2014 6:27 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 9:52 AM
Points: 26, Visits: 103
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
Post #1594898
Posted Friday, July 25, 2014 1:25 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 12:19 PM
Points: 86, Visits: 220
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
Post #1596103
Posted Friday, July 25, 2014 2:01 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 11:46 PM
Points: 778, Visits: 1,386
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.
Post #1596111
Posted Friday, July 25, 2014 7:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 9:52 AM
Points: 26, Visits: 103
Thank you! But unfortunately i work on remote desktop. It is recommended that i should not end any processes on the remote desktop's
Post #1596207
Posted Friday, July 25, 2014 7:52 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 12:19 PM
Points: 86, Visits: 220
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
Post #1596218
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse