Use SSIS to import one cell of an Excel file into SQL Server

Problem

Recently I needed to find a method to import one cell of an Excel sheet into SQL Server 2005 using a scheduled job on a 64 bit clustered environment. For example, I have a monthly sales report listing sales per product category, such as Books, Magazines, DVDs, etc. I only want to import the Total Sales amount into SQL Server and this number is located in an Excel file "sales.xls.sheet1.cell.B5". I used to use OPENROWSET to solve the problem, however OPENROWSET requires Microsoft Jet OLE DB Provider which is not available in 64 bit. In this tip I am going to talk about how to use SSIS to accomplish this task easily.

Solution

Before we get into the details, I would like to mention another tip about how to use SSIS to import Excel data into SQL Server the tip is Importing Excel data with SQL Server Integration Services SSIS with unicode and non-unicode data issues. Please read this tip first if you need a general understanding about importing Excel data using SSIS.

The following steps show how to import one cell of an Excel sheet into SQL Server.

For example, this is what my Excel file looks like, the data I need to import is Total Sales (2300) in cell B5.

For simplicity, this is the structure of the table I am loading the data into.

CREATE TABLE [dbo].[Sales](
[Product] [varchar](50) NULL,
[Sales] [numeric](18, 2) NULL
) ON [PRIMARY]

SSIS Package

1. Using SSIS, create a Data Flow Task as shown below. When adding the Data Flow Destinations, make sure to select "SQL Server Destination", not "OLE DB Destination". The difference between these two is: SQL Server Destination gives you ability to define which row or rows (row 5 in this case) you want to import, while an OLEDB Destination doesn't provide this option.

2. In the Excel Source Editor, select column "Sales($)" as shown below. In my example, I have 5 rows of data and when I selected my Excel file I specified that the first row does not have column names. In addition I gave column names instead of using the defaults F1, F2, etc... Otherwise if I said my Excel data had column names I would import data row 4.

3. In the Data Conversion Transformation Editor, select Output Data Type as "numeric[DT_NUMERIC]". When setting this up I used a precision of 18 and scale of 2 to allow for decimal values.

4. In SQL Destination Editor, map the converted number -- "Copy of Sales($)" in this example to the Destination Column.

5. Again in SQL Destination Editor, select Advanced, put 5 as both the "First row" and "Last row" as shown below. Row 5 contains the total sales value that we want to import. You can specify the other options shown below as needed.

That's all there is to it. In this example I am always going to pull in the value in cell B5, so if you have a static Excel sheet this is a nice simple approach to pull in just one value.

Next Steps

Please notice that in order to use a SQL Server Destination, the SSIS package has to be run on local server, in this case it will be the data import destination server. The package file can be located remotely though. Therefore you need set up the schedule job on the destination server to run this package, not on a remote server.
Since there is no 64 bit driver for Excel, when setting up a scheduled job on a 64 bit server, you have to create a CmdExec step to manually call the 32 bit DTExec.exe to execute the package, such as: C:\SQL 2005 Tools (x86)\90\DTS\Binn\DTExec.exe /FILE "D:\Package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E
File DTExec.exe is installed with the SQL Server client component, not the SSIS component. So in a clustered environment, make sure the SQL Server 2005 client is installed on all nodes and DTExec.exe is located on same folder structure, such as C:\SQL 2005 Tools(x86) on all nodes, otherwise the job may fail when a SQL instance fails over to a different node and the file DTExec.exe is not available.

About the author

Jiqin Ma's bio is coming soon...

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, February 18, 2013 - 7:21:55 PM - Erick

I'm trying to do the same but want to store the single cell value in a variable so i can use it later on.

Can that be achieved ? If so where would i find an example ?

Tuesday, April 17, 2012 - 9:00:10 AM - Koen Verbeeck	Back To Top (16967)
Wouldn't it be easier to use a named range instead of the SQL Server Destination? You wouldn't have to read the entire Excel file and you don't have those nasty dependencies introduced by the SQL Server Destination.