May 13, 2008 at 7:44 am
I have to export weekly data to Excel. Let's not discuss reporting services, that's past.
I have googled the Net and found, that I can use the drop/create table statement to del;ete old data and insert new data. I have this script:
drop table `ExcelDestination'
GO
CREATE TABLE `ExcelDestination` (
`DBKey` INTEGER,
`ServerName` NVARCHAR(60),
`DB` NVARCHAR(128),
`File` NVARCHAR(8),
`Size_in_MB` NUMERIC (19,2),
`Space_Used` NUMERIC (19,2),
`Available_Space` NUMERIC (19,2),
`CheckDate` DATETIME
)
And I cannot see why I receive a syntax error. This should work, but it does not.
Can any1 give me a hint?
Greetz,
Hans Brouwer
May 13, 2008 at 9:40 am
Hans,
Drop the single quotes and use square brackets ([]) for the "file" column ( or use them for all columns)
May 14, 2008 at 5:54 am
Hi
When you are trying to export table data to excel file, you have to use Execute SQL Query task to generate the excel file definition.
I think you are trying to do the same by using the Create 'ExcelDestination'.
Here single quotes are required for file name and column names. One more thing is columns should be defined as just varchar. They should not have any other data type. Internally it may be having different data types at the table level.
I don't think drop table command is required here. As you have the latest data in the table, whenever you exeucte the SSIS package it is going to overwrite the file if it is already present.
So you can just use Execute sql query with create statement at control flow.
Hope this helps you.
Regards
Bindu
May 15, 2008 at 2:01 pm
I have found the following articles very helpful:
[font="Courier New"]http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/
http://blogs.techrepublic.com.com/datacenter/?p=133%5B/font%5D
May 16, 2008 at 5:02 am
Solved it, but I find its weird.
I just made 2b ExecuteSQL tasks and seperated the DROP and CREATE table statements. Nothing with double apostrophe. It runs as intended and a new sheet is created everytime.
Running it without drop/create adds rows to the existing sheet.
Tnx for answering all.
Greetz,
Hans Brouwer
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply