Solved

SSIS Export to Excel

Posted on 2011-02-12
5
1,578 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

730 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