• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1729
  • Last Modified:

SSIS Export to Excel


I have been asked to export data from SQL Server 2008 into an Excel 2003 workbook with three worksheets. Each worksheet has an identical format and is used simply as a means of partitioning the data into currency groups. The worksheets are named GBP, EUR and USD. Each worksheet features 33 columns.

I have created the Excel workbook on my local machine and uploaded to the server (which doesn't have Excel but does have the Office System Driver Data Connectivity Components installed to enable data connections to Excel files). I have created three Execute SQL tasks for the purpose of clearing down and refreshing each worksheet (to prevent exports from appending data). These tasks have a connection type of Excel and the SQL Statement is as below.

What is puzzling me is why the first task succeeds for the GBP worksheet but the second task fails with the error "Too many fields defined". This is despite the fact that the SQL statements are identical for all three tasks, with the only exception being the table references (GBP, EUR, USD).

Is this a bug? Is there a better way to achieve what I want? At the moment the only way I can get the data to the third party in their desired format is to export to three CSV files and then manually copy/paste, format and save as Excel. This is a pain and I'm hoping there is a more reliable and efficient solution!

Many thanks in advance.


[Type of Request] nvarchar(50),
[participant ID] nvarchar(50),
[Account ID] nvarchar(50),
Instcode nvarchar(50),
programid nvarchar(50),
designref nvarchar(50),
currcode nvarchar(50),
statcode nvarchar(50),
crdproduct nvarchar(50),
lang nvarchar(50),
renew nvarchar(50),
[action] nvarchar(50),
cardname nvarchar(50),
crdusrdata nvarchar(50),
amtload numeric(8,2),
Title nvarchar(50),
LastName nvarchar(50),
FirstName nvarchar(50),
Addrl1 nvarchar(50),
Addrl2 nvarchar(50),
Addrl3 nvarchar(50),
Addrl4 nvarchar(100),
PostCode nvarchar(50),
Country nvarchar(50),
Loadsrc nvarchar(50),
ProducePin nvarchar(50),
DOB nvarchar(50),
ACCESSCODE nvarchar(50),
BRNCODE nvarchar(50))
  • 3
  • 2
1 Solution
irb56Author Commented:
I've solved this problem now. I needed to create a script task that runs first and deletes the Excel workbooks if they already exist. I also needed to get rid of the DROP TABLE statements. It seems to be common practice to wrap the table and column names in the inverted quote (`) symbol, rather than use the square brackets ([ ]). I'm not sure if there is any significance of the inverted quote, but it worked in my revised solution. Also, I got rid of the dollar ($) symbol from the table (worksheet) name as this threw an error.
irb56Author Commented:
Hopefully this might be of some use to others if they encounter the same issue.

I'm just curious as to one thing with the above. You have a few columns formatted as integers in your CREATE TABLE statement above. When the Excel spreadsheets are created and the data is extracted, are these columns correctly formatted as numbers in Excel (and not numbers as text)?
irb56Author Commented:

The technique described seems to work for xls files and the numbers don't seem to get formatted as text in Excel (i.e. they stay as numbers and can be summed in Excel without needing to first convert from text to number format). The date format also seems to work. I've just checked this but I'm using my local installation of Open Office because I don't have Excel at home. However, I don't believe the CREATE TABLE approach works at all with xlsx files (at least, it's never worked for me). The method that works with xslx files is the template approach, where you prepare a template in Excel with just columns headings and then use a File System task to copy the template over the file that your Excel destination connector points to. You can control column widths using this approach, but I've always found that numbers and dates go into the xlsx files as strings and need manual conversion in Excel after the SSIS package execution.

SSIS doesn't give you fine grained control over formatting. It's not designed for this. If you want to export data to Excel and the formatting is important, use Reporting Services to publish the report and create a subscription on it.

Thanks for the reply. I thought as much regarding the need for a template file for xlsx, but wasn't sure if the create table method worked with xls. FYI, in order to export numbers as numbers in xlsx, you can use a hidden row of dummy data below your template column headers, where the dummy data is formatted correctly. Don't think it works for eg. currency and dates, but certainly keeps numbers as numbers.
Thanks again for the reply!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now