Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Learning is a never ending thing and just one life is not enough to learn all the things you want to learn. The ocean of knowledge is very, very deep. This blog has been created for the same objective. I write on things which I come across in my daily life and feel to share it with the people across the world. The blog publish articles on SQL Server, SSIS 2005, SSIS 2008 alongwith other useful stuff which are good to know for the professional life.
Browse by Tags · View All
Google SpreadSheet API 12
YouTube API with .NET 7
PEGA Tutorials 6
PRPC Tutorials 6
Google BigQuery 6
Google Analytics API in .NET 6
Google Cloud Services 5
Core Reporting API with C# 4
Good Data API with Picasa 4
Ms-Excel function 4

Archive · View All
June 2012 17
October 2012 12
May 2012 12
August 2012 11
March 2013 10
July 2012 10
December 2011 9
January 2012 7
September 2012 6
February 2012 6

SinghVikash Blog

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

Dec 24 2012 12:00AM by Vikash Kumar Singh   

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.




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


Republished from Blog by Vikash Kumar Singh [46 clicks].  Read the original version here [3 clicks].

Vikash Kumar Singh
282 · 0% · 155
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

3  Comments  

  • Where Can I Find the Blog which Load Data from Excel spreadsheet which has protection level set at workbook level.

    commented on Mar 18 2013 6:31AM
    Rihan
    1129 · 0% · 23
  • where can i find the post which loads the data into SQL from an excel file which has protection at workbook level using SSIS?

    commented on May 26 2014 6:34AM
    katari
    2826 · 0% · 4
  • where can i find the post which can load data into SQL from an excel file which has protection at workbook level using SSIS?

    commented on May 26 2014 6:36AM
    katari
    2826 · 0% · 4

Your Comment


Sign Up or Login to post a comment.

"SSIS: How to read password protected Ms-Excel files?" rated 5 out of 5 by 1 readers
SSIS: How to read password protected Ms-Excel files? , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]