How to build a DTS Package so it will generated excel file without column
headers?

Any help is appreciated.

John

John, This code snippet should be inserted into an Active X Script task at
the beginning of your package:

' Now set the Excel Filename on the Connection
Set oPkg = DTSGlobalVariables.Parent
Set oConn = oPkg.Connections("Connection Summary 1")

oConn.DataSource = DTSGlobalVariables("FileName").Value

oConn.ConnectionProperties("Extended Properties").Value = "Excel
8.0;HDR=NO" ' HDR = YES means generate column header in exported Excel file

Thank you.




"jaryr" wrote:

Have a look at this article

Excel treats First Row of Data as Headers
(http://www.sqldts.com/default.aspx?255)




"jaryr" <najyer (AT) hotmail (DOT) com> wrote

Thanks a lot.

The problem is that Extended Properties are HDR=NO and when I try to
force DTS package to generate xls file, the CREATE TABLE script add
headers to newly created Excel file before transformation task pumps
data. If Excel file is already created and doesn't need to be created by
DTS package then it works fine.

Any idea how to overcome this problem?

Again, thank you very much,

John



"frank chang" <frankchang (AT) discussions (DOT) microsoft.com> wrote

Yes, I am aware of HDR=YES/NO in Extended Properties of the Excel connection
object.
The problem is that when DTS package has to generate Excel file it creates
additional
task with a script CREATE TABLE .... This task adds headers to Excel file no
matter
what I include in Extended Properties.

Any idea how I can overcome this problem?

Thank you very much,
John


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

John, I just checked the DTS package I wrote which exports to Excel and I set
HDR = NO in the Extended Properties of the Excel connection and it works
fine. Have you tried Allan Mitchell's other solutions in his article? If it
is OK with you , could you please post excerpts of your Active X Script code?
Also, once the data transformation into your Excel named range, you
can used the Excel COM object model so that the column header never shows.
Thank you.

"jaryr" wrote:

I use Disconnected Edit option to set Extended Properties as per Allan
Mitchell's solution.
When I try to export to existing spreadsheet it works fine, once the
spreadsheet is generated
by my DTS Package, the headers are generated no matter what I set up in in
Extended Properties.

In the DTS Package you wrote are you exporting to exsisting spreadsheet?

Thanks,
John


"frank chang" <frankchang (AT) discussions (DOT) microsoft.com> wrote

John, In the test I just did, I am trying to export the result of a SQL
Server query to a named range on a new Excel worksheet. I create the Excel
worksheet using an Excel COM object method in a DTS Task just before the DTS
Data Transformation Task. Before the DTS package runs, no Excel spreadsheet
exists. The name of the new excel worksheet is "ABCDEFG" + today's date and
time + ".xls". However, my package is slightly different than yours in that I
am trying to export to a named range on this new spreadsheet.
I know that you can avoid having to deal with Extended Properties,
if you move the data around on your exported Excel spreadsheet , after the
DTS Data Transformation Task, using an Excel COM Object model method so that
the column headers are overwritten. I don't know what your constraints and
specifications are so I don't know if this will help you. Thank you.

"jaryr" wrote:

I have to create a generic server side component (in VB 6) which will export
data from SQl 2K server to excel file by building DTS package at runtime.
There are no Excel objects on the server so the approach you are using is
not an option for me, Microsoft.JET.OLEDB.4.0 is the only available thing I
can use.

Looks like I have two options:
1. Generate empty Excel template file and just before generating DTS package
copy it and use a copy as a export destination file.
2. Forget about option of exporting to Excel with no column headers.

Thank you very much for all you help,
John



"frank chang" <frankchang (AT) discussions (DOT) microsoft.com> wrote

John, Before you implement your template approach, please check with Allan
Mitchell. Finally, I have seen many production SQL Server(i.e database
server) instances which also have Microsoft Excel installed. I am curious why
Microsoft Excel is not allowed on your SQL Server. Once Microsoft Excel is
installed, you automatically get the Excel COM Object.

"jaryr" wrote: