Using Excel Connection Manager to connect to UNC Path

  • 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

  • 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

  • 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.

  • 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

  • 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";

  • 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

  • Thanks for the Help i will put this topic as closed.:-)

  • 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

  • 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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply