aber (3/18/2010)
I need to import data from an Excel file into a SQL 2005 DB and thought I would use SSIS since this is a pretty straight forward task for SSIS. However, I just learned that the Excel file is password-protected and I have not found anything online that mentions how to do this since there is no way to distinguish this in the Connection to the Excel file.I do know the password to open the file but just don't know how to automate this. Any ideas?
If you can use third-party solutions, check the commercial CozyRoc Excel components. These are the relevant components:
* Excel Source component - for reading data from Excel worksheet.
* Excel Destination component - for writing data in Excel worksheet.
* Excel Task - for manipulating Excel workbooks.
* Excel Connection - used by the components above and also for implementing custom scripts based on it.
The Excel Connection supports opening of password-protected workbooks and doesn't require installation of Office. Cheers!