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.
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.
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.
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.