SQL Server Performance Forum – Threads Archive
DTS, Excel, and ActiveX
Hi- I have an excel spreadsheet that I want to poulate with data via DTS. WhatI need is the previous data to be cleared from the spreadsheet starting
with the second row(The first row is going to be column headers). Can that
be accomplished via an ActiveX? Any help is appreciated.
What we do is create a connection to excel and a connection to whichever database we need. Then create a datapump to excel, it will ask you to create a table when you go to the destination tab. Copy that table, then create an execute sql task with using the excel connection. In the sql paste in the create table script. After that create another execute sql task with the excel connection. In that task drop the table you created previously. Usually for us the precedence is Drop Table/Spreadsheet, Create Table/Spreadsheet, Insert data. After that you can go open up the spreadsheet and auto fit the columns, add colors, etc. The formatting will stay with it even after the table is dropped. The column headers are automatically put in the spreadsheet with the datapump, so there is no real reason to use an activeX script (which would be slower and might not even be successful if Office isn’t installed on the same computer as Sql server).
quote:Originally posted by Spyridon
What we do is create a connection to excel and a connection to whichever database we need. Then create a datapump to excel, it will ask you to create a table when you go to the destination tab. Copy that table, then create an execute sql task with using the excel connection. In the sql paste in the create table script. After that create another execute sql task with the excel connection. In that task drop the table you created previously. Usually for us the precedence is Drop Table/Spreadsheet, Create Table/Spreadsheet, Insert data. After that you can go open up the spreadsheet and auto fit the columns, add colors, etc. The formatting will stay with it even after the table is dropped. The column headers are automatically put in the spreadsheet with the datapump, so there is no real reason to use an activeX script (which would be slower and might not even be successful if Office isn’t installed on the same computer as Sql server).
Hi Spyridon,
I read this post and my situation is similar to Jeffrey. However, i keep getting an error when I try to drop a table within the sql task. Can you give me an example of drop table commands?
Thanks
]]>