In SSIS we can read excel data starting from any number of row. Considering a scenario we have a excel file like below screen and we need to read data of Row 3 to Row 14 for Top 10 companies we can do this in SSIS.
To do this I create an Excel Connection manager in my SSIS package. I created a Data Flow Task in the control tab. On Data Flow tab I have following components.
- 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?
I have put a Data Viewer between Excel Source and Sort. Sort component has no use in my package I simply wanted to show the data in the Data Viewer. I setup my Excel source to read the data from excel file (shown in picture 1). After setting up the Excel Source I went to the Properties window and setup the OpenRowset property value to Sheet1$A3:B14. This is because I want to read the values of Top 10 Companies and their location.
After setting the OpenRowset property I run the package and it shows the 10 rows it has read from Excel file.
In second attempt I want to read the Top 10 Companies of India. To do so I setup the OpenRowset property to Sheet1$A18:B27.
After setting the OpenRowset property I run the package and it shows the 10 rows it has read from Excel file.
We can further export these data to any table or flat file we want to. This shows an example when we have a boundation to read the data from a excel file of a specific row. The same property can be setup programmatically using Script Component.
Related Article:
- SSIS: Read public data of Twitter Users?
- SSIS: How to pull Currency Rates from European Central Bank
- SSIS: How to pull Tweets from Twitter?
- SSIS: How to read Excel Meta Data?
- SSIS: How to resolve Excel Import 255 character Truncation issue?
- SSIS: Read and Export Excel data from nth Row
- SSIS: How to pull Stock Quotes from Google Finance
- SSIS: How to load data into SQL table from Google Spreadsheet?
- SSIS: How to Download images from Picasa album?
- SSIS: How to Load images into Picasa web album?
- SSIS: How to Load XML search result from Search engines like Bing, Google etc?
- SSIS: How to generate HyperLink in Excel output?
- SSIS: How to Compress/Zip your file using 7-Zip?
- SSIS: What is CheckPoints?
- SSIS: Checkpoints implementation
- SSIS: Event Handlers
- SSIS: Data Flow v/s Control Flow
