Solved

SSIS Export to Excel

Posted on 2011-02-12
5
1,539 Views
Last Modified: 2012-05-11
Hi,

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.

DROP TABLE [GBP$]
GO

CREATE TABLE [GBP$] (
[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),
SECCHKID int,
SECVALUE date,
ACCESSCODE nvarchar(50),
FEETIER int,
DENYSVC int,
BRNCODE nvarchar(50))
GO
0
Comment
Question by:irb56
  • 3
  • 2
5 Comments
 

Accepted Solution

by:
irb56 earned 0 total points
Comment Utility
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.
0
 

Author Closing Comment

by:irb56
Comment Utility
Hopefully this might be of some use to others if they encounter the same issue.
0
 

Expert Comment

by:MatJamT
Comment Utility
Hi,

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)?
0
 

Author Comment

by:irb56
Comment Utility
Hi,

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

Expert Comment

by:MatJamT
Comment Utility
Hey,

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!
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now