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

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/