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

By: Jiqin Ma   |   Read Comments (2)   |   Related Tips: 1 | 2 | 3 | More > Integration Services Excel

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

View all my tips

We Recommend

• Dynamically find where table data starts in Excel ...
• Retrieve Excel Schema Using SQL Integration Servic...
• More Business Intelligence Tips...

Print

Tweet

Become a paid author

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code