Reading data from excel is one of the regular stuff an SSIS professional do at his/her workplace.
Data in excel comes in various forms and nature. Some time you are reading data from a well-defiend excel file and some time your data in excel is scattered. In many occasion you have to read the data from a single cell only.
In a scenario when your excel file data is scattered or you want to read data from a particular cell you can try the approach discuss in this post. Let us assume my excel data is like below screen. I have to read data of B5, C2 and D6 cell and combined them into one column.
What I can do is I can create RANGE in the excel file. For simplicity I have created three range for all three cells. The Range Name is Company1, Company2 and Company3.
To define a RANGE in excel 2007 you can right click on the cell and select “Name a Range”.
Now coming back to SSIS side, I created a Data Flow task. Inside the Data Flow Task I have following components.
Each of the
Excel Source is reading one of the RANGE(company1, company2 and company3) in excel file. The
Union All combines all the rows from three excel source(s) and shows the result in a Data Viewer.
Row Sampling has no use in the data flow. It has been added just to show what data is being passed from Union All to the next components.
To read the single cell or RANGE we have to select the range from the Excel Source Editor. I am not selecting Sheet1 or Sheet2 but RANGE names (compan1, company2 and company3) which we defined in the excel file.
After setting the package components I run the package and as expected I had the values from all three RANGE in a single column.
This shows an example where we can read the values from a single cell or from a RANGE from excel file.
There are other ways to achieve the same in the SSIS. I hope you like the post.
