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