July 31, 2006 at 7:27 am
I built a DTS whitin 25 queries to manipulate the data and the result table goes to an excel file
everything works fine at the first time, if I run it again it will duplicates all the rows on the excel
file.
how do I clean it up before load the new results??
thanks in advance
July 31, 2006 at 8:03 am
You are in luck, in that dts treats excel as it would a table (well, very nearly).
Before you start the transformation, add a sql task with the following code.
DROP TABLE [Worksheetname$]
GO
CREATE TABLE [Worksheetname$] (
[col1] nvarchar (50) NULL,
[Platform] nvarchar (50) NULL
)
Get the picture? You just need to replace the Worksheetname with your worksheet and the column names.
July 31, 2006 at 8:48 am
I did and nothing happen:
DROP TABLE [New Table]
GO
CREATE TABLE [New Table] (
[STD VIN] VarChar (17) ,
VIC VarChar (10) ,
MODELYEAR VarChar (4) ,
MAKE VarChar (17) ,
MODEL VarChar (26) ,
BODYSTYLE VarChar (35) ,
AVERAUCNETPRICE float ,
STDEVAUCNETPRICE float ,
AVERAUCNETMILEAGE float ,
PERMILEADJ Decimal (28,6) ,
MILEAGEADJCAP float ,
FILECREATEDDATE DateTime
)
still the results keep growing and growing
August 1, 2006 at 1:22 pm
Jonathan is correct. Do you have the connection set to the Excel connection object?
Michael Lee
August 1, 2006 at 1:32 pm
Use temporary tables for setting up your data muniplulation needs and the final selection should be directed to your Excel Object File for output.
Coach James
August 2, 2006 at 9:49 am
Sometimes you will find that dts remembers the last time that it ran and uses that row as the startting point even though it has cleared out the spreadsheet. This code will help by looking for an empty column/row and then assume that everything after it is empty, so it deletes it. I do this by sorting the data first to make sure the empty rows are at the bottom
Just change a few of the hard coded parameters as stated in my code:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim sFileName
Dim i
sFileName = "c:\YOURFILENAME.xls" ' PUT YOUR FILENAME HERE
'********************************save the excel ******************************
Dim excelapp
Dim wkb
Dim objRange
Dim oSheet
Set excelapp = CreateObject("Excel.Application")
excelapp.visible = false
excelapp.displayalerts = false
Set wkb = excelapp.Workbooks.Open(sFileName) ' reference workbook
Set oSheet = wkb.Worksheets("YOURWORKSHEETNAME") ' reference worksheet 1 PUTTING YOUR WORKSHEET NAME HERE
wkb.Worksheets("YOURWORKSHEETNAME").select ' focus on worksheet
'*************ORDER BY THE FIRST COLUMN****************
Set objRange = oSheet.Range("A:AA") ' Sort by column A - EXTEND THE RANGE IF REQUIRED
objRange.Sort objRange,1,,,,,,1
'*************DELETE ALL EMPTY ROWS****************
'++++++ The trick here is to find out the last row with data in it. As the sheet is sorted ascending, we know as soon as a we see a blank that that is the end of
' the spreadsheet. We then want to delete an entire range onwards because deleting one row at a time is very, very slow within dts. ++++++++
i = 1
While i < 65536
If oSheet.Range("A"&i).value = "" Then ' ASSUME ROW IS EMPTY IF THIS COLUMN IS EMPTY
oSheet.Range("A"&i&":A65536").EntireRow.Delete
i = 65537
End If
i = i + 1
Wend
wkb.Save
wkb.close
'------------ Destroy open objects
Set objRange = nothing
Set wkb = nothing
excelapp.quit
Set excelapp = nothing
Main = DTSTaskExecResult_Success
End Function
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply