A variety of problems have been reported involving use of an Excel
worksheet as a destination file from DTS transform. The really useful
thread entries on this topic are hard to find, so there tends to be
alot of frustration.

The best procedure I have found for constructing the SQLTask statements
in order to handle the Excel worksheet as a destination is:

1) Create a named range
2) Create an execute sql task object in your DTS package. Call it
Drop
table. Use the existing connection to the Excel spreadsheet. The
command
is drop table "named_excel_range"
3) Create an execute sql task object in your DTS package. Call it
Add
table. Use the existing connection to the Excel spreadsheet. The
command
is Create Table named_excel_range ( field1 char(1), field2
char(10)....)

Build on success workflow between step 2 to 3 and from there to the
connection to SQL Server.

Watch out that your named range is not the same as the worksheet name.

This can cause problems.

--contributed by
John Gose
Microsoft SQL Server Support


The most useful KB documentation for this topic is:
http://support.microsoft.com/default...;EN-US;Q319951

Care must be taken with the named range issue. Pay particular
attention to the following recommendations from the KB article:
Selecting the Excel Table
· If you select the drop and re-create the destination table option,
the drop command fails the first time that you run the package because
the table does not exist; however, the export succeeds.
· If you select create the destination table without the drop and
re-create option, the create command fails on subsequent executions
because the table already exists; however, the export succeeds.
· If you execute a CREATE TABLE statement against Excel, such as the
statement that the wizard generates, this creates both a worksheet and
a named range with the same name; however, DTS works with the named
ranges unless you specify otherwise. To view these named ranges in
Excel: On the Insert menu, click Name, and then click Define.
· You cannot delete and replace existing rows in the Transform dialog
box, because you cannot delete Excel worksheet rows through OLE DB.
· If you manually blank out the exported data in the destination
worksheet, export the data again to have the new data appended below
the blank rows because the driver is looking at the saved definition of
the named range and it is expanding it for the new rows. If you delete
all the rows of old data in the worksheet, this behavior does not occur
because deleting the rows changes the saved definition of the named
range. However, it is preferable to use the drop and re-create option
to replace the existing data.


If multiple, ambiguous named ranges exist in the Excel file, your data
will get appended each time the DTS package executes. If you want the
drop and re-create behavior to work, go into the Excel file and clean
up the named ranges (Insert > Name > Define) -OR- start all over
again, following the 3-step procedure above exactly as written.

Good Luck. It's tremendously useful when you get it working.

Dave C.


BTW: here is your last resort -->
'maintain a blank template file and promote it before each data export
via VBScript
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()
'Fill in the blanks here
Dim sTemplatePath, sTargetPath, sSourceFileName, sTargetFileName
sSourceFileName = "RPT1.xlt"
sTargetFileName = "RPT1.xls"
sTemplatePath = "C:\Documents and Settings\Jim\Desktop\Templates\"
sTargetPath = "C:\Documents and Settings\Jim\Desktop\DailyOutput\"
sTargetFileName = sTargetPath & _
year(now) & "-" & month(now) & "-" & day(now) - 1 & _
" " & sTargetFileName
DTSGlobalVariables("sFileName").Value = sTargetFileName
sSourceFileName = sTemplatePath & sSourceFileName
Dim oFSO
Set oFSO = CreateObject("Scripting.FileSystemObject")
oFSO.CopyFile sSourceFileName, sTargetFileName
Set oFSO = Nothing
set oPackage = DTSGlobalVariables.parent
set oConn = oPackage.connections("ExcelSheet")
oConn.DataSource = DTSGlobalVariables("sFileName").Value
set oPackage = nothing
set oConn = nothing
Main = DTSTaskExecResult_Success
End Function