February 14, 2006 at 9:24 am
Greetings,
I'm trying to do the following DTS steps due to our business requirement.
1. I created an Excel spreadsheet with a few columns on the network.
2. Everynight, a DTS package will dump retrieve data and dump to to this sheet.
3. Repeat nightly with new records on to the sheet and send email to user.
I use this code in the Execute SQl task to achieve the above steps 1 and 2. But the spreadsheets keep adding empty rows when I ran it and the new result, records, is at the bottom.
UPDATE [JC12$]
SET [Date] = '', Insured =
'', Type = '', VendorNum = ''
So, after doing some googling, someone recommended to use drop and delete table instead.
Again, I created an Excecute SQL task and try to delete the sheet and recreate it but it keeps saying that the SQL statement is incorrect:
DROP table JC12
CREATE TABLE JC12
(
'VendorNum' VarChar (25)
'Ins' VarChar (25)
'Type' VarChar (25)
'Date' VarChar (25)
I'm not sure that I do wrong. Any helps is greatly appreaciate.
Thanks,
February 14, 2006 at 9:37 am
This is a bit of a guess but if you are creating the table in Excell it will be using the Jet driver, It might not understand varchar! Try
CREATE TABLE JC12
(VendorNum String,
Ins String
etc...)
February 14, 2006 at 12:49 pm
Thanks Allen for your respond!
However, I still get the error with the codes above you gave.
February 14, 2006 at 2:24 pm
Try the following:
DROP table [JC12$]
GO
CREATE TABLE [JC12$]
(
'VendorNum' VarChar (25)
'Ins' VarChar (25)
'Type' VarChar (25)
'Date' VarChar (25)
)
GO
February 14, 2006 at 3:15 pm
I got this error:
Error Source: Microsoft Jet Database Engine
Error Description:
Syntax Error in Create Table Statement.
Perharps, that Excel does not like these statement?
Thanks everyone for your responds.
February 15, 2006 at 2:44 am
I tried the code from Micheal and got 'Invaluid SQL Statement; Expected 'DELETE','INSERT',....'
Getting rid of the drop gave me 'Syntax error in CREATE TABLE statement' (I suppose because the table isn't there in my spreadsheet.
Changing the varchar to string and putting commas between the fields gave me the error 'JC12$ is not a valid name...' and something about valid charachters and punctuation.
Getting rid of the $ sign and it works OK
CREATE TABLE [JC12]
(
'VendorNum' string (25),
'Ins' string (25),
'Type' string (25),
'Date' string (25)
)
February 15, 2006 at 7:37 am
Try using different character for the quote: the one to the left to 1(!) key on the keyboard , right above the TAB key: `
Vadim.
February 15, 2006 at 7:38 am
I have been using the same excel file and DTS package for years. The first excel sheet is the actual report. A second hidden sheet 'FY03 StatusRpt' holds the data. Cells in the first sheet link to the second. Maybe you should copy what I have below and edit it:
In the first SQL task Barrel
DROP TABLE [FY03StatusRpt]
In the next SQL task Barrel, I recreate the table:
CREATE TABLE `FY03StatusRpt` (
`MEASURE` VarChar (100) ,
`FREQUENCY` VarChar (10) ,
`LATEST` DateTime ,
`EARLIEST` DateTime ,
`UPDATED` DateTime ,
`DIVISION` VarChar (10) ,
`ReportOrder` Short ,
`Divid` VarChar (15)
)
The DTS transform data task reads the SQL table and loads excel:
SELECT MEASURE, FREQUENCY, LATEST, EARLIEST, UPDATED, DIVISION
FROM FY06StatusRpt
ORDER BY ReportOrder
February 15, 2006 at 1:01 pm
Thanks Fran and everyone for your responds. It works! I had the 1st sql task barrel with Drop Table then once that succeeded, move to create table on the second sql task barrel. Load data to the sheet, send email once that is successful. No more rows appending to the previous Excel sheet.
Also, I made an error using the (') single quote next to the Enter key.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply