Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1034
  • Last Modified:

What does the following option really do, drop and recreate destination table

I'm still very new in SQL. Right now I'm use SQL 2000 to and try and create a DTS package that will export contents of a table to a CSV file, but I need the DTS package to overwrite the contents of the table and not append it. Will the opetion in the wizard do this "drop and recreate destination table"?
0
compdigit44
Asked:
compdigit44
  • 2
1 Solution
 
Chris MangusDatabase AdministratorCommented:
It should overwrite your CSV file.  If it doesn't for some reason you could use an Execute Process Task to delete the file before you attempt to write to it.
0
 
gardmanITCommented:
Q.

Are you trying to drop and re-create the contents of a SQL table in the DTS and then export it to CSV or are you just trying to export an already prepared table to CSV.

If you need to drop and recreate a SQL table within DTS this is done reasonably simply, DTS will even elp you write the code!

Create a fresh DTS Package
Drop a connection object onto the page (top left icon) and set your SQL database as its source
Copy and past the connection object so you have two.
Select both then rightclick and add a Data Transformation task.
Right click the arrow between the connection objects and select properties
Set the source table you want to drop and recreate and go to the destination tab.
On the destination tab select the Create button.
This opens a window with the Create Table command you need to create the source table all over again, Copy the code to the clipboard and then select cancel (you dont want it to create the table.
Cancel out of the transform data properties box as well and delete the second connection object, this was just to get SQL to give you the code to recreate the table without youhaving to write it!

Now to your single connection object add the Execute SQL Task Object (Second section "Tasks" middle icon on the second row of objects).

Paste into the code box the create table statement from the clipboard.

eg.

CREATE TABLE [ASSHSNAPSHOT] (
[ECSTNE] decimal (7,0) NOT NULL,
[EDTNOE] decimal (5,0) NOT NULL,
[EITN14] char (6) NOT NULL,
[EITN24] char (1) NOT NULL,
[EITN34] char (4) NOT NULL,
...................
[CYCTR] decimal (15,2) NOT NULL,
[CHCTR] decimal (15,2) NOT NULL,
[SCCPB] char (1) NOT NULL )

Now all you have to do is add the drop command fro the table at the start, in this case
DROP TABLE [ASSHSNAPSHOT]
CREATE TABLE [ASSHSNAPSHOT] (
[ECSTNE] decimal (7,0) NOT NULL,
[EDTNOE] decimal (5,0) NOT NULL,
[EITN14] char (6) NOT NULL,
[EITN24] char (1) NOT NULL,
[EITN34] char (4) NOT NULL,
...................
[CYCTR] decimal (15,2) NOT NULL,
[CHCTR] decimal (15,2) NOT NULL,
[SCCPB] char (1) NOT NULL )

You now have a SQL Execute task object that will drop and re-create the table and you haven't even had to type most of it.

You can then use whatever DTS transfer  data tasks you need to repopulate the table as required.

If you just want to export a csv file it should just replace the file anyway.

Cheers,
0
 
compdigit44Author Commented:
Thanks for the reply. I'm actually trying to drop and recreate the contents of the CSV file becuase I want the data of overwrite the existing data and not append. I can find and documentation any were though to confirm that the CSV file is automatically recreated each time the export runs.
0
 
gardmanITCommented:
Hi,

I do this all the time in One of my DTS processes to extract our sales agent data for each month to a csv file which I then zip up and send by e-mail all in the same DTS.

I can positively confirm that whenever you export to a fixed CSV filename it will replace any previous file of the same name with the new version and NOT append the data.

You are correct that this behaviour is not documented in SQL Books Online (Help) and there is no control over this behaviour, but I can confirm that replacing the file is what it does.

Cheers,
IM

0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now