Exporting SSIS data to Excel 2007

Below is a short description of how to connect to Excel 2007 from SSIS.

Integration Services supports Excel files out of the box, but not Excel files made in Office 2007. These files have a different extension (.xlsx). To connect to them from SSIS, you first have to install an OLEDB driver. You can download it here.

After the installation you can create an OLEDB Source with the Microsoft Office 12 Access Database Engine OLE DB Provider as its Provider. Enter the location of the Excel sheet as the server name. On the ALL tab, enter the value ‘Excel 12.0 Xml’ in the field ‘Extended Properties’. The XML is important. If you enter only Excel 12.0, the output file becomes a binary file, which you do not want. See here for more information.

Create the columns in the Excel sheet beforehand, otherwise you get problems with the mappings in your destination! Cast all your fields in your OLEDB Source to NVARCHAR, because Excel 2007 likes Unicode strings 😎. If you do not do this, you get error messages like this one: cannot convert between unicode and non-unicode string data types.

The next problem is emptying the Excel file. You want a new data dump every day. If you do not empty the Excel file, SSIS adds the data every time the package runs. Then you get duplicate data.

With the OPENROWSET command in SQL Server you can query data from an Excel file. The first time you run it, you get the error message below:
“SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure..”

You fix this by running the statement below (see here for more information):

{geshi xml:lang=“tsql” lines=“false”}
sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Ad Hoc Distributed Queries’, 1;
GO
RECONFIGURE;
GO{/geshi}

Unfortunately, you cannot delete data from an Excel sheet with T-SQL. If you try, you get this error message:
‘Deleting data in a linked table is not supported by this ISAM’.

You can query it with the statement below:
{geshi xml:lang=“tsql” lines=“false”}
SELECT *
FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’,‘Excel 12.0 Xml;Database=C:Debiteuren.xlsx’, ‘SELECT * FROM [Sheet1$B1:F3]’);{/geshi}

Make sure you have read rights on the file you want to query, otherwise you get the error message below:
Error Message: The OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” reported an error. Access denied.

Not being able to empty the Excel file is an issue. I cannot manage to empty the Excel file and fill it again. So the solution has to come from creating the file again.

**Solution
**The solution is fairly simple. Below are the steps you go through to fill an Excel file successfully.

Step 1) Create a template laid out the way you want the filled Excel file to look. Create only the header row, meaning the column headings. Do not put any data in this file. Put this template in a folder, for example c:reportssjablonen . Also create a folder where the file gets filled, for example: c:reportsgevuld

Step 2) Create an SSIS Package with 2 File System Tasks. In Task 1 you delete the file in gevuld . In Task 2 you copy the template from c:reportssjablonen -> c:reportsgevuld

Step 3) Create a Dataflow Task that pumps the data across.

You need 3 connection managers for this solution. One that points to the template, one that points to the filled file and one to fill the Excel 2007 file (OLEDB Connection).