DTS - Excel Question

• annejo

  Ten Centuries

  Points: 1080

  More actions

  November 25, 2003 at 1:35 pm

  #106642

  Is it possible to export the results from a SQL table to an Excel spreadsheet and then clear the results in the Excel spreadsheet before running the export a second time?

  Thanks for any information you can provide!

  Anne

• mccork

  SSC-Insane

  Points: 22288

  More actions

  November 25, 2003 at 2:34 pm

  #483624

  Definitely.

  This is easiest to set up with the Import/Export Wizard, choosing an Excel destination and, in the "transformations" dialog, chossing to drop and recreate destination table.

  Cheers,

  - Mark

  
  Cheers,
  - Mark

• jvElecenter

  SSCarpal Tunnel

  Points: 4502

  More actions

  November 26, 2003 at 1:11 am

  #483660

  Or work the other way around ? Create a view and get external data in excel ?

  Jeff

  JV

  
  JV

• wildh

  SSCarpal Tunnel

  Points: 4370

  More actions

  November 26, 2003 at 3:06 am

  #483680

  We do it all the time, some of our users want a history of data rather than using XL to get external data, which I would prefer as it’s less of a hassle for me. But it’s what the user wants that counts.

  A brief guide for DTS

  Create a SQL server connection

  Create a XL connection

  Create a data pump between the two , in destination click create (Copy SQL Statement, you’ll need it later)

  Create a DTS task (use the XL connection in Existing connection), in the SQL statement ‘drop table Test_Table Go’ and paste your clipboard.

  Create a precedence workflow to the SQL server connection

  End result

  SQL task drops the XL sheet in XL book then recreates it

  Data pump populates XL sheet

• StephenLRobson

  SSC Journeyman

  Points: 91

  More actions

  November 26, 2003 at 3:13 am

  #483681

  I have used external data queries in Excel with much success. When I have need to schedule this or trigger it automatically, I have used an ActiveX task, or an execute command task that runs a VBScript to open the Excel file, refresh the data, save and close it. Doing this means you can also refresh the data on an ad-hoc basis, without having to have access to the server running DTS - so general users can update the data when they want it, or open the file knowing that the data is refreshed regularly.

• taffy

  Ten Centuries

  Points: 1067

  More actions

  November 28, 2003 at 2:43 am

  #483975

  quote:

  ---

  I have used an ActiveX task, or an execute command task that runs a VBScript to open the Excel file, refresh the data, save and close it.

  ---

  How do you go about doing this???

• data_czar

  Ten Centuries

  Points: 1147

  More actions

  December 1, 2003 at 2:50 pm

  #484167

  Use an Execute SQL task against the Excel connection:

  DROP TABLE table_name

  Then In a second Execute SQL task against the same connection:

  CREATE TABLE `table_name` (

  `ColumnName1` VarChar (8) ,

  `ColumnName2` DateTime )

  You can also apply formatting to this Excel file after the first load and it will maintain the formatting even with the DROP and CREATE.

  Hope this helps.

  -Corey