SSIS: How to read password protected Ms-Excel files?

Reading and writing data with Ms-Excel is a very common in SSIS. A lot of individuals, organization, offices store their data in Ms-excel. To add security to their file and data, they do protect their Ms-Excels files with a password. In this article we will learn how we can read data from a password protected Ms-excel file using SSIS.

Ms-Excel provides two kind of protection mechanism to safeguard Excel file and data.

1. Protection at Sheet level
2. Protection at Workbook level

We will learn how we can read data of Ms-Excel if it has been protected using either of the two mechanism.

Reading Ms-Excel data protected at Sheet level.

For demonstration purpose let us create a Ms-Excel spreadsheet with following data. We have two columns – productCode and productName with some dummy data into it.

Let us apply Sheet level protection on the excel file. We clicked on Review->Protect Sheet and applied a password to protect the sheet.

• SSIS: How to load multiple excel files into multiple SQL tables?
• SSIS: How to loop through Multiple Excel sheets and load them into a SQL Table?
• SSIS: How to resolve Excel Import 255 character Truncation issue?
• SSIS: Read and Export Excel data from nth Row
• SSIS: How to generate HyperLink in Excel output?

Let us create a SQL Table to store Excel data into it.

CREATE TABLE [dbo].[tblProducts](

      [productCode] [nvarchar](50) NULL,

      [productName] [nvarchar](50) NULL

) ON [PRIMARY]

With BIDS designer we have created following ETL structure to read data from protected excel file. We have added one Excel Connection Manager which points to our excel sheet and one SQL Connection Manager which connects to our database which host our SQL table in the package. We have a Data Flow Task on the Control Flow tab. On the Data Flow tab we have added one Excel source which will read the data from excel file. Data Conversion component will convert the data of two excel columns into Unicode data type. OLE DB Destination component will load the data into SQL table (tblProduct).

After setting up the package, I run the package and it runs successfully and it has loaded all five rows into the SQL table.

So the point is if we have a password protected Excel spreadsheet which has protection level set at Sheet level, SSIS can read the data.

In next post we will learn to read Excel spreadsheet which has protection level set at workbook level.

Thanks for reading the post.