Solved

SSIS Export to Excel

Posted on 2011-02-12
5
1,548 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
ID: 34880428
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
ID: 34880434
Hopefully this might be of some use to others if they encounter the same issue.
0
 

Expert Comment

by:MatJamT
ID: 37097605
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
ID: 37121381
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
ID: 37122418
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

911 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

20 Experts available now in Live!

Get 1:1 Help Now