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

Using Excel Connection Manager to connect to UNC Path Expand / Collapse
Author
Message
Posted Monday, March 25, 2013 9:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 1, 2013 7:07 AM
Points: 11, Visits: 75
Hi,
I am trying to connect to the UNC path , but am unable to connect to the Shared Drive, i cannot find any option in the Excel file connection Manger Properties , where i can specify the service account user name and password.I tried to provide the user name and Password in the Connection string and also in the properties of the connection manager, but when i try to access the file during development , it asks for username and password for Excel file connection manager.It seems that this is for the connection manger.I need to deploy this package on the Server and from there it needs to use the service account, to fetch data from the excel files.
I am doing this for the first time so any help will be appreciated.
Thanks
gurvi
Post #1434991
Posted Monday, March 25, 2013 11:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:57 AM
Points: 7,080, Visits: 12,571
gurvimail (3/25/2013)
Hi,
I am trying to connect to the UNC path , but am unable to connect to the Shared Drive, i cannot find any option in the Excel file connection Manger Properties , where i can specify the service account user name and password.I tried to provide the user name and Password in the Connection string and also in the properties of the connection manager, but when i try to access the file during development , it asks for username and password for Excel file connection manager.It seems that this is for the connection manger.I need to deploy this package on the Server and from there it needs to use the service account, to fetch data from the excel files.
I am doing this for the first time so any help will be appreciated.
Thanks
gurvi

Not sure which password you're referring too. Are you saying the Excel file is password protected? i.e. when you try to open the file using Excel are you prompted for a password? If so then you cannot programmatically access this file using the default SSIS Excel connector. You'll have to remove the Spreadsheet password from the file, save it, and then SSIS can access it.

If you mean that you cannot access the UNC path where the Excel file resides then you will need to ask for your Windows Account to be granted access to the UNC path. Similarly, when you deploy your SSIS Package to the server, the service account the SSIS Package will be running as needs to have access to the UNC path.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1435078
Posted Monday, March 25, 2013 11:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 1, 2013 7:07 AM
Points: 11, Visits: 75
i am referring to the service account, the excel file is not password protected. and i think you are right that i need to have my windows account to have acces to the Path.
After deploying it on the server , do i need to add the service account user name and password, somewhere in the package.so that package is runnig through the service account.
Thanks for the quick reply.
Post #1435079
Posted Monday, March 25, 2013 12:47 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:57 AM
Points: 7,080, Visits: 12,571
gurvimail (3/25/2013)
i am referring to the service account, the excel file is not password protected. and i think you are right that i need to have my windows account to have acces to the Path.
After deploying it on the server , do i need to add the service account user name and password, somewhere in the package.so that package is runnig through the service account.
Thanks for the quick reply.

With Windows Authentication the Windows Identity the process is running as is what everything is accessed as. What I mean by that is, when your Windows User has access to a UNC path the reason why you are not prompted for a username and password when you go to that path is because Windows knows who you are logged in as and it automatically and transparently passes a token to authenticate you to the external resource. The same happens in unattended processes, e.g. with SSIS Packages running on the server. The service account running the SSIS Package needs access to the UNC path.

Re: Connection Strings, those are for databases only you can only specify a username and password for database-based Logins, not Windows Users. If you have Trusted Authentication=true or Integrated Security=true in your connection string then you would not supply a username and password.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1435096
Posted Monday, March 25, 2013 1:17 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 1, 2013 7:07 AM
Points: 11, Visits: 75

My connection string looks like this for excel, so if the user with the services account exists in the server then it should be able to acces the file., i am confused on this.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\"Here i provide the path"';Extended Properties="EXCEL 12.0 XML;HDR=YES";
Post #1435115
Posted Monday, March 25, 2013 1:54 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:57 AM
Points: 7,080, Visits: 12,571
gurvimail (3/25/2013)

My connection string looks like this for excel, so if the user with the services account exists in the server then it should be able to acces the file., i am confused on this.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\"Here i provide the path"';Extended Properties="EXCEL 12.0 XML;HDR=YES";

Yes. FOr Excel connection strings there is no other option other than to have the Windows User executing the SSIS Package to have been granted access to the file specified in the Data Source, i.e. there is no way to provide a username or password in an Excel OLE DB Connection String either for the UNC access nor the Excel Spreadsheet password (if there is one which you said there isn't).


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1435133
Posted Monday, March 25, 2013 3:08 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 1, 2013 7:07 AM
Points: 11, Visits: 75
Thanks for the Help i will put this topic as closed.
Post #1435161
Posted Tuesday, March 26, 2013 5:46 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 1, 2013 7:07 AM
Points: 11, Visits: 75
Hi,
Thanks for the help , i had created a services account and through that i was running the packages now.They are working fine.
Thanks
Post #1435719
Posted Tuesday, March 26, 2013 6:56 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:57 AM
Points: 7,080, Visits: 12,571
Great to know you got it sorted! Thanks for posting back.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1435726
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse