We all know that it is possible to import a table of data to SQL Server tables using Integration Services (SSIS). It is as easy as A-B-C. But what if you want to read only a cell from an Excel sheet and import that. Let say you have some kind of Excel sheet from which we need to perform a non-traditional import.

In the above Excel sheet, you will see that format is not a row, column format. Instead, you need to read each cell to find the data. For example, if you want to read Customer Name you, need to read the B3 cell. Similarly for the Order Number you need to read cell E3 and for Address, read B4, etc.

So you will not be able to use native SSIS to import this data. Instead there are two options of doing this, which I will show you.

By Using Name Cells

In Excel there is a feature where you can create "name cells". You can create a named cell by selecting Insert->Name->Define as shown below

For each cell, you need to create a Name. After creating a name, you can access that name treating it is as a table. However, from the Excel data source, you cannot create a cross join. So you need to create five Excel sources (for five cells) and create the SSIS package. Not only will you have five data sources, you also need to have Sorts, Merge Joins, which will complicate your SSIS package layout. See the package below as an example.

This is the output

Though this is what we are expecting, to get to that point we need to work hard, which is not worth money. On top of all this, you need to create a name cell in a Excel file, which you have to either do programmatically or use a template of some sort. The template method will not work in the case when you are generating these files from another third party system.

Using the Script Option

As they say, when you need some non-standard operations in SSIS, it is the script component's turn to put his hand up. These are the steps you need to follow to import that data using the Script component.

1.Drag and drop a script component and select "source" as the script option type.

2.By default the script language is Microsoft Visual C# 2008 and I have done this sample with Microsoft Visual Basic 2008. (That is my preference). Change this if you need to.

3.Define your output columns with the correct data type as shown below.

4.Edit the script. In the IDE you should add two references. These are the last two shown below.

I am not sure whether you need both references. However, in my case I had to add both.

5.Finally it is time to write some code. You need the Imports Microsoft.Office.Interop.Excel at the top of the imports section. Then replace the procedure CreateNewOutputRows with following code.

Public Overrides Sub CreateNewOutputRows()
Dim oExcel As Object = CreateObject("Excel.Application")
Dim FileName As String
FileName = Variables.FileName
Dim oBook As Object = oExcel.Workbooks.Open(FileName)
Dim oSheet As Object = oBook.Worksheets(1)
Output0Buffer.AddRow()
Output0Buffer.Address = oSheet.Range("B4").Value
Output0Buffer.CustomerName = oSheet.Range("B3").Value
Output0Buffer.OrderDate = oSheet.Range("E4").Value
Output0Buffer.OrderNumber = oSheet.Range("E3").Value
Output0Buffer.RepName = oSheet.Range("B5").Value
End Sub

6.Now this is our data flow.

Yes, you saw that correctly, no complications whatsoever, thus the same results.

The only issue with the script method is you need to have the Excel runtime installed. Apart from that, this method looks much smarter. If you wish to download the samples for above, you can get them from sky drive

• Order.xls - Excel where I have add some samples data and defined named cells
• ReadingCellByDefineName - SSIS Package which reads from the define cell names. You need to change the Excel file path to suit your environment
• ReadingCellByScriptTask - SSIS Package which reads from a script.You need to change the FilePath variable to suit your environment